Sortierungsunterstützung

Mithilfe von Sortierung können Sie alternative Regeln für den Vergleich von Zeichenfolgen angeben, damit Daten anhand einer bestimmten Sprache oder anderer benutzerdefinierter Regeln verglichen und sortiert werden.

Übersicht zur Sortierungsunterstützung

In den folgenden Abschnitten wird erklärt, was die Sortierung (Collation) ist und wie Sie die Sortierung beim Vergleichen von Zeichenfolgen verwenden:

Erläuterungen zur Sortierung

Textzeichenfolgen in Snowflake werden mit dem Zeichensatz UTF-8 gespeichert. Standardmäßig werden Zeichenfolgen anhand der Unicode-Codes verglichen, die die Zeichen in der Zeichenfolge darstellen.

Das Vergleichen von Zeichenfolgen auf Grundlage ihrer UTF-8-Zeichenrepräsentationen führt jedoch möglicherweise nicht zum gewünschten oder erwarteten Verhalten. Beispiel:

  • Wenn Sonderzeichen in einer bestimmten Sprache nicht nach den Sortierungsstandards dieser Sprache sortiert werden, kann das Sortieren zu unerwarteten Ergebnissen führen.

  • Möglicherweise wollen Sie, dass die Zeichenfolgen nach anderen Regeln sortiert werden, z. B. indem ignoriert wird, ob Zeichen in Groß- oder Kleinbuchstaben geschrieben sind.

Mithilfe von Sortierung können Sie explizit die Regeln angeben, die zum Vergleichen von Zeichenfolgen verwendet werden sollen, basierend auf:

  • Unterschiedlichen Gebietsschemata (d. h. unterschiedliche Zeichensätze für verschiedene Sprachen).

  • Unterscheidung nach Groß-/Kleinschreibung (d. h. ob Zeichenfolgenvergleiche verwendet werden sollen, bei denen Groß- und Kleinschreibung berücksichtigt wird oder nicht, ohne dass die Funktionen UPPER oder LOWER zum Konvertieren der Zeichenfolgen explizit aufgerufen werden).

  • Unterscheidung nach Akzent (z. B. ob Z, Ź und Ż als gleicher Buchstabe oder als verschiedene Buchstaben betrachtet werden).

  • Unterscheidung nach Interpunktion (d. h. ob Vergleiche nur Buchstaben oder alle Zeichen enthalten). Wenn ein Vergleich beispielsweise nicht nach Interpunktion unterscheidet, werden A-B-C und ABC als gleichwertig behandelt.

  • Zusätzliche Optionen, z. B. Einstellungen für die Sortierung anhand des ersten Buchstabens in einer Zeichenfolge und das Abschneiden von führenden und/oder nachstehenden Leerzeichen.

Anwendungsgebiete für Sortierung

Die Sortierung kann für eine Vielzahl von Vorgängen verwendet werden, einschließlich (aber nicht beschränkt auf):

Verwendung

Beispiel

Verknüpfung

Einfacher Vergleich

... WHERE column1 = column2 ...

WHERE

Joins

... ON table1.column1 = table2.column2 ...

JOIN

Sortierung

... ORDER BY column1 ...

ORDER BY

Top-K-Sortierung

... ORDER BY column1 LIMIT N ...

LIMIT / FETCH

Aggregation

... GROUP BY ...

GROUP BY

Fensterfunktionen

... PARTITION BY ... ORDER BY ...

Fensterfunktionen

Skalarfunktionen

... LEAST(column1, column2, column3) ...

Skalarfunktionen

Aggregatfunktionen

... MIN(column1), MAX(column1) ...

Aggregatfunktionen

Daten-Clustering

... CLUSTER BY (column1) ...

Gruppierungsschlüssel und geclusterte Tabellen

Sortierungssteuerung

Die Sortierungssteuerung ist granular. Sie können die zu verwendende Sortierung explizit angeben für:

  • Ein Konto, mithilfe des Kontoebenenparameters DEFAULT_DDL_COLLATION

  • Alle Spalten in allen einer Datenbank hinzugefügten Tabellen, mithilfe des Befehls ALTER DATABASE

  • Alle Spalten in allen einem Schema hinzugefügten Tabellen, mithilfe des Befehls ALTER SCHEMA

  • Alle Spalten, die einer Tabelle hinzugefügt wurden, mithilfe des Befehls ALTER TABLE

  • Einzelne Spalten in einer Tabelle mit dem Befehl CREATE TABLE.

  • Einen bestimmten Vergleich innerhalb einer SQL-Anweisung (z. B. WHERE col1 = col2). Wenn auf eine Anweisung mehrere Sortierungen angewendet werden, wählt Snowflake die zu verwendende Sortierung anhand der Priorität aus. Weitere Details zur Priorität finden Sie unter Sortierungsrangfolge bei Operationen mit mehreren Zeichenfolgen.

SQL-Konstrukte für Sortierung

Sie können die folgenden SQL-Konstrukte für die Sortierung verwenden:

COLLATE-Klausel für Tabellenspaltendefinitionen

Das Hinzufügen der optionalen COLLATE-Klausel zur Definition einer Tabellenspalte gibt an, dass die angegebene Sortierung für Vergleiche und andere verwandte Vorgänge verwendet wird, die für die Daten in der Spalte ausgeführt werden:

CREATE TABLE <table_name> ( <col_name> <col_type> COLLATE '<collation_specification>'
                            [ , <col_name> <col_type> COLLATE '<collation_specification>' ... ]
                            [ , ... ]
                          )
Copy

Wenn für eine Spalte keine COLLATE-Klausel angegeben ist, verwendet Snowflake die Standardklausel, bei der Zeichenfolgen anhand ihrer UTF-8-Zeichenrepräsentationen verglichen werden.

Außerdem unterstützt Snowflake die Angabe einer leeren Zeichenfolge für die Sortierungsspezifikation (z. B. COLLATE ''), was einer Angabe ohne Sortierung für die Spalte entspricht.

Die Angabe von COLLATE '' hat jedoch für eine Spalte aus Gründen der Priorität nicht die gleiche Wirkung wie die explizite Angabe von COLLATE 'utf8'. Weitere Details dazu finden Sie unter Sortierungsrangfolge bei Operationen mit mehreren Zeichenfolgen.

Verwenden Sie DESCRIBE TABLE, um festzustellen, ob für die Spalten in einer Tabelle eine Sortierung angegeben wurde (oder nutzen Sie die Funktion COLLATION, um gegebenenfalls die Sortierung für eine bestimmte Spalte anzuzeigen).

Funktion COLLATE

Die Funktion COLLATE verwendet die angegebene Sortierung für den eingegebenen Zeichenfolgenausdruck:

COLLATE( <expression> , '<collation_specification>' )
Copy

Diese Funktion kann auch mit der infix-Notation aufgerufen werden:

<expression> COLLATE '<collation_specification>'
Copy

Diese Funktion ist besonders nützlich, um explizit eine bestimmte Sortierung für eine bestimmte Operation anzugeben (z. B. Sortieren). Sie kann jedoch auch verwendet werden, um:

  • Eine Sortierung in der SELECT-Klausel einer Unterabfrage zu erlauben, sodass alle Operationen in der angegebenen Spalte in der äußeren Abfrage die Sortierung nutzen.

  • Erstellen Sie unter Verwendung von CTAS eine Tabelle mit einer angegebenen Sortierung.

In diesem Beispiel wird die englische Sortierung ohne Berücksichtigung der Groß- und Kleinschreibung verwendet:

SELECT * FROM t1 WHERE COLLATE(col1 , 'en-ci') = 'Tango';
Copy

In diesem Beispiel werden die Ergebnisse nach der deutschen Sortierung (Deutsch) sortiert.

SELECT * FROM t1 ORDER BY COLLATE(col1 , 'de');
Copy

In diesem Beispiel wird eine Tabelle mit einer Spalte nach der französischen Sortierung erstellt:

