Link to home
Start Free TrialLog in
Avatar of GuillemGuillem
GuillemGuillem

asked on

Accent insensitive queries in Oracle

Hello Guys!

We got a big problem. We are migrating a database from MSSql2000 to Oracle. In the first one we could use Collation to make accent insensitive queries. But with Oracle we haven't got this option.

We would like to know how to configure Oracle database or how to make accent insensitive queries.

For example: we need to perform a search of a name like 'josé', but if in the database 'jose' was inserted we couldn't find it. The same way, if we perform a search of a name like 'jose', but if in the database 'josé' was inserted we couldn't find it too.
Avatar of Bigfam5
Bigfam5


How was it done in MsSql2000 ?

Avatar of GuillemGuillem

ASKER

In SQL Server there is an option in design mode (Enterprise manager)

If you click on a field there is an column property called collation. If you especify there "Modern_Spanish_CI_AI", SQL Server searches on this field become accent insensitive.

Thanks for your interest!
Avatar of seazodiac
Oracle use the NLSSORT to achieve the same task as Microsoft use COLLATE command
Here is the answer:


Microsoft:
SELECT column1 FROM Table1
WHERE column1 = 'Vide' COLLATE Modern_Spanish_CI_AI
ORDER BY column1 COLLATE Modern_Spanish_CI_AI

Oracle:
CREATE INDEX test_idx ON test(NLSSORT(col, 'NLS_SORT=SPANISH'));
sorry, but nls_sort is used  to define a sort order from queries.
It doesn't  solve my problem. This is to avoid accents when we compare strings.

thanks for your interest
Hi all!

I'm trying to execute this code to change my comp method:

BEGIN
 EXECUTE immediate 'ALTER SESSION SET NLS_SORT=GENERIC_BASELETTER';
 EXECUTE immediate 'ALTER SESSION SET NLS_COMP=ANSI';
END;

I have created an index over my searchable column, also. But I get no result when querying over it.


What oracle Server version you are running??
Oracle 9i
ASKER CERTIFIED SOLUTION
Avatar of seazodiac
seazodiac
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
We will try the convert option

Thanks!


One thing to remember when using the convert, it would obviate an index until you build a function based one