Inhaltsverzeichnis
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 <SPRACHE>_CI oder <SPRACHE>_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
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 ISO/IEC 14651:2011 case-insensitive | true | false | true | _ ! 1 2 3 a A ä b B C c ö ß ü |
GENERIC_M_AI | Multilingual nach 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 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 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:
Support:
- Linguistic Sorting - Frequently Asked Questions(Doc ID 227335.1)