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@xxxxxxxxxxxxxxxxxxxxx> wrote: > Alan Hodgson wrote: >> On Friday 24 October 2008, "Gauthier, Dave" <dave.gauthier@xxxxxxxxx> 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
