X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id A9E069DC827
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  4 Jan 2006 13:00:40 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 52880-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  4 Jan 2006 13:00:38 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158])
	by postgresql.org (Postfix) with ESMTP id A13489DC84E
	for <pgsql-sql@postgresql.org>; Wed,  4 Jan 2006 13:00:35 -0400 (AST)
Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1])
	by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id k04H0V8G016671
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK);
	Wed, 4 Jan 2006 10:00:34 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: from winnie.fuhr.org (localhost [127.0.0.1])
	by winnie.fuhr.org (8.13.4/8.13.4) with ESMTP id k04H0VbY032935;
	Wed, 4 Jan 2006 10:00:31 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: (from mfuhr@localhost)
	by winnie.fuhr.org (8.13.4/8.13.4/Submit) id k04H0VI1032934;
	Wed, 4 Jan 2006 10:00:31 -0700 (MST) (envelope-from mfuhr)
Date: Wed, 4 Jan 2006 10:00:31 -0700
From: Michael Fuhr <mike@fuhr.org>
To: "A. Kretschmer" <andreas.kretschmer@schollglas.com>,
	pgsql-sql@postgresql.org
Subject: Re: Regular Expression Matching problem...
Message-ID: <20060104170031.GB18628@winnie.fuhr.org>
References: <1136383726.8868.27.camel@localhost.localdomain>
	<20060104145030.GG3490@webserv.wug-glas.de>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20060104145030.GG3490@webserv.wug-glas.de>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.07 required=5 tests=[AWL=0.070]
X-Spam-Score: 0.07
X-Spam-Level: 
X-Archive-Number: 200601/16
X-Sequence-Number: 23805

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