LIKE predicate and '\' character

I sent a previous mail with regard to using the '\' (backslash) character

in

an SQL SELECT statement.
The outcome was that postgres does not escape the '\' itself - I need to

do

it myself before submitting the SQL - fair enough, I now do this.

I have just written a java app that uses a single '\' in an SQL SELECT
statement and unlike my C application that uses the PSQLODBC driver this
*DOES* return data. To me this says that the problem of having to escape the
'\' myself (as I have to do in my C++ ODBC application) has already been
addressed in the Java driver, and so I do not need to escape it myself in my
Java application.
If this problem has been addressed in the Java driver then surely (for
conformity) it should also be addressed in the ODBC driver ?.

Here is my Java code :
...
try {
con = DriverManager.getConnection(url, "postgres",
"postgres");
}
catch (Exception e) {
MyOutput(e.getMessage());
System.exit(1);
}

try {
String strPart;

strPart = "A\\B";
MyOutput("strPart: <" + strPart + ">");

strSQL = "SELECT partdesc FROM partmaster WHERE partnum = ?";
MyOutput("SELECT SQL: <" + strSQL + ">");

PreparedStatement pstmt = con.prepareStatement(strSQL);
pstmt.setString(1, strPart);

result = pstmt.executeQuery();

while (result.next()) {
data = result.getString(1);
MyOutput("DATA FETCHED: Partdesc = <" + result.getString(1) + ">");
}
}
catch (Exception e) {
MyOutput(e.getMessage());
System.exit(1);
}

Here is my program output:

strPart: <A\B>
SELECT SQL: <SELECT partdesc FROM partmaster WHERE partnum = ?>
DATA FETCHED: Partdesc = <AB SLASH TEST>

Java does have the same problem with the LIKE predicate however, as to
return any data I need to change my code to :

...
strPart = "A\\\\B";
...
strSQL = "SELECT partdesc FROM partmaster WHERE partnum LIKE ?";

Comments please.

Andy
ahm@exel.co.uk

"Andy Hallam" <ahm@exel.co.uk> wrote in message
news:9sb3ek$r0k$1@news.tht.net...

PostgreSQL - 7.1.3 (installed on Linux 2.4.2-2)
PSQLODBC.DLL - 07.01.0007
Visual C++ - 6.0

I sent a previous mail with regard to using the '\' (backslash) character

in

an SQL SELECT statement.
The outcome was that postgres does not escape the '\' itself - I need to

do

it myself before submitting the SQL - fair enough, I now do this.

i.e
instead of
mydb=# SELECT * FROM users WHERE id = 'WORKGROUP\me';
I now do
mydb=# SELECT * FROM users WHERE id = 'WORKGROUP\\me';

BUT, if I use the LIKE predicate I have to escape the escape.

i.e
mydb=# SELECT * FROM users WHERE id LIKE 'WORKGROUP\\\\me';

Now this must be treated as a bug.
As you can see it is not an error with the PSQLODBC driver as I ran the

SQL

On Wed, Nov 07, 2001 at 12:56:46PM -0000, Andy Hallam wrote:

The PostgreSQL parser "eat" one '\' on arbitrary place in query. You
you want put '\' to some function (operator) you must use '\\'.

test=# select '\\';
?column?
----------
\
(1 row)

test=# select '\\\\';
?column?
----------
\\
(1 row)

test=# select 'hello\\pg' like 'hello\\pg';
?column?
----------
f
(1 row)

test=# select 'hello\\pg' like 'hello\\\\pg';
?column?
----------
t
(1 row)

Karel

PostgreSQL - 7.1.3 (installed on Linux 2.4.2-2)
PSQLODBC.DLL - 07.01.0007
Visual C++ - 6.0

I sent a previous mail with regard to using the '\' (backslash) character in
an SQL SELECT statement.
The outcome was that postgres does not escape the '\' itself - I need to do
it myself before submitting the SQL - fair enough, I now do this.

i.e
instead of
mydb=# SELECT * FROM users WHERE id = 'WORKGROUP\me';
I now do
mydb=# SELECT * FROM users WHERE id = 'WORKGROUP\\me';

BUT, if I use the LIKE predicate I have to escape the escape.

i.e
mydb=# SELECT * FROM users WHERE id LIKE 'WORKGROUP\\\\me';

Now this must be treated as a bug.
As you can see it is not an error with the PSQLODBC driver as I ran the SQL
from the command line with the same results.
I am presuming that the backend parsing logic around the LIKE prodicate is
ignoring the '\'.

Is anyone working on this ?. Can anyone send me a fix, as without this I'm
screwed.

Thanks for any help

Andy.
ahm@exel.co.uk

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

"Andy Hallam" <ahm@exel.co.uk> writes:

BUT, if I use the LIKE predicate I have to escape the escape.
Now this must be treated as a bug.

It's not a bug, it's the defined behavior of LIKE. See
http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/functions-matching.html#FUNCTIONS-LIKE

You might find it more convenient to select a different escape
character for LIKE.

regards, tom lane

On Wed, 7 Nov 2001, Andy Hallam wrote:

PostgreSQL - 7.1.3 (installed on Linux 2.4.2-2)
PSQLODBC.DLL - 07.01.0007
Visual C++ - 6.0

I sent a previous mail with regard to using the '\' (backslash) character in
an SQL SELECT statement.
The outcome was that postgres does not escape the '\' itself - I need to do
it myself before submitting the SQL - fair enough, I now do this.

