pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLike problem
7+ messages / 3 participants
[nested] [flat]
* Like problem
@ 2008-02-13 16:30 Campbell, Lance <lance@uiuc.edu>
2008-02-13 16:42 ` Re: Like problem Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 7+ messages in thread
From: Campbell, Lance @ 2008-02-13 16:30 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Like problem
2008-02-13 16:30 Like problem Campbell, Lance <lance@uiuc.edu>
@ 2008-02-13 16:42 ` Richard Huxton <dev@archonet.com>
2008-02-13 16:52 ` Re: Like problem Tom Lane <tgl@sss.pgh.pa.us>
2008-02-13 16:59 ` Re: Like problem Campbell, Lance <lance@uiuc.edu>
0 siblings, 2 replies; 7+ messages in thread
From: Richard Huxton @ 2008-02-13 16:42 UTC (permalink / raw)
To: Campbell, Lance <lance@uiuc.edu>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Like problem
2008-02-13 16:30 Like problem Campbell, Lance <lance@uiuc.edu>
2008-02-13 16:42 ` Re: Like problem Richard Huxton <dev@archonet.com>
@ 2008-02-13 16:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-02-13 17:02 ` Re: Like problem Campbell, Lance <lance@uiuc.edu>
2008-02-13 17:09 ` Re: Like problem Richard Huxton <dev@archonet.com>
1 sibling, 2 replies; 7+ messages in thread
From: Tom Lane @ 2008-02-13 16:52 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Campbell, Lance <lance@uiuc.edu>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Like problem
2008-02-13 16:30 Like problem Campbell, Lance <lance@uiuc.edu>
2008-02-13 16:42 ` Re: Like problem Richard Huxton <dev@archonet.com>
2008-02-13 16:52 ` Re: Like problem Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-02-13 17:02 ` Campbell, Lance <lance@uiuc.edu>
1 sibling, 0 replies; 7+ messages in thread
From: Campbell, Lance @ 2008-02-13 17:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Like problem
2008-02-13 16:30 Like problem Campbell, Lance <lance@uiuc.edu>
2008-02-13 16:42 ` Re: Like problem Richard Huxton <dev@archonet.com>
2008-02-13 16:52 ` Re: Like problem Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-02-13 17:09 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 7+ messages in thread
From: Richard Huxton @ 2008-02-13 17:09 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Campbell, Lance <lance@uiuc.edu>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Like problem
2008-02-13 16:30 Like problem Campbell, Lance <lance@uiuc.edu>
2008-02-13 16:42 ` Re: Like problem Richard Huxton <dev@archonet.com>
@ 2008-02-13 16:59 ` Campbell, Lance <lance@uiuc.edu>
2008-02-13 17:03 ` Re: Like problem Campbell, Lance <lance@uiuc.edu>
1 sibling, 1 reply; 7+ messages in thread
From: Campbell, Lance @ 2008-02-13 16:59 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Like problem
2008-02-13 16:30 Like problem Campbell, Lance <lance@uiuc.edu>
2008-02-13 16:42 ` Re: Like problem Richard Huxton <dev@archonet.com>
2008-02-13 16:59 ` Re: Like problem Campbell, Lance <lance@uiuc.edu>
@ 2008-02-13 17:03 ` Campbell, Lance <lance@uiuc.edu>
0 siblings, 0 replies; 7+ messages in thread
From: Campbell, Lance @ 2008-02-13 17:03 UTC (permalink / raw)
To: Campbell, Lance <lance@uiuc.edu>; Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
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 datatypes do not
match
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2008-02-13 17:09 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-13 16:30 Like problem Campbell, Lance <lance@uiuc.edu>
2008-02-13 16:42 ` Richard Huxton <dev@archonet.com>
2008-02-13 16:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-02-13 17:02 ` Campbell, Lance <lance@uiuc.edu>
2008-02-13 17:09 ` Richard Huxton <dev@archonet.com>
2008-02-13 16:59 ` Campbell, Lance <lance@uiuc.edu>
2008-02-13 17:03 ` Campbell, Lance <lance@uiuc.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox