Re: Select with Regular Expressions

On Sun, Feb 26, 2006 at 11:04:16AM +0100, Peter Weinzierl wrote:
> I want to fetch 'my (search) string' from the table
>
> select bar from table where bar ~*' my (search) string';
>
> This didn't work out so I tried:
>
> select bar from table where bar~*'my \(search\) string';
>
> But this only returned:
>
> 'my search string'

With single quotes you'll need to add another layer of escaping
because the string parser is parsing the backslashes before the
string is interpreted as a regular expression.  Example:

test=> SELECT * FROM foo;
 id |        bar
----+--------------------
  1 | my search string
  2 | my (search) string
(2 rows)

test=> SELECT 'my \(search\) string';
      ?column?
--------------------
 my (search) string
(1 row)

test=> SELECT * FROM foo WHERE bar ~* 'my \(search\) string';
 id |       bar
----+------------------
  1 | my search string
(1 row)

test=> SELECT 'my \\(search\\) string';
       ?column?
----------------------
 my \(search\) string
(1 row)

test=> SELECT * FROM foo WHERE bar ~* 'my \\(search\\) string';
 id |        bar
----+--------------------
  2 | my (search) string
(1 row)

If you're using 8.0 or later then you can use dollar quotes to avoid
the need for an extra layer of escaping:

test=> SELECT $$my \(search\) string$$;
       ?column?
----------------------
 my \(search\) string
(1 row)

test=> SELECT * FROM foo WHERE bar ~* $$my \(search\) string$$;
 id |        bar
----+--------------------
  2 | my (search) string
(1 row)

You'll also have to consider your programming language's string
parsing, which might necessitate yet another layer of escaping.

--
Michael Fuhr