Thread: like query backslash

select version()
"PostgreSQL 8.0.1 on i686-pc-linux-gnu, compiled by GCC i686-pc-linux-gnu-gcc (GCC) 3.3.5  (Gentoo
Linux 3.3.5-r1, ssp-3.3.2-3, pie-8.7.7.1)"

I have a table with UNC filename values (windows), such as \\server\dir\fname.txt
I am running a like query to find all files on a specific server\dir.

I would expect to put 2 backslashes into my query for each one in the text in order to escape it,
however it is making me put in 4 backslashes for each backslash in the text.

To retrieve the above example, I needed to query:
select * from filetable where filename like '\\\\\\\\server\\\\dir%'

Is this a bug or is there a reason for this?

Sim

This is how version below 8.2 used to handled backslashes, but with 8.2 you can now handle the backslashes using a setting in the postgresql.conf file:

standard_conforming_strings ( boolean)

This controls whether ordinary string literals ('...') treat backslashes literally, as specified in the SQL standard. The default is currently off, causing PostgreSQL to have its historical behavior of treating backslashes as escape characters. The default will change to on in a future release to improve compatibility with the standard. Applications may check this parameter to determine how string literals will be processed. The presence of this parameter can also be taken as an indication that the escape string syntax (E'...') is supported. Escape string syntax should be used if an application desires backslashes to be treated as escape characters.

-------------------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

Sim Zacks wrote:
> To retrieve the above example, I needed to query:
> select * from filetable where filename like '\\\\\\\\server\\\\dir%'
>
> Is this a bug or is there a reason for this?
>
> Sim

There's a reason.  With like queries, if you want to search for the
literal characters % or ?  they need to be escaped with a \.  So
essentially, the entire string is \ escaped twice.  The good news is,
you can redefine the escape character in your query.

eg

select * from filetable where filename like '\\\\server\\dir%' escape ''