Thread: Like problem
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">8.2.5</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">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?</span></font><p class="MsoNormal"><font face="Arial" size="2"><spanstyle="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Table t1</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Column c1</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">String I want to match on ‘abc_’</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">SELECT c1 FROM t1 WHERE c1 LIKE ‘%abc\_%’;</span></font><p class="MsoNormal"><font face="Arial" size="2"><spanstyle="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">This gives me the following message:</span></font><p class="MsoNormal"><font face="Arial" size="2"><spanstyle="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">WARNING: nonstandard use of escape in a string literal</span></font><p class="MsoNormal"><font face="Arial"size="2"><span style="font-size:10.0pt; font-family:Arial">LINE 1: ...ct c1 from t1 where c1 like '%abc\_%';</span></font><p class="MsoNormal"><font face="Arial"size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Remember I want an actual underscore.</span></font><p class="MsoNormal"><font face="Arial" size="2"><spanstyle="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Lance Campbell</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Project Manager/Software Architect</span></font><p class="MsoNormal"><font face="Arial" size="2"><spanstyle="font-size:10.0pt; font-family:Arial">Web Services at Public Affairs</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">University</span></font><fontface="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">of Illinois</span></font><p class="MsoNormal"><font face="Arial" size="2"><spanstyle="font-size:10.0pt; font-family:Arial">217.333.0382</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">http://webservices.uiuc.edu</span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><spanstyle="font-size: 12.0pt"> </span></font></div>
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