Our R&D did struggle with Oracle setting for Czech specific search. Following might be the solution.
ALTER SESSION SET NLS_COMP=ANSI;
ALTER SESSION SET NLS_SORT=GENERIC_BASELETTER;
– this should be XCZECH we discussed previously
First 3 screenshots are from setting up the DB where you can see coding I’ve used.
4th screenshot shows you – first terminal window result when session is not using NLS setting and second terminal window where NLS is set accordingly.
Please ignore Czech strange characters representation caused by incorrect terminal character representation.
Imported data are in CSV file.
Word I used for test was „česká“ and all relative option = see csv file. As you can see all 9 options present in table are selected correctly.
Resources I’ve used:
http://docs.oracle.com/cd/B10501_01/server.920/a96529/ch4.htm
http://docs.oracle.com/cd/B10501_01/server.920/a96529/ch4.htm#1005868
Highly recommend to read following NLS_COMP and language indexing.
http://docs.oracle.com/cd/B28359_01/server.111/b28298/ch5lingsort.htm#i1006421
http://docs.oracle.com/cd/B28359_01/server.111/b28298/ch3globenv.htm#i1008458
Example: Setting Up a French Linguistic Index
The following example shows how to set up a French linguistic index. You may want to set NLS_SORT as a client environment variable instead of using the ALTER SESSION statement.
ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;
ALTER SESSION SET NLS_COMP = ANSI;
ALTER SESSION SET NLS_SORT=’FRENCH‘;
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;
CREATE INDEX test_idx ON test(NLSSORT(col, ‚NLS_SORT=FRENCH‘));
SELECT * FROM test ORDER BY col;
SELECT * FROM test WHERE col > ‚JJJ‘;
Improving Case-Insensitive Searches with a Function-Based Index
You can create a function-based index that improves the performance of case-insensitive searches. For example:
CREATE INDEX case_insensitive_ind ON employees(NLS_UPPER(first_name));
SELECT * FROM employees WHERE NLS_UPPER(first_name) = ‚KARL‘;
Performing a Generic Base Letter Search you can perform a search that ignores case and diacritics. Enter the following statements:
ALTER SESSION SET NLS_COMP=ANSI;
ALTER SESSION SET NLS_SORT=GENERIC_BASELETTER;
Then enter a statement similar to the following:
SELECT * FROM emp WHERE ename=’miller‘;
This statement can now return names that include the following:
Miller
MILLER
Millér
Other resources.
http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch5lingsort.htm#i1008708
http://www.dbsvet.cz/view.php?cisloclanku=2007051401
http://docs.oracle.com/cd/B19306_01/server.102/b14225/loe.htm
http://docs.oracle.com/cd/B19306_01/server.102/b14225/toc.htm
http://docs.oracle.com/cd/B19306_01/server.102/b14200/conditions007.htm
http://www.oracle.com/technetwork/database/globalization/nls-lang-099431.html
http://docs.oracle.com/cd/A58617_01/server.804/a58242/ch4.htm#17044
http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch5lingsort.htm#i1008708
