Обсуждение: Escape wildcard problems.
I read in the docs (section 9.7.1) that the backslash... \ ... is the default escape char to use in “like” expressions. Yet when I try it, it doesn’t seem to work the ay I expect. Here’s an example...
select name from templates where name like ‘%\_cont\_%’;
name
----------------------------------
cgidvcontrol
x8idvcontrol
etc....
I would expect to NOT see these because the “cont” is not preceded by and followed by an underscore (because I escaped them with \).
Please advise.
Thanks
-dave
On Fri, Oct 24, 2008 at 08:12:38AM -0700, Gauthier, Dave wrote: > select name from templates where name like '%\_cont\_%'; > > name > ---------------------------------- > cgidvcontrol > x8idvcontrol > etc.... > > I would expect to NOT see these because the "cont" is not preceded by > and followed by an underscore (because I escaped them with \). You need to escape the escape! backslash is the escape character in literals as well as like patterns, so you need to double it up. I think you want to be doing: name LIKE '%\\_cont\\_%' Sam
On Friday 24 October 2008, "Gauthier, Dave" <dave.gauthier@intel.com> wrote: > I read in the docs (section 9.7.1) that the backslash... \ ... is the > default escape char to use in "like" expressions. Yet when I try it, it > doesn't seem to work the ay I expect. Here's an example... > > select name from templates where name like '%\_cont\_%'; > Use double \\ for underscores. I don't know why it's necessary, but it works here. -- Alan
Alan Hodgson wrote: > On Friday 24 October 2008, "Gauthier, Dave" <dave.gauthier@intel.com> wrote: >> I read in the docs (section 9.7.1) that the backslash... \ ... is the >> default escape char to use in "like" expressions. Yet when I try it, it >> doesn't seem to work the ay I expect. Here's an example... >> >> select name from templates where name like '%\_cont\_%'; >> > > Use double \\ for underscores. I don't know why it's necessary, but it works > here. Here's why. See the documentation for more information: craig=> show standard_conforming_strings; standard_conforming_strings ----------------------------- off (1 row) craig=> SELECT '%\_cont\_%'; WARNING: nonstandard use of escape in a string literal LINE 1: SELECT '%\_cont\_%'; ^ HINT: Use the escape string syntax for escapes, e.g., E'\r\n'. ?column? ---------- %_cont_% (1 row) craig=> SELECT E'%\\_cont\\_%'; ?column? ------------ %\_cont\_% (1 row) craig=> set standard_conforming_strings = 1; SET craig=> SELECT '%\_cont\_%'; ?column? ------------ %\_cont\_% (1 row) -- Craig Ringer
Or you could use: SELECT name FROM templates WHERE name ~ '\_cont\_'; This does it as a regular expression. ~* '\_aa\_'; On Fri, Oct 24, 2008 at 5:07 PM, Craig Ringer <craig@postnewspapers.com.au> wrote: > Alan Hodgson wrote: >> On Friday 24 October 2008, "Gauthier, Dave" <dave.gauthier@intel.com> wrote: >>> I read in the docs (section 9.7.1) that the backslash... \ ... is the >>> default escape char to use in "like" expressions. Yet when I try it, it >>> doesn't seem to work the ay I expect. Here's an example... >>> >>> select name from templates where name like '%\_cont\_%'; >>> >> >> Use double \\ for underscores. I don't know why it's necessary, but it works >> here. > > Here's why. See the documentation for more information: > > > craig=> show standard_conforming_strings; > standard_conforming_strings > ----------------------------- > off > (1 row) > > craig=> SELECT '%\_cont\_%'; > WARNING: nonstandard use of escape in a string literal > LINE 1: SELECT '%\_cont\_%'; > ^ > HINT: Use the escape string syntax for escapes, e.g., E'\r\n'. > ?column? > ---------- > %_cont_% > (1 row) > > craig=> SELECT E'%\\_cont\\_%'; > ?column? > ------------ > %\_cont\_% > (1 row) > > craig=> set standard_conforming_strings = 1; > SET > > craig=> SELECT '%\_cont\_%'; > ?column? > ------------ > %\_cont\_% > (1 row) > > > > -- > Craig Ringer > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >