3

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.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Vanessa Ng
  • 35
  • 1
  • 3
  • `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 Jun 01 '20 at 17:34
  • Yup, I want it as the int array. For example: after converting, the expected result as: 8348544654885765 – Vanessa Ng Jun 01 '20 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. https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY – Schwern Jun 01 '20 at 19:59
  • thanks a lot! It's really helpful for a beginner like me! – Vanessa Ng Jun 01 '20 at 20:41
  • You're welcome. SQL is mind bending, but it's worth it. Good luck! – Schwern Jun 01 '20 at 22:32

2 Answers2

3

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
Schwern
  • 153,029
  • 25
  • 195
  • 336
1

Simpler than Schwern's answer is:

SELECT ascii( unnest( string_to_array('S06.6X9A', NULL) ) )
marbanb
  • 11
  • 2
  • 1
    Technically this is identical to Schwern's answer and using set returning functions in the SELECT list is discouraged though. –  Aug 17 '20 at 07:28