CREATE TABLE t2 AS SELECT COLLATE(col1, 'fr') AS col1 FROM t1;
Copy

In diesem Beispiel wird die Infix-Notation verwendet, um eine Tabelle mit einer Spalte mit französischer Sortierung zu erstellen:

CREATE TABLE t2 AS SELECT col1 COLLATE 'fr' AS col1 FROM t1;
Copy

Funktion COLLATION

Die Funktion COLLATION gibt die Sortierungsspezifikation zurück, die von einem Ausdruck, einschließlich einer Tabellenspalte, verwendet wird:

COLLATION( <expression> )
Copy

Wenn für den Ausdruck keine Sortierung angegeben wurde, gibt die Funktion NULL zurück.

Wenn Sie diese Funktion auf einen Spaltennamen anwenden, verwenden Sie am besten DISTINCT, um zu vermeiden, dass Sie für jede Zeile in der Tabelle eine Zeile der Ausgabe erhalten. Beispiel:

SELECT DISTINCT COLLATION(column1) FROM table1;
Copy

Bemerkung

Diese Funktion gibt nur die Sortierungsspezifikation zurück, nicht die Prioritätsstufe. Weitere Details zur Priorität finden Sie unter Sortierungsrangfolge bei Operationen mit mehreren Zeichenfolgen (unter diesem Thema).

Sortierungsspezifikationen

Wenn Sie eine COLLATE-Klausel (für eine Tabellenspalte) oder die Funktion COLLATE (für einen Ausdruck) nutzen, müssen Sie eine Sortierungsspezifikation angeben, die über die für die Spalte/den Ausdruck verwendete Vergleichslogik bestimmt.

Eine Sortierungsspezifikation besteht aus einer Zeichenfolge mit einem oder mehreren durch einen Bindestrich (-) getrennten Spezifizierern, und zwar in Form von:

'<specifier>[-<specifier> ...]'

Folgende Spezifizierer werden unterstützt (weitere Informationen dazu finden Sie unter Unterstützte Spezifizierer unter diesem Thema):

  • Gebietsschema

  • Unterscheidung nach Groß-/Kleinschreibung

  • Unterscheidung nach Akzent

  • Unterscheidung nach Interpunktion

  • Präferenz für den ersten Buchstaben

  • Konvertierung der Groß-/Kleinschreibung

  • Platzsparend

Bei Spezifizierern wird nicht nach Groß- und Kleinschreibung unterschieden. Sie können in beliebiger Reihenfolge angegeben werden, mit Ausnahme des Gebietsschemas, das bei Verwendung immer an erster Stelle stehen muss.

In den folgenden Abschnitten finden Sie weitere Informationen zu den Sortierungsspezifikationen:

Spezifikationsbeispiele

Einige Beispiele für Sortierungsspezifikationszeichenfolgen umfassen:

  • 'de': Deutsches Gebietsschema.

  • 'de-ci-pi': Deutsches Gebietsschema mit Vergleichen, bei denen Groß-/Kleinschreibung sowie Interpunktion nicht unterschieden werden.

  • 'fr_CA-ai': Gebietsschema für kanadisches Französisch mit Vergleichen ohne Unterscheidung nach Akzent.

  • 'en_US-trim': Gebietsschema für US-Englisch, wobei führende und nachstehende Leerzeichen vor dem Vergleich abgeschnitten werden.

Sie können auch eine leere Zeichenfolge für eine Sortierungsspezifikation festlegen (z. B. COLLATE '' oder COLLATE(col1, '')), die angibt, dass keine Sortierung verwendet werden soll.

Unterstützte Spezifizierer

Gebietsschema:

Gibt die anzuwendenden sprach- und länderspezifischen Regeln an.

Unterstützt gültige Gebietsschema-Zeichenfolgen, die aus einem Sprachcode (erforderlich) und einem Ländercode (optional) in der Form language_country bestehen. Zu Beispielen gehören:

  • en – Englisch.

  • en_US – Amerikanisches Englisch.

  • fr – Französisch

  • fr_CA – Kanadisches Französisch.

Darüber hinaus gibt das Pseudo-Gebietsschema utf8 die Unicode-Reihenfolge an, die der Standard ist. Weitere Details dazu finden Sie unter Unterschiede in der Sortierung bei Verwendung von UTF-8 oder lokaler Sortierung (unter diesem Thema).

Der Gebietsschemaspezifizierer ist optional, muss jedoch, falls verwendet, der erste Spezifizierer in der Zeichenfolge sein.

Eine vollständige Liste der von Snowflake unterstützten Gebietsschemata finden Sie unter Sortierung n Snowflake unterstützte Sortierungs-Gebietsschemas.

Unterscheidung nach Groß-/Kleinschreibung:

Legt fest, ob die Groß-/Kleinschreibung beim Vergleich von Werten berücksichtigt wird. Mögliche Werte:

  • cs – Zwischen Groß- und Kleinschreibung wird unterschieden (Standard).

  • ci – Zwischen Groß- und Kleinschreibung wird nicht unterschieden.

Beispiel:

Sortierungsspezifikation 

Wert

Ergebnis

'en-ci'

Abc = abc

True

'en-cs' / en

Abc = abc

False

Unterscheidung nach Akzent:

Legt fest, ob Zeichen mit Akzenten als gleichwertig oder verschieden von ihren Basiszeichen betrachtet werden. Mögliche Werte:

  • as – Es wird nach Akzent unterschieden (Standardeinstellung).

  • ai – Es wird nicht nach Akzent unterschieden.

Beispiel:

Sortierungsspezifikation 

Wert

Ergebnis

Anmerkungen

'fr-ai'

E = É

True

'fr-as' / 'fr'

E = É

False

'en-ai'

a = ą

True

Im Englischen werden diese Buchstaben so behandelt, als ob sie nur Akzentunterschiede aufweisen würden. Wenn Sie also keine Unterscheidung nach Akzent angeben, werden die Werte bei Vergleichen als gleich betrachtet.

'pl-ai'

a = ą

False

In der polnischen Sprache werden diese Buchstaben als separate Grundbuchstaben behandelt, sodass sie unabhängig von der Angabe der Unterscheidung nach Akzent immer als ungleich gelten.

'pl-as' / 'pl'

a = ą

False

Die Regeln für die Unterscheidung nach Akzent und die Sortierung sind von Sprache zu Sprache verschieden sind. In einigen Sprachen wird bei der Sortierung beispielsweise immer nach Akzenten unterschieden; in dem Fall können Sie die Unterscheidung nicht deaktivieren, selbst wenn Sie eine akzentunabhängige Sortierung auswählen.

Unterscheidung nach Interpunktion:

Legt fest, ob Nichtbuchstaben eine Rolle spielen. Mögliche Werte:

  • ps – Interpunktionsempfindlich.

  • pi – Interpunktionsunempfindlich.

Beachten Sie, dass die Standardeinstellung vom Gebietsschema abhängt (d. h., wenn die Unterscheidung nach Interpunktion nicht festgelegt ist, werden Regeln je nach dem Gebietsschema verwendet). In den meisten Fällen entsprechen die Regeln ps.

Beispiel:

Sortierungsspezifikation 

Wert

Ergebnis

Anmerkungen

'en-pi'

A-B-C = ABC

True

'en-ps'

A-B-C = ABC

False

Präferenz für den ersten Buchstaben:

Legt fest, ob bei der Sortierung Groß- oder Kleinbuchstaben an erster Stelle stehen. Mögliche Werte:

  • fl – Kleinbuchstaben zuerst sortiert.

  • fu – Großbuchstaben zuerst sortiert.

Die Standardeinstellung hängt vom Gebietsschema ab (d. h., wenn kein Wert angegeben ist, wird eine Sortierung auf Basis des Gebietsschemas verwendet). In den meisten Fällen entspricht die Sortierung fl.

