Thread: BUG #5535: Backslash in condition for LIKE statement is not seen

The following bug has been logged online:

Bug reference:      5535
Logged by:          Jeff Benjamin
Email address:      jeff@ivertex.com
PostgreSQL version: 8.3.8
Operating system:   MacOSX, Linux
Description:        Backslash in condition for LIKE statement is not seen
Details:

Seems one cannot use a backslash character in a LIKE condition.

backslashprob=# create table test ( pattern varchar(50) );
CREATE TABLE
backslashprob=# insert into test (pattern) values ('\\w{12}');
INSERT 0 1
backslashprob=# select * from test;
 pattern
---------
 \w{12}
(1 row)

backslashprob=# select * from test where pattern like '\\w%';
 pattern
---------
(0 rows)

backslashprob=# select * from test where pattern like E'\\w%';
 pattern
---------
(0 rows)

backslashprob=# select * from test where pattern like '%w%';
 pattern
---------
 \w{12}
(1 row)

"Jeff Benjamin" <jeff@ivertex.com> wrote:

> Seems one cannot use a backslash character in a LIKE condition.

By default that has special meaning as an escape character.

Try this:

select * from test where pattern like E'\\w%' escape '#';

or this:

select * from test where pattern like E'\\\\w%';

-Kevin

Thanks, that works!

On Jul 1, 2010, at 1:34 PM, Kevin Grittner wrote:

> "Jeff Benjamin" <jeff@ivertex.com> wrote:
>
>> Seems one cannot use a backslash character in a LIKE condition.
>
> By default that has special meaning as an escape character.
>
> Try this:
>
> select * from test where pattern like E'\\w%' escape '#';
>
> or this:
>
> select * from test where pattern like E'\\\\w%';
>
> -Kevin