Escaping strings - different for = and LIKE ?

From: Doug Fields <dfields-postgres(at)pexicom(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Escaping strings - different for = and LIKE ?
Date: 2002-03-06 18:32:53
Message-ID: 5.1.0.14.2.20020306132732.035ba300@mail.pexicom.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Hello all,

I've been doing a lot of JDBC queries involving where clauses such as

varchar = '...'

and

varchar LIKE '...'

Where the ... contents are user-entered and escaped using an escape method
I wrote (since we do not seem to have a publically accessible one in the
JDBC drivers).

I have noticed that escapes on the = work as advertised (put a backslash in
front of special characters and the character loses special meaning) but in
LIKE expressions, a new level of escaping is required. For example:

\\_ - ILIKE for a _ without special meaning
\\% - ILIKE for a % without special meaning
\\\\ - ILIKE for a \ without special meaning (matches a literal backslash)
\' - ILIKE for a '

Whereas, in an = clause, one can use

_ - without escapes or as \_
% - same as _
\\ - for a single \
\' - for a single quote

Unfortunately, this discrepancy seems to require me to have two different
"escape" functions: one for = comparisons, and another for LIKE comparisons.

Why can't I simply use \\ and \% and \_ in LIKE just as in =?

Many thanks,

Doug

Browse pgsql-jdbc by date

  From Date Subject
Next Message Dave Cramer 2002-03-06 18:44:16 Re: Pooled Connections (was Connections with Tomcat 3.2.4 and PostgreSQL 7.1.3)
Previous Message tsmets 2002-03-06 15:50:45 Re: Pooled Connections (was Connections with Tomcat 3.2.4 and PostgreSQL 7.1.3)