Date: Wed, 4 Jan 2006 15:50:31 +0100
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: Re: Regular Expression Matching problem...
Message-ID: <20060104145030.GG3490@webserv.wug-glas.de>
Mail-Followup-To: "A. Kretschmer" <andreas.kretschmer@schollglas.com>,
	pgsql-sql@postgresql.org
References: <1136383726.8868.27.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1136383726.8868.27.camel@localhost.localdomain>
User-Agent: mutt-ng 1.5.9i (Linux)

am  04.01.2006, um 15:08:45 +0100 mailte Mario Splivalo folgendes:
> I have a table containing regular expresion patterns for matching phone
> numbers with the mobile operators.
> 
> For instance, my phone number is '+385911234567', and the regexp for
> this mobile operator is: "^\+38591\d{7}$".
> 
> Now, when I do a regexp match in a single select, it behaves as
> expected:
> 
> octopussy2=# select '+385911234567' ~ '^\\+38591\\d{7}$';
>  ?column?
> ----------
>  t
> (1 row)
> 
> Now, as I've mentioned, I have a table with operators and their patterns
> for phone numbers:
> 
> octopussy2=# select * from operators;
>  operator_id | operator_name | operator_phonenumber_pattern
> -------------+---------------+------------------------------
>            1 | FreeSMSC      | ^\\+38590\\d{6,7}$
>            2 | VipNet        | ^\\+38591\\d{7}$
>            3 | T-Mobile      | ^\\+3859[9|8]\\d{6,7}$
>            4 | Tele2         | ^\\+38595\\d{7}$
> (4 rows)
> 
> 
> Now, if I construct my query like this:
> 
> octopussy2=# select '+385911234567', operator_phonenumber_pattern,
> '+385911234567' ~ operator_phonenumber_pattern from operators;
> 
>    ?column?    | operator_phonenumber_pattern | ?column?
> ---------------+------------------------------+----------
>  +385911234567 | ^\\+38590\\d{6,7}$           | f
>  +385911234567 | ^\\+38591\\d{7}$             | f
>  +385911234567 | ^\\+3859[9|8]\\d{6,7}$       | f
>  +385911234567 | ^\\+38595\\d{7}$             | f
> (4 rows)
> 
> 
> Why do I get all the "f"'s? I tought that the operator_id 2 should
> return "t", esp. when I wrote the first query it seems that the regex
> match was ok.

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)


HTH, Andreas
-- 
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47212,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===