default escape character in like clause

[charly]

Hello,
at the moment I am using MS Access and MS SQLserver as Databases
(development and production).
Since H2 seems to be much better than Access and other (java)
databases, I would like to use H2 interchangeable for MS Access and
SQLserver.
I am using "LIKE" clauses without "ESCAPE" keywords, and Access and
SQLserver do not have default escape characters so I have not dealt
with escape characters. Yet H2 has the default character "\", which
will generate an SQLException, if used in the like pattern without
escaping itself.
I have looked for a way to disable the default escape character:
The following databases do not have a default escape character:

mssqlserver:
http://msdn.microsoft.com/en-us/library/aa933232(SQL.80).aspx
hsqldb:
http://hsqldb.org/doc/guide/ch09.html
derby:
http://db.apache.org/derby/docs/dev/ref/ref-single.html#rrefsqlj23075
db2:
http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/doc/r0000751.htm
oracle:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/conditions007.htm#i1034153

So I tried the H2 Compatibility Modes for these DBs (e.g. ;MODE=DB2 )
in the connection string, but I had no success in disabling the
default escape char.

Is there another connection parameter I could use to disable the
default escape char? .. Or could such connection parameter provided?
Thanks.
Best Regards
Charly

[Thomas Mueller]

Hi,

That's strange... I thought '\' is the standard escape character for
the SQL standard, but it looks like it's not. Currently PostgreSQL and
MySQL work like H2. The PostgreSQL documentation says you can disable
the escape character, but when I test it it doesn't work:
http://www.postgresql.org/docs/8.4/static/functions-matching.html#FUNCTIONS-LIKE
"It's also possible to select no escape character by writing ESCAPE
''" - this seems to work for Oracle however.

> Is there another connection parameter I could use to disable the
> default escape char? .. Or could such connection parameter provided?

No, currently the escape character can not be changed or disabled. I
will fix this in the next release. My plans are:

- Allow to disable the escape mechanism using ESCAPE ''.
- Add a system property h2.defaultEscape (default: \)
- Add a feature request: "Compatibility: use different LIKE ESCAPE
characters depending on the mode (disable for Derby, HSQLDB, DB2,
Oracle)."

I will also extend the documentation for LIKE ESCAPE:

"
When comparing with LIKE, the wildcards characters are _ (any one character)
and % (any characters). The database uses an index when comparing with LIKE
except if the operand starts with a wildcard. To search for the characters % and
_, the characters need to be escaped. The default escape character is
\ (backslash).
To select no escape character, use ESCAPE '' (empty string).
"

Regards,
Thomas

[charly]

Hello Thomas,

Thank you for your response.

> No, currently the escape character can not be changed or disabled. I
> will fix this in the next release. My plans are:
>
> - Allow to disable the escape mechanism using ESCAPE ''.

This would be a proprietary feature of H2 (and maybe other databases
like the above mentioned Oracle)
and is fine, if the SQL-statements are running only on H2 i.e. need
not be portable.

> - Add a system property h2.defaultEscape (default: \)

This fits my use case, if the default can also be disabled with this.
Even though in case of a webapp (with embedded H2) within a servlet-
engine like Tomcat the place for this configuration option is away
from the expected place (web.xml)

> - Add a feature request: "Compatibility: use different LIKE ESCAPE
> characters depending on the mode (disable for Derby, HSQLDB, DB2,
> Oracle)."

This is very fine. I prefer this, because it can be specified in the
connection string, which is already (i.e. even must be) specific for
the used DB and (web)app.

A heretical thought: What about a (new) Compatibility mode
MSSQLServer (in which the default ESCAPE char is disabled)?

Regards
Charly

On 5 Aug., 20:05, Thomas Mueller <thomas.tom.muel...@gmail.com> wrote:
> Hi,
>
> That's strange... I thought '\' is the standard escape character for
> the SQL standard, but it looks like it's not. Currently PostgreSQL and
> MySQL work like H2. The PostgreSQL documentation says you can disable

> the escape character, but when I test it it doesn't work:http://www.postgresql.org/docs/8.4/static/functions-matching.html#FUN...

> "It's also possible to select no escape character by writing ESCAPE
> ''" - this seems to work for Oracle however.
>
> > Is there another connection parameter I could use to disable the
> > default escape char? .. Or could such connection parameter provided?
>
> No, currently the escape character can not be changed or disabled. I
> will fix this in the next release. My plans are:
>
> - Allow to disable the escape mechanism using ESCAPE ''.
> - Add a system property h2.defaultEscape (default: \)
> - Add a feature request: "Compatibility: use different LIKE ESCAPE
> characters depending on the mode (disable for Derby, HSQLDB, DB2,
> Oracle)."
>
> I will also extend the documentation for LIKE ESCAPE:
>
> "
> When comparing with LIKE, the wildcards characters are _ (any one character)
> and % (any characters). The database uses an index when comparing with LIKE
> except if the operand starts with a wildcard. To search for the characters % and
> _, the characters need to be escaped. The default escape character is
> \ (backslash).
> To select no escape character, use ESCAPE '' (empty string).
> "
>
> Regards,
> Thomas
>

