From: | "Campbell, Lance" <lance(at)uiuc(dot)edu> |
---|---|
To: | "Campbell, Lance" <lance(at)uiuc(dot)edu>, "Richard Huxton" <dev(at)archonet(dot)com> |
Cc: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Like problem |
Date: | 2008-02-13 17:03:00 |
Message-ID: | B10E6810AC2A2F4EA7550D072CDE8760197EF5@SAB-FENWICK.sab.uiuc.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)postgresql(dot)org
[mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Campbell, Lance
Sent: Wednesday, February 13, 2008 10:59 AM
To: Richard Huxton
Cc: pgsql-sql(at)postgresql(dot)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(at)archonet(dot)com]
Sent: Wednesday, February 13, 2008 10:42 AM
To: Campbell, Lance
Cc: pgsql-sql(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2008-02-13 17:09:10 | Re: Like problem |
Previous Message | Campbell, Lance | 2008-02-13 17:02:30 | Re: Like problem |