PostgreSQL - Convert string to ASCII integer

Question

How do I get the ASCII value of a string as an int in PostgreSQL?

For example: the string S06.6X9A

Currently, I am using an ASCII function, but it returns only the first character of a given string.

S06.6X9A is not a single integer. Do you want it as the int array [83, 48, 54, 46, 54, 88, 57, 65]? Could you provide an example of the output you want? — Schwern
– Schwern, Commented Jun 1, 2020 at 17:34
Yup, I want it as the int array. For example: after converting, the expected result as: 8348544654885765 — Vanessa Ng
– Vanessa Ng, Commented Jun 1, 2020 at 19:02
You can use the technique in my answer and turning the table back into an array, but you're probably better off writing a function and using foreach to do it more directly. postgresql.org/docs/current/… — Schwern
– Schwern, Commented Jun 1, 2020 at 19:59
You're welcome. SQL is mind bending, but it's worth it. Good luck! — Schwern
– Schwern, Commented Jun 1, 2020 at 22:32

Schwern · Accepted Answer · 2020-06-01 17:44:46Z

4

Use string_to_array('S06.6X9A', null) to split the string into a text[] of individual characters. Then unnest to turn that text[] into a table. Then use that in a from clause and run ascii() over each row.

select ascii(char)
from (
  select unnest( string_to_array('S06.6X9A', null) )
) as chars(char);

 ascii 
-------
    83
    48
    54
    46
    54
    88
    57
    65

answered Jun 1, 2020 at 17:44

Schwern

167k28 gold badges225 silver badges370 bronze badges

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

Comments

marbanb · Accepted Answer · 2020-08-17 07:23:52Z

4

Simpler than Schwern's answer is:

SELECT ascii( unnest( string_to_array('S06.6X9A', NULL) ) )

answered Aug 17, 2020 at 7:23

marbanb

412 bronze badges

1 Comment

user330315 Over a year ago

Technically this is identical to Schwern's answer and using set returning functions in the SELECT list is discouraged though.

Collectives™ on Stack Overflow

PostgreSQL - Convert string to ASCII integer

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related