Thread: Bug #480: problem with LIKE pattern matches involving % and \_
Tom Cross (decius@whack.org) reports a bug with a severity of 2 The lower the number the more severe it is. Short Description problem with LIKE pattern matches involving % and \_ Long Description The following pattern matches do not work: LIKE '%\_%' LIKE '%\_' LIKE '\_%' They seem to match everything even if it does not contain an underscore. There seems to be no problem if another character is involved somehow, such as LIKE '\_b%' I'm running 7.1.3... Sample Code somedatabase=# select * from tom; stuff ----------- blah blah_ _blah blah_blah (4 rows) somedatabase=# select * from tom where stuff LIKE '%\_%'; stuff ----------- blah blah_ _blah blah_blah (4 rows) somedatabase=# select * from tom where stuff LIKE '\_%'; stuff ----------- blah blah_ _blah blah_blah (4 rows) somedatabase=# select * from tom where stuff LIKE '%h\_%'; stuff ----------- blah_ blah_blah (2 rows) No file was uploaded with this report
On Fri, 12 Oct 2001 pgsql-bugs@postgresql.org wrote: > Tom Cross (decius@whack.org) reports a bug with a severity of 2 > The lower the number the more severe it is. > > Short Description > problem with LIKE pattern matches involving % and \_ > > Long Description > The following pattern matches do not work: > > LIKE '%\_%' > LIKE '%\_' > LIKE '\_%' I think you need to double the backslashes, so '%\\_%' searches for anything with an underscore.
pgsql-bugs@postgresql.org writes:
> The following pattern matches do not work:
> LIKE '%\_%'
> LIKE '%\_'
> LIKE '\_%'
You are short a backslash: what the LIKE operator is seeing is just
%_%, etc, which naturally matches anything. You gotta double the
backslashes, because one backslash will be eaten by the string-literal
parser.
regards, tom lane