Außerdem hat dieser Spezifizierer keinen Einfluss auf Gleichheitsvergleiche.

Konvertierung der Groß-/Kleinschreibung:

Führt dazu, dass Zeichenfolgen vor Vergleichen in Klein- oder Großbuchstaben konvertiert werden. In einigen Fällen ist dies schneller als eine vollständige Sortierung anhand des Gebietsschemas. Mögliche Werte:

  • upper – Konvertieren der Zeichenfolge vor Vergleichen in Großbuchstaben.

  • lower – Konvertieren der Zeichenfolge vor Vergleichen in Kleinbuchstaben.

Dieser Spezifizierer hat keinen Standardwert (d. h. wenn kein Wert angegeben ist, findet keine der Konvertierungen statt).

Platzsparend:

Entfernt vor Vergleichen führende/nachstehende Leerzeichen aus Zeichenfolgen. Diese Funktion kann nützlich sein, um Vergleiche durchzuführen, die (außer in äußerst seltenen Fällen) der Semantik des Datentyps SQL CHAR entsprechen.

Mögliche Werte:

  • trim – Entfernen sowohl führender als auch nachstehender Leerzeichen vor Vergleichen.

  • ltrim – Entfernen nur führender Leerzeichen vor Vergleichen.

  • rtrim – Entfernen nur nachstehender Leerzeichen vor Vergleichen.

Dieser Spezifizierer hat keinen Standardwert (d. h. wenn kein Wert angegeben ist, wird keine Kürzung vorgenommen).

Beispiel:

Sortierungsspezifikation 

Wert

Ergebnis

Anmerkungen

'en-trim'

__ABC_ = ABC

True

In diesen Beispielen stehen Unterstriche für Leerzeichen.

'en-ltrim'

__ABC_ = ABC

False

'en-rtrim'

__ABC_ = ABC

False

'en'

__ABC_ = ABC

False

Details zur Sortierungsimplementierung

Weitere Details zur Unterstützung der Sortierung finden Sie in den folgenden Abschnitten:

Vergleiche ohne Berücksichtigung der Groß-/Kleinschreibung

In den folgenden Abschnitten werden Vergleiche ohne Berücksichtigung der Groß-/Kleinschreibung beschrieben:

Unterschiede beim Vergleichen von Zeichenfolgen mit Großbuchstaben und ursprünglichen Zeichenfolgen

In einigen Sprachen haben zwei Kleinbuchstaben den gleichen Großbuchstaben. Einige Sprachen unterstützen z. B. sowohl gepunktete als auch nicht gepunktete Formen der Kleinschreibung I (i vs. ı). Das Erzwingen von Großbuchstaben bei Zeichenfolge hat Auswirkungen auf das Vergleichen.

Das folgende Beispiel veranschaulicht den Unterschied:

Erstellen Sie die Tabelle:

CREATE OR REPLACE TABLE test_table (col1 VARCHAR, col2 VARCHAR);
INSERT INTO test_table VALUES ('ı', 'i');
Copy

Führen Sie Abfragen auf den Daten aus:

SELECT col1 = col2,
       COLLATE(col1, 'lower') = COLLATE(col2, 'lower'),
       COLLATE(col1, 'upper') = COLLATE(col2, 'upper')
  FROM test_table;
Copy
+-------------+-------------------------------------------------+-------------------------------------------------+
| COL1 = COL2 | COLLATE(COL1, 'LOWER') = COLLATE(COL2, 'LOWER') | COLLATE(COL1, 'UPPER') = COLLATE(COL2, 'UPPER') |
|-------------+-------------------------------------------------+-------------------------------------------------|
| False       | False                                           | True                                            |
+-------------+-------------------------------------------------+-------------------------------------------------+

Zeichengewichtungen

Snowflake unterstützt die folgenden Sortierspezifikationen.

  • ICU (International Components for Unicode).

  • Snowflake-spezifische Sortierspezifikationen (z. B. upper und lower).

Bei Vergleichsoperationen ohne Berücksichtigung der Groß-/Kleinschreibung, die durch ICU definiert sind, folgt Snowflake dem Unicode Collation Algorithm (UCA) und berücksichtigt nur die primären und sekundären Gewichtungen, aber nicht die tertiären Gewichtungen, von Unicode-Zeichen. Zeichen, die sich nur in ihren Tertiärgewichtungen unterscheiden, werden als identisch behandelt. So werden bei der en-ci-Sortierspezifikation beispielsweise ein Leerzeichen und ein nicht umgebrochenes Leerzeichen als identisch angesehen.

Unterschiede in der Sortierung bei Verwendung von UTF-8 oder lokaler Sortierung

Zeichenfolgen werden in Snowflake immer in UTF-8 gespeichert und können alle Zeichen in allen Sprachen darstellen, die von UTF-8 unterstützt werden. Daher ist die Standardsortierung UTF-8 (d. h. 'utf8').

UTF-8-Sortierung basiert auf der numerischen Darstellung des Zeichens und nicht auf der alphabetischen Rangfolge des Zeichens.

Dies ist analog zur Sortierung nach dem Ordnungswert einzelner ASCII-Zeichen. Dies ist wichtig, da Großbuchstaben Ordnungswerte haben, die kleiner sind als bei Kleinbuchstaben:

A = 65
B = 66
...
a = 97
b = 98
...

Infolgedessen:

  • Wenn Sie in der UTF-8-Reihenfolge sortieren, werden alle Großbuchstaben vor allen Kleinbuchstaben zurückgegeben:

    A, B, …, Y, Z, …, a, b, …, y, z

  • Im Gegensatz dazu wird die Sortierungsspezifikation 'en' alphabetisch sortiert (anstatt die interne UTF-8-Darstellung zu verwenden), sodass sowohl A als auch a vor B und b zurückgegeben werden:

    a, A, b, B, …

Darüber hinaus wirken sich die Unterschiede zwischen den cs- und ci-Spezifizierern zur Unterscheidung nach Groß-/Kleinschreibung auf die Sortierung aus:

  • cs (Unterscheidung nach Groß-/Kleinschreibung) gibt immer die Kleinbuchstabenversion eines Buchstabens vor der Großbuchstabenversion desselben Buchstabens zurück. Beispiel mit 'en-cs':

    a, A, b, B, …

    Die Unterscheidung nach Groß-/Kleinschreibung ist die Standardeinstellung ist, daher sind 'en-cs' und 'en' gleichwertig.

  • ci (ohne Unterscheidung der Groß-/Kleinschreibung) gibt Versionen der Groß-/Kleinschreibung von Buchstaben in zufälliger Relation zueinander zurück, jedoch noch vor der Groß-/Kleinschreibung von späteren Buchstaben. Beispiel mit 'en-ci':

    A, a, b, B, …

Auch einige nicht alphabetische Zeichen können je nach Sortiereinstellung unterschiedlich sortiert werden. Das folgende Beispiel zeigt, dass das Pluszeichen (+) und das Minuszeichen (-) bei verschiedenen Sortiereinstellungen unterschiedlich sortiert werden:

Erstellen Sie die Tabelle:

CREATE OR REPLACE TABLE demo (
    no_explicit_collation VARCHAR,
    en_ci VARCHAR COLLATE 'en-ci',
    en VARCHAR COLLATE 'en',
    utf_8 VARCHAR collate 'utf8');
INSERT INTO demo (no_explicit_collation) VALUES
    ('-'),
    ('+');
UPDATE demo SET
    en_ci = no_explicit_collation,
    en = no_explicit_collation,
    utf_8 = no_explicit_collation;
Copy

Führen Sie Abfragen auf den Daten aus:

SELECT MAX(no_explicit_collation), MAX(en_ci), MAX(en), MAX(utf_8)
  FROM demo;
Copy
+----------------------------+------------+---------+------------+
| MAX(NO_EXPLICIT_COLLATION) | MAX(EN_CI) | MAX(EN) | MAX(UTF_8) |
|----------------------------+------------+---------+------------|
| -                          | +          | +       | -          |
+----------------------------+------------+---------+------------+

