Overriding database collation in queries for LIKE operators

[Jon.Hakkinen]

Hi all,

In SQL Server I can use the COLLATE clause to sort, compare(=) and do
LIKE comparisons in queries, overiding the server collation. For
instance I can write a query like
select * from T where (someStringCol COLLATE Latin1_General_CI_AI
LIKE '%Some Text%')

DB2 provides the COLLATION_KEY_BIT function, which will work fine for
sorting and "=" comparisons, but obviously not for "LIKE", as it
returns a key.
Is there any way I can do for instance case insensitive or accent
insensitive searches in DB2 when the database collation is case/accent
sensitive? (Without using to_upper, etc)
thanks!

[Serge Rielau]

Jo**********@gmail.com wrote:

Hi all,

In SQL Server I can use the COLLATE clause to sort, compare(=) and do
LIKE comparisons in queries, overiding the server collation. For
instance I can write a query like
select * from T where (someStringCol COLLATE Latin1_General_CI_AI
LIKE '%Some Text%')

DB2 provides the COLLATION_KEY_BIT function, which will work fine for
sorting and "=" comparisons, but obviously not for "LIKE", as it
returns a key.
Is there any way I can do for instance case insensitive or accent
insensitive searches in DB2 when the database collation is case/accent
sensitive? (Without using to_upper, etc)

Not built-in. You can use the libraries here:
www.ibm.com/software/globalization/icu/index.jsp
To roll your own.
I while ago I tinkered with them a bit. If you send me an email I'll
gladly sent you as-is code to get you jump-started.

I'd also be interested in your usage. The need for "session level
collation" is highly contested. Any input welcome.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab