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.
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.
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!
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!
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'));
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'));
ASKER
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
It doesn't solve my problem. This is to avoid accents when we compare strings.
thanks for your interest
ASKER
Hi all!
I'm trying to execute this code to change my comp method:
BEGIN
EXECUTE immediate 'ALTER SESSION SET NLS_SORT=GENERIC_BASELETTE R';
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.
I'm trying to execute this code to change my comp method:
BEGIN
EXECUTE immediate 'ALTER SESSION SET NLS_SORT=GENERIC_BASELETTE
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??
ASKER
Oracle 9i
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
We will try the convert option
Thanks!
Thanks!
One thing to remember when using the convert, it would obviate an index until you build a function based one
How was it done in MsSql2000 ?