Sortierungsrangfolge bei Operationen mit mehreren Zeichenfolgen

Wenn Sie eine Operation für zwei (oder mehr) Zeichenfolgen ausführen, können unterschiedliche Sortierungen für unterschiedliche Zeichenfolgen angegeben werden. Das Festlegen der anzuwendenden Sortierung hängt davon ab, wie die Sortierung für einzelne Eingaben und die Rangfolge der einzelnen Spezifizierer angegeben wurden.

Es gibt drei Prioritätsstufen (von der höchsten zur niedrigsten):

Funktion:

Die Sortierung wird mit der Funktion COLLATE in einer SQL-Anweisung angegeben.

Spalte:

Die Sortierung wurde in der Spaltendefinition angegeben.

Keine:

Für einen bestimmten Ausdruck/eine bestimmte Spalte ist/wurde keine Sortierung angegeben, oder es wird/wurde eine Sortierung mit einer leeren Spezifizierung verwendet (z. B. COLLATE(col1, '') oder col1 STRING COLLATE '').

Bei der Bestimmung der zu verwendenden Sortierung wird die Sortierungsspezifikation mit der höchsten Priorität verwendet. Wenn verschiedene Sortierungen mit der gleichen Prioritätsstufe angegeben sind, werden ihre Werte verglichen, und wenn sie nicht gleich sind, wird ein Fehler zurückgegeben.

Betrachten Sie beispielsweise eine Tabelle mit den folgenden Sortierungsspezifikationen auf Spaltenebene:

CREATE OR REPLACE TABLE collation_precedence_example(
  col1    VARCHAR,               -- equivalent to COLLATE ''
  col2_fr VARCHAR COLLATE 'fr',  -- French locale
  col3_de VARCHAR COLLATE 'de'   -- German locale
);
Copy

Wenn die Tabelle in einer Anweisung verwendet wird, die zwei Zeichenfolgen miteinander vergleicht, wird die Sortierung wie folgt angewendet:

  • Bei diesem Vergleich wird die Sortierung 'fr' verwendet, da die Rangfolge für col2_fr höher ist als die für col1:

    ... WHERE col1 = col2_fr ...
    
    Copy
  • Bei diesem Vergleich wird die Sortierung 'en' verwendet, da sie in der Anweisung explizit angegeben ist und deshalb Vorrang vor der Sortierung col2_fr hat:

    ... WHERE col1 COLLATE 'en' = col2_fr ...
    
    Copy
  • Dieser Vergleich gibt einen Fehler zurück, da die Ausdrücke unterschiedliche Sortierungen auf der gleichen Rangstufe haben:

    ... WHERE col2_fr = col3_de ...
    
    Copy
  • Dieser Vergleich verwendet die Sortierung 'de', da die Sortierung für col2_fr entfernt wurde:

    ... WHERE col2_fr COLLATE '' = col3_de ...
    
    Copy
  • Dieser Vergleich gibt einen Fehler zurück, da die Ausdrücke unterschiedliche Sortierungen auf der gleichen Rangstufe haben:

    ... WHERE col2_fr COLLATE 'en' = col3_de COLLATE 'de' ...
    
    Copy

Auch wenn die Standardsortierung von Snowflake 'utf8' ist, unterscheidet sich die Angabe einer leeren Zeichenfolge (oder die Angabe keiner Sortierung) von der expliziten Angabe von 'utf8', da die explizite Sortierung eine höhere Priorität hat als keine Sortierung. Die letzten beiden Anweisungen in den folgenden Codebeispielen machen den Unterschied deutlich:

Betrachten Sie beispielsweise eine Tabelle mit den folgenden Sortierungsspezifikationen auf Spaltenebene:

CREATE OR REPLACE TABLE collation_precedence_example2(
  s1 STRING COLLATE '',
  s2 STRING COLLATE 'utf8',
  s3 STRING COLLATE 'fr'
);
Copy

Wenn die Tabelle in einer Anweisung verwendet wird, die zwei Zeichenfolgen miteinander vergleicht, wird die Sortierung wie folgt angewendet:

  • Dieser Vergleich verwendet 'utf8', da s1 keine Sortierung hat und 'utf8' die Standardeinstellung ist:

    ... WHERE s1 = 'a' ...
    
    Copy
  • Dieser Vergleich verwendet 'utf8', da s1 keine Sortierung hat und s2 die explizite Sortierung 'utf8' hat

    ... WHERE s1 = s2 ...
    
    Copy
  • Dieser Vergleich wird ohne Fehler ausgeführt, da s1 keine Sortierung hat und s3 die explizite Sortierung fr, sodass die explizite Sortierung Vorrang hat:

    ... WHERE s1 = s3 ...
    
    Copy
  • Dieser Vergleich führt zu einem Fehler, da s2 und s3 unterschiedliche Sortierungen auf der gleichen Rangstufe angegeben haben:

    ... WHERE s2 = s3 ...
    
    Copy
    002322 (42846): SQL compilation error: Incompatible collations: 'fr' and 'utf8'
    

Eingeschränkte Unterstützung bei Sortierung in integrierten Funktionen

Sortierung wird nur in einer Teilmenge von Zeichenfolgenfunktionen unterstützt. Funktionen, bei denen eine Implementierung der Sortierung vernünftigerweise erwartet werden kann, die jedoch keine Sortierung unterstützen, geben bei Verwendung der Sortierung einen Fehler zurück. Diese Fehlermeldungen werden nicht nur beim Aufrufen von COLLATE angezeigt, sondern auch beim Aufrufen einer Zeichenfolgenfunktion für eine Spalte, die in der Anweisung CREATETABLE oder ALTERTABLE, mit der diese Spalte erstellt wurde, als sortiert definiert wurde.

Gegenwärtig beeinflusst die Sortierung nur einfache Vergleichsoperationen. Beispiel: POSITION('abc' in COLLATE('ABC', 'en-ci')) findet abc nicht in ABC, obwohl Sortierung ohne Unterscheidung nach Groß-/Kleinschreibung festgelegt ist.

Funktionen, die Sortierung unterstützen

Diese Funktionen unterstützen Sortierung:

Einige dieser Funktionen weisen Einschränkungen bei der Verwendung mit Sortierung auf. Weitere Informationen dazu finden Sie in der Dokumentation der jeweiligen Funktion.

Diese Liste kann im Laufe der Zeit erweitert werden.

Vorsicht

Einige SQL-Operatoren und -Prädikate wie || (Verkettung) und LIKE werden als Funktionen implementiert (und sind als Funktionen verfügbar, z. B. LIKE() und CONCAT()). Wenn ein Prädikat oder Operator als Funktion implementiert ist und die Funktion keine Sortierung unterstützt, unterstützt das Prädikat oder der Operator ebenfalls keine Sortierung.

Siehe auch Sortierungseinschränkungen.

Leistungsauswirkungen bei Verwendung der Sortierung

Eine Nutzung von Sortierung kann sich auf die Leistung verschiedener Datenbankvorgänge auswirken:

  • Vorgänge mit Vergleichen werden möglicherweise langsamer.

    Dies kann Auswirkungen auf einfache WHERE-Klauseln sowie Verknüpfungen, Sortierungen, GROUPBY-Operationen usw. haben.

  • Bei Verwendung mit einigen Funktionen in WHERE-Prädikaten kann das Pruning von Mikropartitionen weniger effizient ausfallen.

  • Eine Verwendung der Sortierung in einem WHERE-Prädikat, das sich von der für die Spalte angegebenen Sortierung unterscheidet, kann zu einer verringerten Pruning-Effizienz führen oder das Pruning vollständig verhindern.

