How to use Postgres regular expression matching on a string with $ dollar signs?

Question

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.

Answer 1

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)