From: Michael Fuhr <mike@fuhr.org>
To: A. Kretschmer <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: Re: Regular Expression Matching problem...
Date: Wed, 4 Jan 2006 10:00:31 -0700
Message-ID: <20060104170031.GB18628@winnie.fuhr.org> (raw)
In-Reply-To: <20060104145030.GG3490@webserv.wug-glas.de>
References: <1136383726.8868.27.camel@localhost.localdomain>
<20060104145030.GG3490@webserv.wug-glas.de>
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-STRINGShttp://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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike@fuhr.org, andreas.kretschmer@schollglas.com
Subject: Re: Regular Expression Matching problem...
In-Reply-To: <20060104170031.GB18628@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox