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