Thread: Like problem

Like problem

From
"Campbell, Lance"
Date:
<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>

Re: Like problem

From
Richard Huxton
Date:
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


Re: Like problem

From
Tom Lane
Date:
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


Re: Like problem

From
"Campbell, Lance"
Date:
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


Re: Like problem

From
"Campbell, Lance"
Date:
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


Re: Like problem

From
"Campbell, Lance"
Date:
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 


Re: Like problem

From
Richard Huxton
Date:
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