Thread: LIKE predicate and '\' character
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
On Wed, Nov 07, 2001 at 12:56:46PM -0000, Andy Hallam wrote: The PostgreSQL parser "eat" one '\' on arbitrary place in query. Youyou want put '\' to some function (operator) you mustuse '\\'. 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).
> 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
> 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
>
>
>
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) > > -- > Karel Zak <zakkr@zf.jcu.cz> > http://home.zf.jcu.cz/~zakkr/ > > C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
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) > > > > -- > > Karel Zak <zakkr@zf.jcu.cz> > > http://home.zf.jcu.cz/~zakkr/ > > > > C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > >