Обсуждение: Like problem
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?
Table t1
Column c1
String I want to match on ‘abc_’
SELECT c1 FROM t1 WHERE c1 LIKE ‘%abc\_%’;
This gives me the following message:
WARNING: nonstandard use of escape in a string literal
LINE 1: ...ct c1 from t1 where c1 like '%abc\_%';
Remember I want an actual underscore.
Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu
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
Richard Huxton <dev@archonet.com> writes:
> Campbell, Lance wrote:
>> 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\_%'
Actually that's wrong, what he'd need is LIKE E'%abc\\_%'
(or omit the E and ignore the warning).
Alternatively, set standard_conforming_strings to TRUE and write
LIKE '%abc\_%' ... but beware that that might break other parts
of your app that are expecting backslash to be special.
> Or, change the escape character: LIKE '%abcQ_%' ESCAPE 'Q'
Yeah, this might be the easiest localized solution.
regards, tom lane
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:dev@archonet.com] 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
Tom, From your comments the recommended approach moving forward would be to use ESCAPE and define your escape character? Thanks for your help, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Wednesday, February 13, 2008 10:53 AM To: Richard Huxton Cc: Campbell, Lance; pgsql-sql@postgresql.org Subject: Re: [SQL] Like problem Richard Huxton <dev@archonet.com> writes: > Campbell, Lance wrote: >> 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\_%' Actually that's wrong, what he'd need is LIKE E'%abc\\_%' (or omit the E and ignore the warning). Alternatively, set standard_conforming_strings to TRUE and write LIKE '%abc\_%' ... but beware that that might break other parts of your app that are expecting backslash to be special. > Or, change the escape character: LIKE '%abcQ_%' ESCAPE 'Q' Yeah, this might be the easiest localized solution. regards, tom lane
Tom Lane answered this question in a prior post. 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: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Campbell, Lance Sent: Wednesday, February 13, 2008 10:59 AM To: Richard Huxton Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Like problem 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:dev@archonet.com] 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 datatypesdo not match
Tom Lane wrote: > Richard Huxton <dev@archonet.com> writes: >> Campbell, Lance wrote: >>> 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\_%' > > Actually that's wrong, what he'd need is LIKE E'%abc\\_%' > (or omit the E and ignore the warning). <sigh> need to switch to a better coffee... -- Richard Huxton Archonet Ltd