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)
Show quoted text
On 1/14/07, Sim Zacks <sim@compulab.co.il> wrote:
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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
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 ''