Insertion of subscript text in a column of a PostgreSQL table

Question

I want to insert H2O into a column where the "2" should be stored as a subscript in PostgreSQL table (H₂O). What will be insert query for this?

Can you clarify what you mean by "stored as subscript", what is H2O, what your table looks like, etc... — 404
– 404, Commented Jun 9, 2017 at 10:34
@a_horse_with_no_name I am getting H2O as an output. — Prakash
– Prakash, Commented Jun 9, 2017 at 10:42
@melpomene : I have a data in XML as H2O. I have to convert it into H₂O and store it in posgresql table. — Prakash
– Prakash, Commented Jun 9, 2017 at 10:45

pozs · Accepted Answer · 2017-06-09 12:41:41Z

1

If your input:

is always a chemical formula,
uses mostly (but incorrectly) normal numbers, and
normal numbers can't occur anyway in them, just subscripts,

then you can use the translate() function upon insertion:

translate(formula, '0123456789', '₀₁₂₃₄₅₆₇₈₉')

http://rextester.com/XUDCXA71407

If you have other characters too, which you want to replace with its counterparts, then you'll need a slightly advanced expression:

regexp_replace(translate(regexp_replace(formula, '<.*?>', '', 'g'), '0123456789-', '₀₁₂₃₄₅₆₇₈₉₋'), 'x(e[urs]|[^e]|$)', 'ₓ\1', 'ig')

http://rextester.com/SCHLWJ33436

Notes:

the expression above just adds - and X, however
it assumes - always needed to be in subscript
X is only needed to be in subscript, where
- it does not followed by e (so it will leave Xe as is)
- if it is followed by e, the string must be continued with either u, r, or s (so ??ₓEu, ??ₓEr and ??ₓEs will be generated)
as you can see, this depends on the actual periodic table, so I would consider this kind of solution very fragile, but there are no other solutions in SQL for your problem
also, the unicode subscript set is very limited. you may be able to add a few more pairs to this conversion, but you should consider storing the original formula instead.

Or, if you can guarantee that every subscript x will be in lower case (this way, it cannot collide with Xe), then:

translate(regexp_replace(formula, '<.*?>', '', 'g'), '0123456789-x', '₀₁₂₃₄₅₆₇₈₉₋ₓ')

http://rextester.com/WPRDO99076

edited Jun 9, 2017 at 12:41

answered Jun 9, 2017 at 11:29

pozs

36.6k5 gold badges61 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Prakash Over a year ago

I have XML structure like <mod>Sr1-x BaxNb 2O 6 </mod> I have to store it as Sr(1-x)Ba(x)Nb(2)O(6) in table. character inside () should be store as subscript.

Prakash Over a year ago

Thanks a lot. This is very useful solution.

Collectives™ on Stack Overflow

Insertion of subscript text in a column of a PostgreSQL table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related