Richard,
The first example you gave me does not work. Below is the test example I used (this example should NOT return 'matched'): SELECT 'matched' WHERE 'abcgxyz' LIKE E'%abc\_x%'; ?column? ---------- matched (1 row) The second example you gave me does work: SELECT 'matched' WHERE 'abcgxyz' LIKE '%abcQ_x%' ESCAPE 'Q'; ?column? ---------- 0 rows returned SELECT 'matched' WHERE 'abc_xyz' LIKE '%abcQ_x%' ESCAPE 'Q'; ?column? ---------- matched 1 row Why does the first example not work? I have also tried the following (the below should not work if they are correct): SELECT 'matched' WHERE 'abcgxyz' LIKE '%abc' || E'\_' || 'x%'; ?column? ---------- matched (1 row) SELECT 'matched' WHERE 'abcgxyz' LIKE '%abc' || E'_' || 'x%'; ?column? ---------- matched (1 row) Do you have any thoughts on why none of these examples work with the 'E'? Thanks, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu -----Original Message----- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 13, 2008 10:42 AM To: Campbell, Lance Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Like problem Campbell, Lance wrote: > 8.2.5 > > I am having an issue with trying to use 'LIKE' so that I can match on a > string with an underscore in it. What is the proper way to find the > following string? > WARNING: nonstandard use of escape in a string literal > > LINE 1: ...ct c1 from t1 where c1 like '%abc\_%'; Either indicate you are using an escaped string: LIKE E'%abc\_%' Or, change the escape character: LIKE '%abcQ_%' ESCAPE 'Q' -- Richard Huxton Archonet Ltd ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
