prog:sql_nls_comp_nls_sort_inguistic_search_sorting
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende Überarbeitung | |||
prog:sql_nls_comp_nls_sort_inguistic_search_sorting [2013/03/19 21:06] – [Das ß in UPPER Vergleichen] gpipperr | prog:sql_nls_comp_nls_sort_inguistic_search_sorting [2014/07/08 17:14] (aktuell) – [Quelle:] gpipperr | ||
---|---|---|---|
Zeile 1: | Zeile 1: | ||
+ | =====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 < | ||
+ | |||
+ | |||
+ | **Beispiel Einstellen der Session**: | ||
+ | |||
+ | <code sql> | ||
+ | |||
+ | -- Aktuelle Einstellung überprüfen | ||
+ | column value format a20 | ||
+ | column parameter format a30 | ||
+ | select parameter, | ||
+ | PARAMETER | ||
+ | ------------------------------ -------------------- | ||
+ | NLS_SORT | ||
+ | NLS_COMP | ||
+ | .... | ||
+ | |||
+ | |||
+ | -- Werte einstellen | ||
+ | alter session set NLS_COMP=LINGUISTIC; | ||
+ | alter session set NLS_SORT=XGERMAN_AI; | ||
+ | |||
+ | -- Erneut prüfen | ||
+ | |||
+ | select parameter, | ||
+ | PARAMETER | ||
+ | ------------------------------ -------------------- | ||
+ | NLS_SORT | ||
+ | NLS_COMP | ||
+ | |||
+ | </ | ||
+ | |||
+ | <note important> | ||
+ | |||
+ | |||
+ | **Test:** | ||
+ | |||
+ | <code sql> | ||
+ | |||
+ | -- Testdaten | ||
+ | create table t (wert varchar2(32)); | ||
+ | insert into t values (' | ||
+ | commit; | ||
+ | |||
+ | -- | ||
+ | |||
+ | select * from t where wert = ' | ||
+ | |||
+ | 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!) | ||
+ | |||
+ | |||
+ | ^NLS_COMP = ^ LINGUISTIC ^ ^ ^ ^ ^ | ||
+ | ^NLS_SORT ^Beschreibung ^ Vergleich \\ ' | ||
+ | |BINARY | ||
+ | |GERMAN | ||
+ | |XGERMAN | ||
+ | |XGERMAN_DIN | ||
+ | |XGERMAN_CI | ||
+ | |XGERMAN_AI | ||
+ | |GENERIC_M_CI |Multilingual nach [[http:// | ||
+ | |GENERIC_M_AI | Multilingual nach [[http:// | ||
+ | |FRENCH_M_AI | ||
+ | |||
+ | |||
+ | Die Kürzel im Detail: | ||
+ | |||
+ | * Ohne ein **_M** | ||
+ | * mit einem **_M** | ||
+ | * Postfix | ||
+ | * Postfix | ||
+ | |||
+ | siehe auch : http:// | ||
+ | |||
+ | |||
+ | ===== Probleme ===== | ||
+ | |||
+ | Wird mit den " | ||
+ | |||
+ | Beispiel: | ||
+ | <code sql> | ||
+ | SYS@GPI-WORKGROUP\JUPITER> | ||
+ | |||
+ | WERT | ||
+ | ---------- | ||
+ | Pippèrr | ||
+ | |||
+ | |||
+ | Ausf³hrungsplan | ||
+ | ---------------------------------------------------------- | ||
+ | Plan hash value: 1601196873 | ||
+ | |||
+ | -------------------------------------------------------------------------- | ||
+ | | Id | Operation | ||
+ | -------------------------------------------------------------------------- | ||
+ | | 0 | SELECT STATEMENT | ||
+ | |* 1 | TABLE ACCESS FULL| T | 1 | 2 | | ||
+ | -------------------------------------------------------------------------- | ||
+ | |||
+ | Predicate Information (identified by operation id): | ||
+ | --------------------------------------------------- | ||
+ | |||
+ | 1 - filter(NLSSORT(" | ||
+ | 023F021B023F023F024F02450245' | ||
+ | | ||
+ | </ | ||
+ | |||
+ | Beachten Sie die Filter Bedingung: | ||
+ | Aus einem " | ||
+ | wird ein **(NLSSORT(" | ||
+ | |||
+ | Damit wird kein Index mehr verwendet! | ||
+ | |||
+ | |||
+ | Alternativ kann auch ein [[http:// | ||
+ | |||
+ | <code sql> | ||
+ | |||
+ | CREATE INDEX idx_t ON t (NLSSORT(wert, | ||
+ | |||
+ | |||
+ | select * from t where wert = ' | ||
+ | |||
+ | Ausf³hrungsplan | ||
+ | ---------------------------------------------------------- | ||
+ | Plan hash value: 1594971208 | ||
+ | |||
+ | ------------------------------------------------------------------------------------- | ||
+ | | Id | Operation | ||
+ | ------------------------------------------------------------------------------------- | ||
+ | | 0 | SELECT STATEMENT | ||
+ | | 1 | TABLE ACCESS BY INDEX ROWID| T | ||
+ | |* 2 | INDEX RANGE SCAN | IDX_T | 1 | | ||
+ | ------------------------------------------------------------------------------------- | ||
+ | |||
+ | Predicate Information (identified by operation id): | ||
+ | --------------------------------------------------- | ||
+ | |||
+ | 2 - access(NLSSORT(" | ||
+ | |||
+ | </ | ||
+ | |||
+ | ===== NLSSORT ===== | ||
+ | |||
+ | Mit der Funktion NLSSORT im order by kann auch die entschrechende Reichenfolge erzwungen werden (unabhängig von den NLS Settings in der Session!): | ||
+ | |||
+ | <code sql> | ||
+ | SQL> select * from t order by nlssort(wert,' | ||
+ | |||
+ | WERT | ||
+ | --------------- | ||
+ | ! 1 2 3 A B C _ a b c ß ä ö ü | ||
+ | |||
+ | 15 rows selected. | ||
+ | |||
+ | SQL> select * from t order by nlssort(wert,' | ||
+ | |||
+ | WERT | ||
+ | --------------- | ||
+ | ! _ A a ä B b C c ö ß ü 1 2 3 | ||
+ | |||
+ | 15 rows selected. | ||
+ | |||
+ | SQL> select * from t order by nlssort(wert,' | ||
+ | |||
+ | WERT | ||
+ | --------------- | ||
+ | ! _ ä a A b B C c ö ß ü 1 2 3 | ||
+ | |||
+ | 15 rows selected. | ||
+ | |||
+ | SQL> select * from t order by nlssort(wert,' | ||
+ | |||
+ | 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:// | ||
+ | |||
+ | |||
+ | Beispiel: | ||
+ | |||
+ | **ß wird nicht gewandelt** | ||
+ | |||
+ | <code sql> | ||
+ | |||
+ | select * from DUAL where UPPER (' | ||
+ | |||
+ | no rows selected. | ||
+ | |||
+ | |||
+ | select UPPER (' | ||
+ | |||
+ | UPPER(' | ||
+ | -------------- | ||
+ | GRÖßE | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | **ß wird nicht gewandelt** | ||
+ | |||
+ | <code sql> | ||
+ | |||
+ | select * from DUAL where NLS_UPPER (' | ||
+ | |||
+ | no rows selected. | ||
+ | |||
+ | |||
+ | select NLS_UPPER (' | ||
+ | |||
+ | |||
+ | NLS_UPPER(' | ||
+ | ------------------------------------ | ||
+ | GRÖßE | ||
+ | | ||
+ | |||
+ | </ | ||
+ | |||
+ | ** ß wird zu SS gewandet durch den XGERMAN NLS Parameter! | ||
+ | |||
+ | <code sql> | ||
+ | |||
+ | select * from DUAL where NLS_UPPER (' | ||
+ | |||
+ | DUMMY | ||
+ | ----- | ||
+ | X | ||
+ | |||
+ | 1 row selected. | ||
+ | |||
+ | |||
+ | select NLS_UPPER (' | ||
+ | |||
+ | NLS_UPPER(' | ||
+ | ------------------------------------- | ||
+ | GRÖSSE | ||
+ | |||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ====Quelle: | ||
+ | * http:// | ||
+ | * http:// | ||
+ | |||
+ | Support: | ||
+ | |||
+ | * Linguistic Sorting - Frequently Asked Questions(Doc ID 227335.1) | ||
+ | |||
+ | |||
prog/sql_nls_comp_nls_sort_inguistic_search_sorting.txt · Zuletzt geändert: 2014/07/08 17:14 von gpipperr