0

I need to check if given string is a palindrome using PostgreSQL. The first approach that came into my mind is to use a regex. I found a good regex example here.

The regex is: \A(?:(.)(?=.*?(\1\2?)\z))*?.?\2\z.

It works just fine in my browser. But when I tried to apply it in PostgreSQL 11.2 (on Windows 10), it does not work as expected.

What I've tried so far:

  1. Escaping backslashes (did not work):

    select '23200000232' ~ '\\\\A(?:(.)(?=.*?(\\\\1\\\\2?)\\\\Z))*?.?\\\\2\\\\Z' as r;

    Expected true, returned false.

  2. Using special escaping syntax E'...' (did not work):

    select '23200000232' ~ E'\A(?:(.)(?=.*?(\1\2?)\Z))*?.?\2\Z' as r;

    Expected true, returned false. Meanwhile select '23200000232' ~ E'232(.)+232' as r; returns true as expected;

  3. Executing set standard_conforming_strings=on; right before my query didn't help.

  4. Using small \Z and capital \Z (according to the PostgreSQL regex documentation) to define end of a string didn't help.

lospejos
  • 1,976
  • 3
  • 19
  • 35
  • Try `'^(?:(.)(?=.*?(\1\2?)$))*?.?\2$'` or `'^(?:(.)(?=.*?(\\1\\2?)$))*?.?\\2$'`. Escaping backslashes may not be required, that depends on the options you have enabled or not in your PostgreSQL environment – Wiktor Stribiżew Mar 28 '19 at 18:53
  • 6
    Why a regex? `column = reverse(column)` would achieve the same. –  Mar 28 '19 at 18:55
  • Thanks, this also could be an accepted answer (because goal will be reached). But if I (or somebody else) will need to use regexp in PostgreSQL for different tasks, it could be nice to find a working exampe using regexp in PostgreSQL with backslashes. – lospejos Mar 28 '19 at 19:00

0 Answers0