i.e
instead of
mydb=# SELECT * FROM users WHERE id = 'WORKGROUP\me';
I now do
mydb=# SELECT * FROM users WHERE id = 'WORKGROUP\\me';

BUT, if I use the LIKE predicate I have to escape the escape.

i.e
mydb=# SELECT * FROM users WHERE id LIKE 'WORKGROUP\\\\me';

Now this must be treated as a bug.

Postgres *also* treats \ as the default LIKE escape character.
Use LIKE '<string>' ESCAPE '' (or some other character if
you want to use the like escaping for %, etc).

Did you actually read my mail posting???

I sent a previous mail with regard to using the '\' (backslash) character

in

an SQL SELECT statement.
The outcome was that postgres does not escape the '\' itself - I need to

do

it myself before submitting the SQL - fair enough, I now do this.

I already know what you have just tried to explain to me.

My question is not about having to escape a '\' character but the fact that
the behaviour is not consistent when using the LIKE predicate. I.e - you
have to escape the escape when using LIKE.

Andy.

"Karel Zak" <zakkr@zf.jcu.cz> wrote in message
news:20011107151110.C6354@zf.jcu.cz...

On Wed, Nov 07, 2001 at 12:56:46PM -0000, Andy Hallam wrote:

The PostgreSQL parser "eat" one '\' on arbitrary place in query. You
you want put '\' to some function (operator) you must use '\\'.

test=# select '\\';
?column?
----------
\
(1 row)

test=# select '\\\\';
?column?
----------
\\
(1 row)

test=# select 'hello\\pg' like 'hello\\pg';
?column?
----------
f
(1 row)

test=# select 'hello\\pg' like 'hello\\\\pg';
?column?
----------
t
(1 row)

Karel

PostgreSQL - 7.1.3 (installed on Linux 2.4.2-2)
PSQLODBC.DLL - 07.01.0007
Visual C++ - 6.0

I sent a previous mail with regard to using the '\' (backslash)

character in

an SQL SELECT statement.
The outcome was that postgres does not escape the '\' itself - I need to

do

it myself before submitting the SQL - fair enough, I now do this.

i.e
instead of
mydb=# SELECT * FROM users WHERE id = 'WORKGROUP\me';
I now do
mydb=# SELECT * FROM users WHERE id = 'WORKGROUP\\me';

BUT, if I use the LIKE predicate I have to escape the escape.

i.e
mydb=# SELECT * FROM users WHERE id LIKE 'WORKGROUP\\\\me';

Now this must be treated as a bug.
As you can see it is not an error with the PSQLODBC driver as I ran the

SQL

from the command line with the same results.
I am presuming that the backend parsing logic around the LIKE prodicate

is

ignoring the '\'.

Is anyone working on this ?. Can anyone send me a fix, as without this

I'm

I have since found out that Postgres treats \ as the default LIKE escape
character. (Courtesy Stephan Szabo)

I will learn to live with this 'feature'.

Thanks.

Andy

"Andy Hallam" <ahm@exel.co.uk> wrote in message
news:9se3qn$1dju$1@news.tht.net...

Did you actually read my mail posting???

I sent a previous mail with regard to using the '\' (backslash)

character

in

an SQL SELECT statement.
The outcome was that postgres does not escape the '\' itself - I need to

do

it myself before submitting the SQL - fair enough, I now do this.

I already know what you have just tried to explain to me.

My question is not about having to escape a '\' character but the fact

that

the behaviour is not consistent when using the LIKE predicate. I.e - you
have to escape the escape when using LIKE.

Andy.

"Karel Zak" <zakkr@zf.jcu.cz> wrote in message
news:20011107151110.C6354@zf.jcu.cz...

On Wed, Nov 07, 2001 at 12:56:46PM -0000, Andy Hallam wrote:

The PostgreSQL parser "eat" one '\' on arbitrary place in query. You
you want put '\' to some function (operator) you must use '\\'.

test=# select '\\';
?column?
----------
\
(1 row)

test=# select '\\\\';
?column?
----------
\\
(1 row)

test=# select 'hello\\pg' like 'hello\\pg';
?column?
----------
f
(1 row)

test=# select 'hello\\pg' like 'hello\\\\pg';
?column?
----------
t
(1 row)

Karel

PostgreSQL - 7.1.3 (installed on Linux 2.4.2-2)
PSQLODBC.DLL - 07.01.0007
Visual C++ - 6.0

I sent a previous mail with regard to using the '\' (backslash)

character in

an SQL SELECT statement.
The outcome was that postgres does not escape the '\' itself - I need

to

do

it myself before submitting the SQL - fair enough, I now do this.

i.e
instead of
mydb=# SELECT * FROM users WHERE id = 'WORKGROUP\me';
I now do
mydb=# SELECT * FROM users WHERE id = 'WORKGROUP\\me';

BUT, if I use the LIKE predicate I have to escape the escape.

i.e
mydb=# SELECT * FROM users WHERE id LIKE 'WORKGROUP\\\\me';

Now this must be treated as a bug.
As you can see it is not an error with the PSQLODBC driver as I ran

the

SQL

from the command line with the same results.
I am presuming that the backend parsing logic around the LIKE

prodicate

is

ignoring the '\'.

Is anyone working on this ?. Can anyone send me a fix, as without this

I'm

screwed.

Thanks for any help

Andy.
ahm@exel.co.uk

---------------------------(end of

broadcast)---------------------------

TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to

majordomo@postgresql.org)