2

I'm using the ~* operator in a SELECT statement to search for parts of a string.

Where I'm having trouble is with a particular string that starts/ends with the $ dollars sign.

SELECT "artists".* FROM "artists" WHERE (name ~* '$uicideBoy$')

If I remove the $'s it works as it should. But when I include them, no results come back.

To be clear, the artist's name is literally $uicideBoy$, and that's what I want to be able to ~* match based on.. I'm vaguely aware of Postgres "dollar quoting", which I'm assuming may be causing the issue here...I'm just not sure how to resolve it.

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
Shpigford
  • 24,748
  • 58
  • 163
  • 252
  • 1
    `~*` operator is case insensitive regexp match. In regexp dollar sign means EOL (end of line). If you want to search for literal values then you should to escape special regexp characters with backslashes. Consider: `select '$abc$' ~* '$abc$', '$abc$' ~* '\$abc\$';` – Abelisto Jan 10 '20 at 20:56

1 Answers1

0

I would either escape the $ with a backslash, just double it to avoid any regex conflict, or use a different operator, e.g. LIKE or ILIKE (case insensitive):

SELECT 
  '$fOO$Bar$' ~* $$$$foo$$bar$$$$,
  '$fOO$Bar$' ~* '\$foo\$bar\$',
  '$fOO$Bar$' ILIKE '%$foo$bar$%';

 foo$$bar$$$$ | ?column? | ?column? 
--------------+----------+----------
 t            | t        | t
(1 Zeile)
Jim Jones
  • 18,404
  • 3
  • 35
  • 44