Overriding database collation in queries for LIKE operators

[Jon.Hakkinen@gmail.com]

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_B IT 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]

Re: Overriding database collation in queries for LIKE operators

Jon.Hakkinen@gm ail.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_B IT 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:

https://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