Thread: Select with Regular Expressions

Hi,

testing a program I ran into a glitch working with the ~* statement.
The problem is, that whenever I want to query something from the
database with ~* containing brackets () or a question mark psql throws
an exception.
So far I have tried to escape the sequence but then I ran into another
problem. Psql didn't fetch the result I wanted it to fetch (the one with
the brackets or question mark),
but one that was similiar to the one I wanted it to fetch.
Here's an example:
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'

and not the wanted result 'my (search) string'.
The problem is, that I have to escape ... the string otherwise my
program throws an exception.

The language is python.

Thanx in advance

Lucius

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