0

I created this PostgreSQL query while troubleshooting a problem that turned out to be due to non-printing characters in my strings:

with cte as ( select 'The character between the xes is evil: xx' as my_bad_string )
select substring( s.my_bad_string, n.i, 1) char_i
  , ascii(substring( s.my_bad_string, n.i, 1)) ascii_char_i
from 
  cte as s
  cross join (
    select generate_series(1, length( 'The character between the xes is evil: xx' ))
  ) n(i)

It works. It helped me to find the evil character 141.

Question: what is a more elegant way to write this query so that my hard-coded string appears exactly once instead of twice?

  • Note: it seems that stackexchange ate my evil non-printing character. You could get back to the original string by concatenating ||chr(141)|| in between the x's. But really, that's not core to the question. – mdahlman Jun 12 at 19:05
  • It turns out that at least in this situation, "evil character" turned out to be a synonym for "control character". So the actual fix to my problem was along these lines: regexp_replace('x' || chr(141) || 'x', '[[:cntrl:]]', '', 'g') to return strings stripped of stuff I didn't want. – mdahlman Jun 12 at 20:05
1

Use unnest() after converting string to array with string_to_array():

SELECT char_i, ascii(char_i) AS ascii_char_i
FROM   unnest(string_to_array('The character between the xes is evil: xx', NULL)) char_i;

NULL as separator splits after every character in string_to_array().

There is also regexp_split_to_table(), it's more versatile, but also more expensive.

To guarantee original order of characters throw WITH ORDINALITY into the mix:

SELECT pos, char_i, ascii(char_i) AS ascii_char_i
FROM   unnest(string_to_array('The character between the xes is evil: xx', NULL)) WITH ORDINALITY t(char_i, pos)
ORDER  BY pos;

But while the query is that simple the order is preserved anyway - by the current implementation.

About tricky whitespace and non-printing (UNICODE) characters:

  • Thank you. This is lovely. My query was good enough for my immediate needs... but I'm glad I asked. This is much cleaner and will come in handy in other situations. – mdahlman Jun 12 at 19:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.