"Mike G." <[EMAIL PROTECTED]> writes:
> create a table with a data type of varchar (50) and name it email
> insert into this table the following values:
> [EMAIL PROTECTED]
> 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_table GROUP 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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
