Skip site navigation (1) Skip section navigation (2)

Re: Like problem

From: "Campbell, Lance" <lance(at)uiuc(dot)edu>
To: "Richard Huxton" <dev(at)archonet(dot)com>
Cc: <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Like problem
Date: 2008-02-13 16:59:14
Message-ID: B10E6810AC2A2F4EA7550D072CDE8760197EF3@SAB-FENWICK.sab.uiuc.edu (view raw or flat)
Thread:
Lists: 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(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

In response to

Responses

pgsql-sql by date

Next:From: Campbell, LanceDate: 2008-02-13 17:02:30
Subject: Re: Like problem
Previous:From: Tom LaneDate: 2008-02-13 16:52:41
Subject: Re: Like problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group