Re: Regular Expression Matching problem...

On Wed, Jan 04, 2006 at 03:50:31PM +0100, A. Kretschmer wrote:
> Try this:
> 
> test=# select '+385911234567', operator_phonenumber_pattern, '+385911234567' ~
replace(operator_phonenumber_pattern,'\\\\','\\')from operators;
 
>    ?column?    | operator_phonenumber_pattern | ?column?
> ---------------+------------------------------+----------
>  +385911234567 | ^\\+38590\\d{6,7}$           | f
>  +385911234567 | ^\\+38591\\d{7}$             | t
>  +385911234567 | ^\\+3859[9|8]\\d{6,7}$       | f
>  +385911234567 | ^\\+38595\\d{7}$             | f
> (4 rows)

What Andreas is saying is that the patterns in the table have too
many backslashes.  The original query was

test=> select '+385911234567' ~ '^\\+38591\\d{7}$';?column? 
----------t
(1 row)

but if you select just the pattern you get

test=> select '^\\+38591\\d{7}$';   ?column?    
----------------^\+38591\d{7}$
(1 row)

which isn't what's in the table; somehow the patterns in the table
are over-escaped.  You might want to read up on how backslashes are
handled in quoted strings, especially when those strings are used
as regular expressions.  If you're using 8.0 or later then also
read about dollar-quoted strings.

http://www.postgresql.org/docs/8.1/interactive/sql-syntax.html#SQL-SYNTAX-STRINGS
http://www.postgresql.org/docs/8.1/interactive/functions-matching.html#FUNCTIONS-POSIX-REGEXP

(Use the documentation for whatever version of PostgreSQL you're
running; the above links have some comments that apply only to 8.1
and future versions).

-- 
Michael Fuhr