> On Sun, Aug 2, 2009 at 1:06 PM, charly<gartenzwer...@googlemail.com> wrote:
>
> > Hello,
> > at the moment I am using  MS Access and MS SQLserver as Databases
> > (development and production).
> > Since H2 seems to be much better than Access and other (java)
> > databases,  I would like to use H2 interchangeable for MS Access and
> > SQLserver.
> > I am using "LIKE" clauses without "ESCAPE" keywords, and Access and
> > SQLserver do not have default escape characters so I have not dealt
> > with escape characters. Yet H2 has the default character "\", which
> > will generate an SQLException, if used in the like pattern without
> > escaping itself.
> > I have looked for a way to disable the default escape character:
> > The following databases do not have a default escape character:
>
> > mssqlserver:
> >http://msdn.microsoft.com/en-us/library/aa933232(SQL.80).aspx
> > hsqldb:
> >http://hsqldb.org/doc/guide/ch09.html
> > derby:
> >http://db.apache.org/derby/docs/dev/ref/ref-single.html#rrefsqlj23075
> > db2:

> >http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2....
> > oracle:
> >http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/condit...

[Thomas Mueller]

Hi,

> This would be a proprietary feature of H2 (and maybe other databases
> like the above mentioned Oracle)

Yes, it is supported by Oracle, MySQL, and is supposed to be supported
by PostgreSQL (maybe only in newer versions).

> A heretical thought:  What about a (new) Compatibility mode
> MSSQLServer (in which the default ESCAPE char is disabled)?

Sure.

Regards,
Thomas

[Thomas Kellerer]

On 9 Aug., 16:53, charly <gartenzwer...@googlemail.com> wrote:
> This would be a proprietary feature of H2 (and maybe other databases
> like the above mentioned Oracle)
> and is fine, if  the SQL-statements are running only on H2  i.e. need
> not be portable.

I'm not sure what kind of escaping is being talked about.

If this is the character that is supposed to turn a SQL wildcard in a
regular character (so that you can e.g. search for % sign) then there
_is_ a standard for this:

SELECT *
FROM my_table
WHERE some_column LIKE '\%' escape '\';

This is defined in the SQL standard and to supported at least
Postgres, Oracle, SQL Server and DB2.

Regards
Thomas

[charly]

> I'm not sure what kind of escaping is being talked about.

Yes we are talking about the ESCAPE char in the LIKE clause.
Especially about if there is an <default> ESCAPE character when the
ESCAPE keyword is NOT! provided
And yes: Standard is that this ESCAPE keyword can be used!

But it seems its not standard,

1. that there is a default ESCAPE char active, if the keyword ESCAPE
is NOT used

2. that ESCAPE '' i.e. with empty string (meaning "no escape char
active") can be provided ..
and this what I meant with proprietary feature

SQL Server also does have no default ESCAPE char. At least at
following site
http://msdn.microsoft.com/en-us/library/aa933232(SQL.80).aspx
it is mentioned:
"To search for the percent sign as a character instead of as a
wildcard character, the ESCAPE keyword and escape character must be
provided"
Nothing about a default. And I have verified this just now (at least
that it does not have the '\' as default)

Regards
Charly

On 13 Aug., 08:33, Thomas Kellerer <google-gro...@sql-workbench.net>
wrote:

[Thomas Kellerer]

> But it seems its not standard

> 1. that there is a default ESCAPE char active, if the keyword ESCAPE
> is NOT used

Correct. The standard requires that no escaping takes place if ESCAPE
is not specified.

> 2. that ESCAPE ''  i.e. with empty string (meaning "no escape char
> active")  can be provided ..

Also correct. The standard requires that an error is thrown if the
length of the escape character is anything else than 1 (one)

Thomas

[Thomas Mueller]

Hi,

> I'm not sure what kind of escaping is being talked about.

We are talking about the _default_ escape character to use if the
ESCAPE '...' clause is _not_ used. H2, PostgreSQL, and MySQL use '\'
by default. Other databases don't seem to use any escape character by
default (at least Derby, HSQLDB, DB2, and Oracle).

The second question is how to _disable_ the _default_ escape
character. MySQL, Oracle (not sure why), and now H2 support ESCAPE ''
(empty string).

Regards,
Thomas

[charly]

> A heretical thought:  What about a (new) Compatibility mode
> MSSQLServer (in which the default ESCAPE char is disabled)?
>

Ooops. At the moment I saw that there is already this mode.
I don't know how I could overlook this..
Sorry..

Regards Charly