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

Re: LIKE with pattern containing backslash

From: "Daniel Verite" <daniel(at)manitou-mail(dot)org>
To: "Jack Orenstein" <jack(dot)orenstein(at)hds(dot)com>
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>,pgsql-general(at)postgresql(dot)org,"Matt McDonald" <matt(dot)mcdonald(at)hds(dot)com>
Subject: Re: LIKE with pattern containing backslash
Date: 2009-02-03 21:50:14
Message-ID: 2ef31fa3-2b0b-4f99-8982-82e33bbf2cf4@mm (view raw or flat)
Thread:
Lists: pgsql-general
	Jack Orenstein wrote:

> In this code, The string passed to JDBC is %\% (percent, one
> backslash, percent), and no rows are returned.
> It appears as if the one backslash is being treated as an
> escape for the %. 

That's right. So \% as a pattern matches a percent, and \\ as a pattern 
matches a backslash. This has nothing to do with literals and all to do 
with the special role of backslash in LIKE.

From your program:

>	   // Find rows with at least one backslash
>	   String oneBackslash = new String(new char[]{'%', '\\', 
'%'});

Actually, this will find rows ending with a percent.

>	   // Find rows with at least two backslashes
>	   String twoBackslashes = new String(new char[]{'%', '\\', 
'\\', '%'});

Actually, this will find rows having at least one backslash.

The point is the lack of an ESCAPE clause to LIKE. Just add it to 
disable the special role of backslash and the patterns will match as 
you expect.

 Best regards,
-- 
 Daniel
 PostgreSQL-powered mail user agent and storage: 
http://www.manitou-mail.org

In response to

pgsql-general by date

Next:From: Simon RiggsDate: 2009-02-03 22:06:21
Subject: Re: Pet Peeves?
Previous:From: Greg SmithDate: 2009-02-03 21:33:00
Subject: Re: Pet Peeves?

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