Weitere Hinweise zur Verwendung der Sortierung

  • Beachten Sie, dass die folgenden Sortierungsfunktionen trotz der Ähnlichkeit ihrer Namen unterschiedliche Ergebnisse liefern:

    • COLLATE gibt explizit an, welche Sortierung verwendet werden soll.

    • COLLATION zeigt an, welche Sortierung verwendet wird, wenn keine explizit angegeben ist.

  • In einer Spalte mit einer Sortierungsspezifikation können Zeichen verwendet werden, die nicht zum Gebietsschema der Sortierung gehören. Dies kann sich auf die Sortierung auswirken.

    Wenn beispielsweise eine Spalte mit einer COLLATE 'en'-Klausel erstellt wird, können die Daten in der Spalte das nicht englische Zeichen É enthalten. In diesem Fall wird das Zeichen É in der Nähe von E sortiert.

  • Sie können Sortiervorgänge angeben, die nicht unbedingt von Bedeutung sind.

    Sie können beispielsweise festlegen, dass polnische Daten mit französischen Daten anhand einer deutschen Sortierung verglichen werden:

    SELECT ... WHERE COLLATE(French_column, 'de') = Polish_column;
    
    Copy

    Snowflake empfiehlt jedoch, das Feature nicht auf diese Weise zu verwenden, da möglicherweise unerwartete oder unbeabsichtigte Ergebnisse zurückgegeben werden.

  • Nachdem eine Tabellenspalte definiert wurde, können Sie die Sortierung für diese Spalte nicht mehr ändern. Mit anderen Worten: Nachdem eine Spalte mit einer bestimmten Sortierung unter Verwendung einer CREATE TABLE-Anweisung erstellt wurde, können Sie die Sortierung mit ALTER TABLE nicht mehr ändern.

    Sie können jedoch in einer DML-Anweisung eine andere Sortierung angeben, z. B. eine SELECT-Anweisung, die auf die Spalte verweist.

Unterschiede zwischen ci und upper/lower

Die Sortierungsspezifikationen upper und lower können beim Vergleichen und Sortieren von Zeichenfolgen eine bessere Performance bieten als die Sortierungsspezifikation ci. Allerdings haben upper und lower etwas andere Auswirkungen als ci, wie in den nächsten Abschnitten erläutert wird:

Unterschiede beim Vergleich von Breiten, Abständen und Schriften

Bei Zeichenfolgevergleichen erkennt die ci-Sortierungsspezifikation, dass verschiedene visuelle Darstellungen eines Zeichens sich auf dasselbe Zeichen beziehen können und behandelt sie entsprechend. Um leistungsfähigere Vergleiche zu ermöglichen, erkennen die upper/lower-Sortierungsspezifikationen diese unterschiedlichen visuellen Darstellungen eines Zeichens nicht als dasselbe Zeichen.

Insbesondere ignoriert die ci-Sortierungsspezifikation einige Unterschiede in den folgenden Kategorien, während die upper/lower-Sortierungsspezifikationen diese nicht ignorieren:

Die folgenden Abschnitte enthalten Beispiele, die diese Unterschiede verdeutlichen.

Bemerkung

Das Vergleichsverhalten von Zeichen mit voller Breite und halber Breite kann vom Gebietsschema abhängen.

Beispiel für den Vergleich von Zeichen mit unterschiedlicher Breite

Erstellen Sie eine Tabelle mit dem Namen different_widths, und fügen Sie Zeilen mit Zeichen unterschiedlicher Breite ein:

CREATE OR REPLACE TABLE different_widths(codepoint STRING, description STRING);

INSERT INTO different_widths VALUES
  ('a', 'ASCII a'),
  ('A', 'ASCII A'),
  ('a', 'Full-width a'),
  ('A', 'Full-width A');

SELECT codepoint VISUAL_CHAR,
       'U+'  || TO_CHAR(UNICODE(codepoint), '0XXX') codepoint_representation,
       description
  FROM different_widths;
Copy
+-------------+--------------------------+--------------+
| VISUAL_CHAR | CODEPOINT_REPRESENTATION | DESCRIPTION  |
|-------------+--------------------------+--------------|
| a           | U+0061                   | ASCII a      |
| A           | U+0041                   | ASCII A      |
| a          | U+FF41                   | Full-width a |
| A          | U+FF21                   | Full-width A |
+-------------+--------------------------+--------------+

Die folgende Abfrage zeigt, dass die ci-Sortierungsspezifikation beim Vergleich der Zeichen genau einen diskreten Wert findet. Die upper/lower-Sortierungsspezifikationen finden beim Vergleich der Zeichen zwei diskrete Werte.

SELECT COUNT(*) NumRows,
       COUNT(DISTINCT UNICODE(codepoint)) DistinctCodepoints,
       COUNT(DISTINCT codepoint COLLATE 'en-ci') DistinctCodepoints_EnCi,
       COUNT(DISTINCT codepoint COLLATE 'upper') DistinctCodepoints_Upper,
       COUNT(DISTINCT codepoint COLLATE 'lower') DistinctCodepoints_Lower
  FROM different_widths;
Copy
+---------+--------------------+-------------------------+--------------------------+--------------------------+
| NUMROWS | DISTINCTCODEPOINTS | DISTINCTCODEPOINTS_ENCI | DISTINCTCODEPOINTS_UPPER | DISTINCTCODEPOINTS_LOWER |
|---------+--------------------+-------------------------+--------------------------+--------------------------|
|       4 |                  4 |                       1 |                        2 |                        2 |
+---------+--------------------+-------------------------+--------------------------+--------------------------+

Die ci-Sortierungsspezifikation ignoriert sowohl Unterschiede in der Breite als auch bei der Groß-/Kleinschreibung, d. h. sie findet keine Unterschiede zwischen den Zeichen. Die upper/lower-Sortierungsspezifikationen ignorieren nur Unterschiede in der Groß-/Kleinschreibung, sodass die Zeichen mit halber Breite als andere Zeichen betrachtet werden als die Zeichen mit voller Breite.

Der Kleinbuchstabe a in halber Breite entspricht dem Großbuchstaben A in halber Breite, und der Kleinbuchstabe a in voller Breite entspricht dem Großbuchstaben A in voller Breite. Daher finden die upper/lower-Sortierungsspezifikationen zwei unterschiedliche Werte.

Beispiel für den Vergleich verschiedener Typen von Leerzeichen

Erstellen Sie eine Tabelle namens different_whitespaces, und fügen Sie Zeilen mit verschiedenen Typen von Leerzeichen ein:

CREATE OR REPLACE TABLE different_whitespaces(codepoint STRING, description STRING);

INSERT INTO different_whitespaces VALUES
  (' ', 'ASCII space'),
  ('\u00A0', 'Non-breaking space'),
  (' ', 'Ogham space mark'),
  (' ', 'en space'),
  (' ', 'em space');

SELECT codepoint visual_char,
       'U+'  || TO_CHAR(unicode(codepoint), '0XXX')
       codepoint_representation, description
  FROM different_whitespaces;
Copy
+-------------+--------------------------+--------------------+
| VISUAL_CHAR | CODEPOINT_REPRESENTATION | DESCRIPTION        |
|-------------+--------------------------+--------------------|
|             | U+0020                   | ASCII space        |
|             | U+00A0                   | Non-breaking space |
|             | U+1680                   | Ogham space mark   |
|             | U+2002                   | en space           |
|             | U+2003                   | em space           |
+-------------+--------------------------+--------------------+

Die folgende Abfrage zeigt, dass die ci-Sortierungsspezifikation beim Vergleich der Leerzeichen genau einen diskreten Wert findet, was bedeutet, dass es keine Unterschiede zwischen ihnen gibt. Die upper/lower-Sortierungsspezifikationen finden beim Vergleich der Leerzeichen fünf diskrete Werte, was bedeutet, dass sie alle unterschiedlich sind.

