=====NLS_COMP und NLS_SORT für Linguistische Vergleiche und Case Ignore in der Oracle Datenbank ===== **10g/11g** **Problemstellung:**\\ Im MS SQLServer erfolgt die Wildcard Suche über ein Textfeld automatisch unabhängig von der Groß- und Kleinschreibung. **Lösung:**\\ Ein ähnliche Verhalten kann auch in der Oracle DB mit dem beiden NLS Parametern **NLS_COMP** und **NLS_SORT** erreicht werden. Dazu muss der NLS_COMP Parameter auf den Wert **LINGUISTIC** gesetzt werden. Je nach dem Parameter ob nun der Parameter NLS_SORT auf einen Wert mit **_CI** oder **_AI** gesetzt ist, wird dann (**und nur dann!**) ein "linguistisch" Case in sensitiver und/oder Accent in sensitiver Vergleich durchgeführt. (siehe Tabelle nach dem SQL Beispiel) **Beispiel Einstellen der Session**: -- Aktuelle Einstellung überprüfen column value format a20 column parameter format a30 select parameter,value from nls_session_parameters where parameter like '%O%' ; PARAMETER VALUE ------------------------------ -------------------- NLS_SORT BINARY NLS_COMP GERMAN .... -- Werte einstellen alter session set NLS_COMP=LINGUISTIC; alter session set NLS_SORT=XGERMAN_AI; -- Erneut prüfen select parameter,value from nls_session_parameters where parameter like '%O%' ; PARAMETER VALUE ------------------------------ -------------------- NLS_SORT XGERMAN_CI NLS_COMP LINGUISTIC Die NLS Settings werden immer vom Client an die Datenbank Session vererbt! Zwar können die Einstellung auch DB global gesetzt werden, der Client (Java, .NEt, SQL*Plus) kann das aber jederzeit wieder überschreiben! **Test:** -- Testdaten create table t (wert varchar2(32)); insert into t values ('Gunther'); commit; -- select * from t where wert = 'gunther'; Wert ----- Gunther ==== Zusammenhang NLS_COMP und NLS_SORT ===== Der Default für NLS_COMP ist *binary*, d.h. keine linguistischer Vergleich.\\ Steht der Wert auf LINGUISTIC (ANSI alternativ nur für Rückwärtskompatibilität!) ist je nach eingestelltem Wert für NLS_SORT das folgende Verhalten zu beobachteten: ^NLS_COMP = ^ LINGUISTIC ^ ^ ^ ^ ^ ^NLS_SORT ^Beschreibung ^ Vergleich \\ 'ABC'= 'abc' ^Vergleich \\ 'Pèr'= 'per' ^ > Vergleich \\ 'Z'>'A' ^ Sortierreihenfolge von \\ 1 2 3 a b c ö ü ä _ ! A B C ß^ |BINARY |Binärer Vergleich auf den ASCII Wert | false | false | false | ! 1 2 3 A B C _ a b c ß ä ö ü | |GERMAN |Deutsche Sortierung | false | false | true | ! _ a A ä b B c C ö ß ü 1 2 3 | |XGERMAN |Deutsche Sortierung ß wird berücksichtigt! | false | false | true | ! _ a A ä b B c C ö ß ü 1 2 3 | |XGERMAN_DIN |Deutsche Telefonbuch Norm | false | false | true | ! _ A a ä B b C c ö ß ü 1 2 3 | |XGERMAN_CI |_CI = postfixes für case-insensitive | true | false| false |! _ a A ä b B C c ö ß ü 1 2 3 | |XGERMAN_AI |_AI = postfixes für accent-insensitive | true | true | true | ! _ ä a A b B C c ö ß ü 1 2 3 | |GENERIC_M_CI |Multilingual nach [[http://www.iso.org/iso/home/store/catalogue_ics/catalogue_detail_ics.htm?csnumber=57976|ISO/IEC 14651:2011]] \\ case-insensitive | true | false| true | _ ! 1 2 3 a A ä b B C c ö ß ü | |GENERIC_M_AI | Multilingual nach [[http://www.iso.org/iso/home/store/catalogue_ics/catalogue_detail_ics.htm?csnumber=57976|ISO/IEC 14651:2011]] \\ accent-insensitive | true | true| true | 1 2 3 A a ä b B C c ö ß ü _ ! | |FRENCH_M_AI | Französisch |true | true | true | 1 2 3 A a ä b B C c ö ß ü _ ! | Die Kürzel im Detail: * Ohne ein **_M** => Monolingual Linguistic Sorts * mit einem **_M** => Multilingual LInguistic Sorts * Postfix **_CI** => case-insensitive * Postfix **_AI** => accent-insensitive und case-insensitive siehe auch : http://docs.oracle.com/cd/E11882_01/server.112/e10729/applocaledata.htm#NLSPG593 ===== Probleme ===== Wird mit den "linguistischen" Einstellungen über NLS_COMP gearbeitet, wird die Where Bedingung des SQL Statements umgeschrieben und auf beiden Seiten des Vergleiches Funktionen eingesetzt. Beispiel: SYS@GPI-WORKGROUP\JUPITER>select * from t where wert = 'pippèrr'; WERT ---------- Pippèrr Ausf³hrungsplan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T | 1 | 2 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(NLSSORT("WERT",'nls_sort=''FRENCH_M_AI''')=HEXTORAW(' 023F021B023F023F024F02450245') ) Beachten Sie die Filter Bedingung:\\ Aus einem "harmlosen" **wert = 'pippèrr'** \\ wird ein **(NLSSORT("WERT",nls_sort=FRENCH_M_AI)=HEXTORAW('023F021B023F023F024F02450245') )** ! Damit wird kein Index mehr verwendet! Alternativ kann auch ein [[http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch5lingsort.htm#NLSPG294|linguistischer Index]] angelegt werden: CREATE INDEX idx_t ON t (NLSSORT(wert, 'NLS_SORT=FRENCH_M_AI')); select * from t where wert = 'pippèrr'; Ausf³hrungsplan ---------------------------------------------------------- Plan hash value: 1594971208 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 2 | 1 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_T | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access(NLSSORT("WERT",'nls_sort=''FRENCH_M_AI''')=HEXTORAW('023F021B023F023F024F02450245') ) ===== NLSSORT ===== Mit der Funktion NLSSORT im order by kann auch die entschrechende Reichenfolge erzwungen werden (unabhängig von den NLS Settings in der Session!): SQL> select * from t order by nlssort(wert,'NLS_SORT=BINARY') WERT --------------- ! 1 2 3 A B C _ a b c ß ä ö ü 15 rows selected. SQL> select * from t order by nlssort(wert,'NLS_SORT=XGERMAN_DIN') WERT --------------- ! _ A a ä B b C c ö ß ü 1 2 3 15 rows selected. SQL> select * from t order by nlssort(wert,'NLS_SORT=XGERMAN_AI') WERT --------------- ! _ ä a A b B C c ö ß ü 1 2 3 15 rows selected. SQL> select * from t order by nlssort(wert,'NLS_SORT=GENERIC_M_CI') WERT --------------- _ ! 1 2 3 a A ä b B C c ö ß ü 15 rows selected. (Werte zur Lesbarkeit nach rechts geschrieben .-) !) ===== Das ß in UPPER Vergleichen ===== Soll das ß in einem UPPER zu einem doppel s umgewandelt werden muss als NLS Setting XGERMAN für [[http://oracle.informatik.haw-hamburg.de/server.111/b28286/functions105.htm|NLS_UPPER]] eingesetzt werden! Beispiel: **ß wird nicht gewandelt** select * from DUAL where UPPER ('größe') = 'GRÖSSE'; no rows selected. select UPPER ('größe') from DUAL UPPER('GRÖßE') -------------- GRÖßE **ß wird nicht gewandelt** select * from DUAL where NLS_UPPER ('größe', 'NLS_SORT=GERMAN') = 'GRÖSSE'; no rows selected. select NLS_UPPER ('größe', 'NLS_SORT=GERMAN') from DUAL NLS_UPPER('GRÖßE','NLS_SORT=GERMAN') ------------------------------------ GRÖßE ** ß wird zu SS gewandet durch den XGERMAN NLS Parameter! ** select * from DUAL where NLS_UPPER ('größe', 'NLS_SORT=XGERMAN') = 'GRÖSSE' DUMMY ----- X 1 row selected. select NLS_UPPER ('größe', 'NLS_SORT=XGERMAN') from DUAL NLS_UPPER('GRÖßE','NLS_SORT=XGERMAN') ------------------------------------- GRÖSSE ====Quelle:==== * http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams143.htm#REFRN10117 * http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch5lingsort.htm Support: * Linguistic Sorting - Frequently Asked Questions(Doc ID 227335.1)