Postgres Pro
Downloads
Home   >   mailing lists

Thread: Substring function incorrect when searching for '@.'

Substring function incorrect when searching for '@.'

From
"Mike G."
Date:
To reproduce:

create a table with  a data type of varchar (50) and name it email

insert into this table the following values:
test1@anyemail.com

Execute the following statement:
SELECT CASE WHEN count(substring(email FROM '@.')) > 0 THEN count(substring(email FROM '@.')) ELSE 0 END, email FROM
your_schema.your_tableGROUP BY email; 

Result with be equal to 1 / True.  It should be 0 / False.

If you execute the above but replace '@.' with '@a' it will also return 1 / True is correct.

The only time it fails for me is if the @ is immediately followed by a period.

7.3.4 using psql via pgadminIII under cygwin.

Mike

Re: Substring function incorrect when searching for '@.'

From
Tom Lane
Date:
"Mike G." <mike@thegodshalls.com> writes:
> create a table with  a data type of varchar (50) and name it email

> insert into this table the following values:
> test1@anyemail.com

> Execute the following statement:
> SELECT CASE WHEN count(substring(email FROM '@.')) > 0 THEN count(substring(email FROM '@.')) ELSE 0 END, email FROM
your_schema.your_tableGROUP BY email; 

> Result with be equal to 1 / True.  It should be 0 / False.

This is not a bug; it's a POSIX regular expression match, and it's
behaving exactly as it should ('.' matches any character).

The particular syntax substring(char-expression FROM char-expression)
is not defined by SQL99 --- their regular-expression construct requires
a third parameter (ESCAPE something).  We have chosen to interpret it
as a POSIX regular-expression match.  See
http://www.postgresql.org/docs/7.3/static/functions-matching.html

            regards, tom lane