Thread: backslashes in queries containing LIKE

Hi,

We have a problem with backslashes in queries containing LIKE.

insert into table (field) values ('IM\\test')
select * from table where field = 'IM\\test'

returns one record

select * from table where field LIKE 'IM\\%'

returns no record

Why is postgres behaves like that ?
Can someone help us ?

Scripts for testing purpose have been attached.

Mickael Faivre-Macon.

On Wed, Jan 26, 2005 at 02:44:40PM +0100, Mickael Faivre-Macon wrote:

> We have a problem with backslashes in queries containing LIKE.
>
> insert into table (field) values ('IM\\test')
> select * from table where field = 'IM\\test'
>
> returns one record
>
> select * from table where field LIKE 'IM\\%'
>
> returns no record

See the "Pattern Matching" section in the "Functions and Operators"
chapter of the documentation:

http://www.postgresql.org/docs/8.0/static/functions-matching.html

The documentation under "LIKE" discusses issues regarding the escape
character (the backslash by default).

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Hi Michael,

Thank you for your reply.

We had read the LIKE doc but obviously missing the fact that

"the backslash already has a special meaning in string
literals, so to write a pattern constant that contains a backslash you
must write two backslashes in an SQL statement. Thus, writing a
pattern that actually matches a literal backslash means writing four
backslashes in the statement. "

Mickael.

--

See the "Pattern Matching" section in the "Functions and Operators"
chapter of the documentation:

http://www.postgresql.org/docs/8.0/static/functions-matching.html

The documentation under "LIKE" discusses issues regarding the escape
character (the backslash by default).

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/