Diacritics in in like clause, Globalization support Options

[Ana]

Hello,

I use to write clauses like that to ignore case sensitive and diacritics :

alter session set nls_comp=ANSI
/
alter session set nls_sort=generic_baseletter
/
select * from tab28
where lastName = 'Médar'

Result

lastName
---------

Medar
médar
MEDAR
medar

Next day, i try this, to find everyone witch last name is beginning by Médar ignoring diacritics and case sensitive :

alter session set nls_comp=ANSI
/
alter session set nls_sort=generic_baseletter
/
select * from tab28
where lastName like 'Médar%'

and Oracle told me he doesn't find any thing !!!

Does anyone know how to resolve this problem ? i'm using Oracle 9i.

Thanks a lot ...

Ana

[burleson]

Hi,

Oracle10g release 2 has a new feature that does case insensitive searches:

http://www.oracle.com/corporate/press/2004_dec/open12.html

However, you would have to go to great lengths to ignore diacritics.

I would write a built-in function in PL/SQL to transform the query to whatever for it resides in the database:

select stuff from tab where lastName = transform_string('Médar');

[Ana]

Thanks,

But, i'm quite confused coz the data can have diacritics or not, can be in upper case or not, it's very heterogeneous.

I want to find all the clients whose name is beginnig with 'médar' or 'Médar' or 'MEDAR' or 'medar' or 'MEdar' and so on ...

I don't really see how to write a pl/sql function in this case : trying all combinaisons of characters will spent a very long time ?


Ana

[burleson]

Hi Ana,

Oh! Using the LIKE parameter is an whole new issue:

http://www.dba-oracle.com/t_oracle10g_rele...ve_searches.htm

Here is a discussion on case insensitive searches with the "LIKE operator":

http://www.iherve.com/oracle/case_ins.htm

Also, this from the Kim Floss book:

http://www.oracle-training.cc/cbo_194.htm

[Ana]

Hi,

I'm using actually Oracle 9i, it seems that Oracle 10g release 2 is up to date for my request.

Trying to catch a trial version...

Thanks

Ana