SELECT COUNT(*) NumRows,
       COUNT(DISTINCT UNICODE(codepoint)) NumDistinctCodepoints,
       COUNT(DISTINCT codepoint COLLATE 'en-ci') DistinctCodepoints_EnCi,
       COUNT(DISTINCT codepoint COLLATE 'upper') DistinctCodepoints_Upper,
       COUNT(DISTINCT codepoint COLLATE 'lower') DistinctCodepoints_Lower
  FROM different_whitespaces;
Copy
+---------+-----------------------+-------------------------+--------------------------+--------------------------+
| NUMROWS | NUMDISTINCTCODEPOINTS | DISTINCTCODEPOINTS_ENCI | DISTINCTCODEPOINTS_UPPER | DISTINCTCODEPOINTS_LOWER |
|---------+-----------------------+-------------------------+--------------------------+--------------------------|
|       5 |                     5 |                       1 |                        5 |                        5 |
+---------+-----------------------+-------------------------+--------------------------+--------------------------+

Beispiel für den Vergleich von Zeichen unterschiedlicher Schriften

Erstellen Sie eine Tabelle mit dem Namen different_scripts, und fügen Sie Zeilen mit Zeichen ein, die verschiedene Schriften verwenden:

CREATE OR REPLACE TABLE different_scripts(codepoint STRING, description STRING);

INSERT INTO different_scripts VALUES
  ('1', 'ASCII digit 1'),
  ('¹', 'Superscript 1'),
  ('₁', 'Subscript 1'),
  ('①', 'Circled digit 1'),
  ('੧', 'Gurmukhi digit 1'),
  ('௧', 'Tamil digit 1');

SELECT codepoint VISUAL_CHAR,
       'U+'  || TO_CHAR(UNICODE(codepoint), '0XXX') codepoint_representation,
       description
  FROM different_scripts;
Copy
+-------------+--------------------------+------------------+
| VISUAL_CHAR | CODEPOINT_REPRESENTATION | DESCRIPTION      |
|-------------+--------------------------+------------------|
| 1           | U+0031                   | ASCII digit 1    |
| ¹           | U+00B9                   | Superscript 1    |
| ₁           | U+2081                   | Subscript 1      |
| ①           | U+2460                   | Circled digit 1  |
| ੧           | U+0A67                   | Gurmukhi digit 1 |
| ௧           | U+0BE7                   | Tamil digit 1    |
+-------------+--------------------------+------------------+

Die folgende Abfrage zeigt, dass die ci-Sortierungsspezifikation beim Vergleich der Zeichen genau einen diskreten Wert findet, was bedeutet, dass es keine Unterschiede zwischen ihnen gibt. Die upper/lower-Sortierungsspezifikationen finden beim Vergleich der Zeichen sechs diskrete Werte, was bedeutet, dass sie alle unterschiedlich sind.

SELECT COUNT(*) NumRows,
       COUNT(DISTINCT UNICODE(codepoint)) DistinctCodepoints,
       COUNT(DISTINCT codepoint COLLATE 'en-ci') DistinctCodepoints_EnCi,
       COUNT(DISTINCT codepoint COLLATE 'upper') DistinctCodepoints_Upper,
       COUNT(DISTINCT codepoint COLLATE 'lower') DistinctCodepoints_Lower
  FROM different_scripts;
Copy
+---------+--------------------+-------------------------+--------------------------+--------------------------+
| NUMROWS | DISTINCTCODEPOINTS | DISTINCTCODEPOINTS_ENCI | DISTINCTCODEPOINTS_UPPER | DISTINCTCODEPOINTS_LOWER |
|---------+--------------------+-------------------------+--------------------------+--------------------------|
|       6 |                  6 |                       1 |                        6 |                        6 |
+---------+--------------------+-------------------------+--------------------------+--------------------------+

Unterschiede beim Verwenden von ignorierbaren Codepunkten

Der Unicode-Sortierungsalgorithmus legt fest, dass Sortierungselemente (Codepunkte) ignorierbar sein können, was bedeutet, dass ein Codepunkt beim Zeichenfolgenvergleich und bei der Sortierung nicht berücksichtigt wird.

  • Bei der Sortierungsspezifikation ci werden diese Codepunkte ignoriert. Dies kann die Suche nach ignorierbaren Codepunkten oder deren Ersetzung erschweren.

  • Bei den Sortierungsspezifikationen upper und lower werden diese Codepunkte nicht ignoriert.

Beispiel: Der Codepunkt U+0001 ist ignorierbar. Wenn Sie diesen Codepunkt mit einer leeren Zeichenfolge unter Verwendung der Sortierungsspezifikation en-ci vergleichen, ist das Ergebnis TRUE, da U+0001 ignoriert wird:

SELECT '\u0001' = '' COLLATE 'en-ci';
Copy
+-------------------------------+
| '\U0001' = '' COLLATE 'EN-CI' |
|-------------------------------|
| True                          |
+-------------------------------+

Wenn Sie dagegen die Sortierungsspezifikation upper oder lower verwenden, ist das Ergebnis FALSE, da U+0001 nicht ignoriert wird:

SELECT '\u0001' = '' COLLATE 'upper';
Copy
+-------------------------------+
| '\U0001' = '' COLLATE 'UPPER' |
|-------------------------------|
| False                         |
+-------------------------------+

Ähnlich verhält es sich, wenn Sie die Funktion REPLACE aufrufen, um diesen Codepunkt aus einer Zeichenfolge zu entfernen. Wenn Sie die Sortierungsspezifikation en-ci verwenden, entfernt die Funktion den Codepunkt nicht, da U+0001 ignoriert wird.

Wie im folgenden Beispiel gezeigt, hat die von der Funktion REPLACE zurückgegebene Zeichenfolge dieselbe Länge wie die an die Funktion übergebene Zeichenfolge, da die Funktion das Zeichen U+0001 nicht entfernt.

SELECT
  LEN('abc\u0001') AS original_length,
  LEN(REPLACE('abc\u0001' COLLATE 'en-ci', '\u0001')) AS length_after_replacement;
Copy
+-----------------+--------------------------+
| ORIGINAL_LENGTH | LENGTH_AFTER_REPLACEMENT |
|-----------------+--------------------------|
|               4 |                        4 |
+-----------------+--------------------------+

Wenn Sie hingegen die Sortierungsspezifikation upper oder lower verwenden, entfernt die Funktion den Codepunkt aus der Zeichenfolge und gibt eine kürzere Zeichenfolge zurück.

SELECT
  LEN('abc\u0001') AS original_length,
  LEN(REPLACE('abc\u0001' COLLATE 'upper', '\u0001')) AS length_after_replacement;
Copy
+-----------------+--------------------------+
| ORIGINAL_LENGTH | LENGTH_AFTER_REPLACEMENT |
|-----------------+--------------------------|
|               4 |                        3 |
+-----------------+--------------------------+

Unterschiede bei der Repräsentation von Zeichen durch unterschiedliche Codepunkte

In Unicode können unterschiedliche Sequenzen von Codepunkten das gleiche Zeichen repräsentieren. Beispielsweise kann der griechische Kleinbuchstabe Iota mit Dialytika und Tonos durch das zusammengesetzte Zeichen mit dem Codepunkt U+0390, aber durch die Folge der Codepunkte U+03b9 U+0308 U+0301 für die nicht zusammengesetzten Zeichen dargestellt werden.

Wenn Sie die Sortierungsspezifikation ci verwenden, werden die verschiedenen Folgen von Codepunkten für ein Zeichen als dasselbe Zeichen behandelt. Beispielsweise werden der Codepunkt U+0390 und die Folge von Codepunkten U+03b9 U+0308 U+0301 als gleichwertig behandelt:

SELECT '\u03b9\u0308\u0301' = '\u0390' COLLATE 'en-ci';
Copy
+-------------------------------------------------+
| '\U03B9\U0308\U0301' = '\U0390' COLLATE 'EN-CI' |
|-------------------------------------------------|
| True                                            |
+-------------------------------------------------+

Um die Leistung für die Sortierungsspezifikationen upper und lower zu verbessern, werden die Sequenzen nicht auf die gleiche Weise behandelt. Zwei Sequenzen von Codepunkten gelten nur dann als gleichwertig, wenn sie nach der Umwandlung in Groß- oder Kleinbuchstaben dieselbe binäre Repräsentation ergeben.

Beispielsweise führt die Verwendung der Spezifikation upper mit dem Codepunkt U+0390 und die Sequenz der Codepunkte U+03b9 U+0308 U+0301 zu Zeichen, die als gleichwertig behandelt werden:

SELECT '\u03b9\u0308\u0301' = '\u0390' COLLATE 'upper';
Copy
+-------------------------------------------------+
| '\U03B9\U0308\U0301' = '\U0390' COLLATE 'UPPER' |
|-------------------------------------------------|
| True                                            |
+-------------------------------------------------+

Die Verwendung der Spezifikation lower führt zu Zeichen, die nicht gleich sind:

SELECT '\u03b9\u0308\u0301' = '\u0390' COLLATE 'lower';
Copy
+-------------------------------------------------+
| '\U03B9\U0308\U0301' = '\U0390' COLLATE 'LOWER' |
|-------------------------------------------------|
| False                                           |
+-------------------------------------------------+

Diese Unterschiede sind bei der Verwendung von upper (statt lower) weniger wahrscheinlich, da es nur einen zusammengesetzten Großbuchstaben-Codepunkt (U+0130) gibt, im Vergleich zu über 100 zusammengesetzten Kleinbuchstaben-Codepunkten.

Unterschiede bei Sequenzen von Codepunkten, die ein einzelnes Zeichen repräsentieren

In Fällen, in denen eine Sequenz von Codepunkten ein einzelnes Zeichen repräsentiert, erkennt die Sortierungsspezifikation ci, dass die Sequenz ein einzelnes Zeichen repräsentiert, und stimmt nicht mit einzelnen Codepunkten in der Sequenz überein.

Beispielsweise steht die Sequenz der Codepunkte U+03b9 U+0308 U+0301 für ein einzelnes Zeichen (den griechischen Kleinbuchstaben Iota mit Dialytika und Tonos). U+0308 und U+0301 repräsentieren Akzente, die auf U+03b9 angewendet werden.

Wenn Sie bei der Sortierungsspezifikation ci die Funktion CONTAINS verwenden, um festzustellen, ob die Sequenz U+03b9 U+0308 den Codepunkt U+03b9 oder U+0308 enthält, gibt die Funktion FALSE zurück, da die Sequenz U+03b9 U+0308 als einzelnes Zeichen behandelt wird:

SELECT CONTAINS('\u03b9\u0308', '\u03b9' COLLATE 'en-ci');
Copy
+----------------------------------------------------+
| CONTAINS('\U03B9\U0308', '\U03B9' COLLATE 'EN-CI') |
|----------------------------------------------------|
| False                                              |
+----------------------------------------------------+
SELECT CONTAINS('\u03b9\u0308', '\u0308' COLLATE 'en-ci');
Copy
+----------------------------------------------------+
| CONTAINS('\U03B9\U0308', '\U0308' COLLATE 'EN-CI') |
|----------------------------------------------------|
| False                                              |
+----------------------------------------------------+

Um die Leistung zu verbessern, behandeln die Spezifikationen upper und lower diese Sequenzen nicht als ein einzelnes Zeichen. Im obigen Beispiel gibt die Funktion CONTAINS den Wert TRUE zurück, da diese Spezifikationen die Sequenz von Codepunkten als separate Zeichen behandeln:

SELECT CONTAINS('\u03b9\u0308', '\u03b9' COLLATE 'upper');
Copy
+----------------------------------------------------+
| CONTAINS('\U03B9\U0308', '\U03B9' COLLATE 'UPPER') |
|----------------------------------------------------|
| True                                               |
+----------------------------------------------------+
SELECT CONTAINS('\u03b9\u0308', '\u0308' COLLATE 'upper');
Copy
+----------------------------------------------------+
| CONTAINS('\U03B9\U0308', '\U0308' COLLATE 'UPPER') |
|----------------------------------------------------|
| True                                               |
+----------------------------------------------------+

Unterschiede, wenn Änderungen an der Groß-/Kleinschreibung zu mehreren Codepunkten führen

Bei einigen zusammengesetzten Zeichen wird die Groß-/Kleinbuchstaben-Version des Zeichens durch eine Sequenz von Codepunkten dargestellt. Beispiel: Das Großbuchstabenzeichen für das deutsche Zeichen ß ist zum Beispiel eine Folge von zwei S (SS).

Obwohl ß und SS gleichwertig sind, liefert die Suche nach ß und SS unterschiedliche Ergebnisse, wenn Sie die Sortierungsspezifikation upper verwenden. Die durch die Groß-/Kleinschreibung erzeugten Sequenzen stimmen entweder vollständig oder gar nicht überein.

SELECT CONTAINS('ß' , 's' COLLATE 'upper');
Copy
+--------------------------------------+
| CONTAINS('SS' , 'S' COLLATE 'UPPER') |
|--------------------------------------|
| False                                |
+--------------------------------------+
SELECT CONTAINS('ss', 's' COLLATE 'upper');
Copy
+-------------------------------------+
| CONTAINS('SS', 'S' COLLATE 'UPPER') |
|-------------------------------------|
| True                                |
+-------------------------------------+

Unterschiede in der Sortierreihenfolge

Die Sortierung für die Sortierungsspezifikationen upper und lower funktioniert anders als die Sortierung für die Spezifikation ci:

  • Mit der Spezifikation ci werden Zeichenfolgen nach dem Sortierschlüssel sortiert. Im Allgemeinen kann der Sortierschlüssel die Groß-/Kleinschreibung, den Akzent, das Gebietsschema usw. berücksichtigen.

  • Bei den Spezifikationen upper und lower werden die Zeichenfolgen nach Codepunkten sortiert, um die Performance zu verbessern.

Beispiel: Einige Zeichen im ASCII-Bereich (z. B. + und -) werden anders sortiert:

SELECT '+' < '-' COLLATE 'en-ci';
Copy
+---------------------------+
| '+' < '-' COLLATE 'EN-CI '|
|---------------------------|
| False                     |
+---------------------------+
SELECT '+' < '-' COLLATE 'upper';
Copy
+---------------------------+
| '+' < '-' COLLATE 'UPPER' |
|---------------------------|
| True                      |
+---------------------------+

Ein weiteres Beispiel: Zeichenfolgen mit ignorierten Codepunkten werden in einer anderen Reihenfolge sortiert:

SELECT 'a\u0001b' < 'ab' COLLATE 'en-ci';
Copy
+-----------------------------------+
| 'A\U0001B' < 'AB' COLLATE 'EN-CI' |
|-----------------------------------|
| False                             |
+-----------------------------------+
SELECT 'a\u0001b' < 'ab' COLLATE 'upper';
Copy
+-----------------------------------+
| 'A\U0001B' < 'AB' COLLATE 'UPPER' |
|-----------------------------------|
| True                              |
+-----------------------------------+

Darüber hinaus werden Emojis anders sortiert:

SELECT 'abc' < '❄' COLLATE 'en-ci';
Copy
+-----------------------------+
| 'ABC' < '❄' COLLATE 'EN-CI' |
|-----------------------------|
| False                       |
+-----------------------------+
SELECT 'abc' < '❄' COLLATE 'upper';
Copy
+-----------------------------+
| 'ABC' < '❄' COLLATE 'UPPER' |
|-----------------------------|
| True                        |
+-----------------------------+

Sortierungseinschränkungen

Es gelten die folgenden Einschränkungen für die Sortierung:

Die Sortierung wird nur für Zeichenfolgen bis zu 8 MB unterstützt.

Obwohl der Snowflake-Datentyp VARCHAR Zeichenfolgen bis zu 16 MB unterstützt, unterstützt Snowflake die Sortierung nur, wenn die resultierende Zeichenfolge eine maximale Größe von 8 MB aufweist. (Einige Sortieroperationen können zu einer Verlängerung der Zeichenfolge führen).

Sortierung (Collation) bei UDFs nicht unterstützt

Snowflake unterstützt keine Sortierung mit UDFs (benutzerdefinierte Funktionen):

  • Sie können von einer UDF keinen sortierten Zeichenfolgenwert zurückgeben. Der Server meldet, dass der tatsächliche Rückgabetyp nicht mit dem deklarierten Rückgabetyp kompatibel ist.

  • Wenn Sie einen sortierten Zeichenfolgenwert an eine UDF übergeben, wird die Sortierungsinformation nicht übergeben. Die UDF behandelt die Zeichenfolge wie eine nicht sortierte Zeichenfolge.

Sortierung für Zeichenfolgen in VARIANT-, ARRAY- oder OBJECT-Werten nicht unterstützt

Zeichenfolgen, die innerhalb eines VARIANT, OBJECT oder ARRAY-Wertes gespeichert sind, enthalten keine Sortierungsspezifikation. Deshalb gilt:

  • SortierungspezifikationBeim Vergleich dieser Werte wird immer die Sortierung 'utf8' verwendet.

  • Wenn ein VARCHAR-Wert mit einer Sortierungsspezifikation verwendet wird, um einen ARRAY-, OBJECT- oder VARIANT-Wert zu erstellen, wird die Sortierungsspezifikation nicht beibehalten.

  • Sie können dennoch einen in einem ARRAY, OBJECT oder VARIANT gespeicherten Wert vergleichen, indem Sie den Wert extrahieren, in VARCHAR umwandeln und eine Sortierungsspezifikation hinzufügen. Beispiel:

    COLLATE(VARIANT_COL:fld1::VARCHAR, 'en-ci') = VARIANT_COL:fld2::VARCHAR
    
    Copy

Beispiele für Sortierung (Collation)

Die folgende Anweisung erstellt eine Tabelle, die für jede Spalte eine andere Sortierung verwendet:

CREATE OR REPLACE TABLE collation_demo (
  uncollated_phrase VARCHAR, 
  utf8_phrase VARCHAR COLLATE 'utf8',
  english_phrase VARCHAR COLLATE 'en',
  spanish_phrase VARCHAR COLLATE 'es');

INSERT INTO collation_demo (
      uncollated_phrase, 
      utf8_phrase, 
      english_phrase, 
      spanish_phrase) 
   VALUES (
     'pinata', 
     'pinata', 
     'pinata', 
     'piñata');
Copy

Bemerkung

Sortierungen haben keinen Einfluss auf die Menge der speicherbaren Zeichen. Snowflake unterstützt alle UTF-8 Zeichen.

Die folgende Abfrage für die Tabelle ergibt die erwarteten Werte:

SELECT * FROM collation_demo;
Copy
+-------------------+-------------+----------------+----------------+
| UNCOLLATED_PHRASE | UTF8_PHRASE | ENGLISH_PHRASE | SPANISH_PHRASE |
|-------------------+-------------+----------------+----------------|
| pinata            | pinata      | pinata         | piñata         |
+-------------------+-------------+----------------+----------------+

Die folgende Abfrage findet keine Übereinstimmung, da das Zeichen ñ nicht mit n übereinstimmt:

SELECT * FROM collation_demo WHERE spanish_phrase = uncollated_phrase;
Copy
+-------------------+-------------+----------------+----------------+
| UNCOLLATED_PHRASE | UTF8_PHRASE | ENGLISH_PHRASE | SPANISH_PHRASE |
|-------------------+-------------+----------------+----------------|
+-------------------+-------------+----------------+----------------+

Beim Ändern der Sortierung werden verwandte, aber ungleiche Zeichen (z. B. ñ und n) nicht als gleich behandelt:

CREATE OR REPLACE TABLE collation_demo1 (
  uncollated_phrase VARCHAR, 
  utf8_phrase VARCHAR COLLATE 'utf8',
  english_phrase VARCHAR COLLATE 'en-ai',
  spanish_phrase VARCHAR COLLATE 'es-ai');

INSERT INTO collation_demo1 (
    uncollated_phrase, 
    utf8_phrase, 
    english_phrase, 
    spanish_phrase) 
  VALUES (
    'piñata', 
    'piñata', 
    'piñata', 
    'piñata');

SELECT uncollated_phrase = 'pinata', 
       utf8_phrase = 'pinata', 
       english_phrase = 'pinata', 
       spanish_phrase = 'pinata'
  FROM collation_demo1;
Copy
+------------------------------+------------------------+---------------------------+---------------------------+
| UNCOLLATED_PHRASE = 'PINATA' | UTF8_PHRASE = 'PINATA' | ENGLISH_PHRASE = 'PINATA' | SPANISH_PHRASE = 'PINATA' |
|------------------------------+------------------------+---------------------------+---------------------------|
| False                        | False                  | True                      | False                     |
+------------------------------+------------------------+---------------------------+---------------------------+

Nur die englische Phrase gibt aus den folgenden Gründen True zurück:

  • Nicht sortierte Vergleiche ignorieren Akzente nicht.

  • utf8-Sortierungsvergleiche ignorieren Akzente nicht.

  • Die Sortierungsvergleiche en-ai und es-ai ignorieren Akzente, aber im Spanischen wird ñ als einzelnes Zeichen und nicht als n mit Akzent behandelt.

Die folgenden Beispiele veranschaulichen die Auswirkung der Sortierung auf die Sortierreihenfolge:

INSERT INTO collation_demo (spanish_phrase) VALUES
  ('piña colada'),
  ('Pinatubo (Mount)'),
  ('pint'),
  ('Pinta');
Copy
SELECT spanish_phrase FROM collation_demo 
  ORDER BY spanish_phrase;
Copy
+------------------+
| SPANISH_PHRASE   |
|------------------|
| Pinatubo (Mount) |
| pint             |
| Pinta            |
| piña colada      |
| piñata           |
+------------------+

Die folgende Abfrage gibt die Werte in einer anderen Reihenfolge zurück, indem sie die Sortierung von 'es' (Spanisch) in 'utf8' ändert:

SELECT spanish_phrase FROM collation_demo 
  ORDER BY COLLATE(spanish_phrase, 'utf8');
Copy
+------------------+
| SPANISH_PHRASE   |
|------------------|
| Pinatubo (Mount) |
| Pinta            |
| pint             |
| piña colada      |
| piñata           |
+------------------+

In diesem Beispiel wird gezeigt, wie Sie mit der Funktion COLLATION die Sortierung für einen Ausdruck anzeigen können, z. B. für eine Spalte:

CREATE OR REPLACE TABLE collation_demo2 (
  c1 VARCHAR COLLATE 'fr', 
  c2 VARCHAR COLLATE '');

INSERT INTO collation_demo2 (c1, c2) VALUES
  ('a', 'a'),
  ('b', 'b');
Copy
SELECT DISTINCT COLLATION(c1), COLLATION(c2) FROM collation_demo2;
Copy
+---------------+---------------+
| COLLATION(C1) | COLLATION(C2) |
|---------------+---------------|
| fr            | NULL          |
+---------------+---------------+

Sie können auch DESCRIBE TABLE verwenden, um Sortierungsinformationen für die Spalten in einer Tabelle anzuzeigen:

DESC TABLE collation_demo2;
Copy
+------+--------------------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+
| name | type                           | kind   | null? | default | primary key | unique key | check | expression | comment | policy name | privacy domain |
|------+--------------------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------|
| C1   | VARCHAR(16777216) COLLATE 'fr' | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| C2   | VARCHAR(16777216)              | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
+------+--------------------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+