Prise en charge du classement¶

Comprendre le classement¶

Les chaînes de texte dans Snowflake sont stockées à l’aide du jeu de caractères UTF-8 et, par défaut, les chaînes sont comparées selon les codes Unicode représentant les caractères de la chaîne.

Toutefois, la comparaison de chaînes en fonction de leurs représentations en caractères UTF-8 peut ne pas fournir le comportement attendu/requis. Par exemple :

• Si les caractères spéciaux d’une langue donnée ne sont pas triés conformément aux normes de classement de cette langue, le tri peut générer des résultats inattendus.

• Vous souhaiterez peut-être que les chaînes soient ordonnées par d’autres règles, telles que le fait d’ignorer si les caractères sont en majuscules ou en minuscules.

Le classement vous permet de spécifier explicitement les règles à utiliser pour comparer des chaînes, en fonction des éléments suivants :

• Différents paramètres régionaux (c.-à-d. différents jeux de caractères pour différentes langues).

• Sensibilité à la casse (c’est-à-dire s’il faut utiliser des comparaisons de chaînes sensibles à la casse ou insensibles à la casse sans appeler explicitement les fonctions UPPER ou LOWER pour convertir les chaînes).

• Sensibilité aux accents (par exemple, si Z, Ź et Ż sont considérés comme la même lettre ou des lettres différentes).

• Sensibilité à la ponctuation (c’est-à-dire si les comparaisons utilisent uniquement des lettres ou incluent tous les caractères). Par exemple, si une comparaison est insensible à la ponctuation, A-B-C et ABC sont alors considérés comme équivalents.

• Des options supplémentaires, telles que les préférences de tri en fonction de la première lettre d’une chaîne et le découpage des espaces vides de début et de fin.

Utilisations du classement¶

Le classement peut être utilisé dans une grande variété d’opérations, y compris (mais sans s’y limiter) :

Contrôle du classement¶

Le contrôle des classements est granulaire. Vous pouvez spécifier explicitement le classement à utiliser pour :

• Un compte, en utilisant le paramètre au niveau du compte DEFAULT_DDL_COLLATION.

• Toutes les colonnes de toutes les tables ajoutées à une base de données, à l’aide de la commande ALTER DATABASE.

• Toutes les colonnes de toutes les tables ajoutées à un schéma, à l’aide de la commande ALTER SCHEMA.

• Toutes les colonnes ajoutées à une table, à l’aide de la commande ALTER TABLE.

• Colonnes individuelles dans une table, à l’aide de la commande CREATE TABLE.

• Comparaison spécifique dans une instruction SQL (par exemple, WHERE col1 = col2). Si plusieurs classements sont appliqués à une instruction, Snowflake détermine le classement à utiliser en fonction de la priorité. Pour plus de détails sur la préséance, voir Précédence de classement dans les opérations multi-chaînes.

Clause COLLATE pour les définitions de colonne de table¶

L’ajout de la clause facultative COLLATE à la définition d’une colonne de table indique que le classement spécifié est utilisé pour les comparaisons et autres opérations connexes effectuées sur les données de la colonne :

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

Si aucune clause COLLATE n’est spécifiée pour une colonne, Snowflake utilise la valeur par défaut, qui compare les chaînes en fonction de leurs représentations de caractères UTF-8.

En outre, Snowflake prend en charge la spécification d’une chaîne vide pour la spécification de classement (par exemple, COLLATE ''), ce qui revient à ne spécifier aucun classement pour la colonne.

Cependant, en raison de la préséance, la spécification de COLLATE '' pour une colonne n’a pas le même effet que la spécification explicite de COLLATE 'utf8'. Pour plus de détails, voir Précédence de classement dans les opérations multi-chaînes.

Pour savoir si un classement a été spécifié pour les colonnes d’une table, utilisez DESCRIBE TABLE (ou utilisez la fonction COLLATION pour afficher le classement éventuel d’une colonne spécifique).

Fonction COLLATE¶

La fonction COLLATE utilise le classement spécifié dans l’expression de chaîne d’entrée :

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

Cette fonction peut aussi être appelée en utilisant la notation infix :

<expression> COLLATE '<collation_specification>'

Cette fonction est particulièrement utile pour spécifier explicitement un classement particulier pour une opération particulière (par exemple, le tri), mais elle peut également être utilisée pour :

• Autoriser le classement dans la clause SELECT d’une sous-requête, en faisant en sorte que toutes les opérations effectuées sur la colonne spécifiée dans la requête externe utilisent le classement.

• Créer une table en utilisant CTAS avec un classement spécifié.

Cet exemple évalue à l’aide d’un classement insensible à la casse en anglais :

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

Cet exemple trie les résultats à l’aide d’un classement allemand :

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

Cet exemple crée une table avec une colonne à l’aide du classement français :

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

Cet exemple utilise la notation infixe pour créer une table avec une colonne à l’aide du classement français :

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

Fonction COLLATION¶

Cette fonction COLLATION renvoie la spécification de classement utilisée par une expression, y compris une colonne de table :

COLLATION( <expression> )

Si aucun classement n’a été spécifié pour l’expression, la fonction renvoie NULL.

Généralement, si vous utilisez cette fonction sur un nom de colonne, vous devriez utiliser DISTINCT pour éviter d’obtenir une ligne de sortie pour chaque ligne de la table. Par exemple :

SELECT DISTINCT COLLATION(column1) FROM table1;

Exemples de spécification¶

Voici quelques exemples de chaînes de spécification de classement :

• 'de' : langue allemande (néerlandais).

• 'de-ci-pi' : paramètres régionaux allemands, avec des comparaisons insensibles à la casse et à la ponctuation.

• 'fr_CA-ai' : région canadienne française, avec des comparaisons insensibles à l’accent.

• 'en_US-trim' : paramètres régionaux US anglais, avec des espaces de début et de fin ajustés avant la comparaison.

Vous pouvez également spécifier une chaîne vide pour une spécification de classement (par exemple, COLLATE '' ou COLLATE(col1, '')), ce qui indique de ne pas utiliser de classement.

Spécificateurs pris en charge¶

Paramètre régional:

Spécifie les règles spécifiques à la langue et au pays à appliquer.

Prend en charge les chaînes de paramètres régionaux valides, composées d’un code de langue (obligatoire) et d’un code de pays (facultatif) sous la forme language_country. Quelques exemples de paramètres régionaux :

• en - Anglais

• en_US - Anglais américain

• fr - Français

• fr_CA - Français canadien

De plus, le pseudo-paramètre régional utf8 spécifie le tri Unicode, qui est la valeur par défaut. Pour plus de détails, voir Différences de tri lors de l’utilisation du classement en paramètres régionaux ou UTF-8 (dans ce chapitre).

Le spécificateur de paramètres régionaux est facultatif, mais, s’il est utilisé, il doit être le premier spécificateur dans la chaîne.

Pour la liste complète des paramètres régionaux pris en charge par Snowflake, voir Paramètres régionaux de classement pris en charge par Snowflake.

Sensibilité à la casse:

Détermine si la casse est prise en compte lors de la comparaison des valeurs. Valeurs possibles :

• cs - Sensible à la casse (par défaut)

• ci - Insensible à la casse

Par exemple :

Classement Spécification	Valeur	Résultat
'en-ci'	Abc = abc	Vrai
'en-cs' / en	Abc = abc	Faux

Sensibilité aux accents:

Détermine si les caractères accentués sont considérés comme égaux ou différents de leurs caractères de base. Valeurs possibles :

• as - Sensible aux accents (par défaut)

• ai - Insensible aux accents

Par exemple :

Classement Spécification	Valeur	Résultat	Remarques
'fr-ai'	E = É	Vrai
'fr-as' / 'fr'	E = É	Faux
'en-ai'	a = ą	Vrai	En anglais, ces lettres sont considérées comme n’ayant que des différences d’accent. Par conséquent, si vous indiquez une insensibilité aux accents, les valeurs comparées sont égales.
'pl-ai'	a = ą	Faux	En polonais, ces lettres sont traitées comme des lettres de base distinctes. Elles se comparent donc toujours comme étant inégales, que l’insensibilité ou non soit spécifiée ou non.
'pl-as' / 'pl'	a = ą	Faux

Les règles de sensibilité aux accents et de classement varient d’une langue à l’autre. Par exemple, dans certaines langues, le classement est toujours sensible aux accents et vous ne pouvez pas le désactiver, même en spécifiant un classement sans accent.

Sensibilité à la ponctuation:

Détermine si les caractères non alphabétiques sont importants. Valeurs possibles :

• ps - Sensible à la ponctuation.

• pi - Insensible à la ponctuation.

Notez que la valeur par défaut est spécifique au paramètre régional (autrement dit, si aucune sensibilité à la ponctuation n’est spécifiée, des règles spécifiques au paramètre régional sont utilisées). Dans la plupart des cas, les règles sont équivalentes à ps.

Par exemple :

Classement Spécification	Valeur	Résultat	Remarques
'en-pi'	A-B-C = ABC	Vrai
'en-ps'	A-B-C = ABC	Faux

Préférence de première lettre:

Détermine si, lors du tri, les lettres majuscules ou minuscules sont en premier. Valeurs possibles :

• fl - Les lettres minuscules sont triées en premier.

• fu - Les lettres majuscules sont triées en premier.

La valeur par défaut est spécifique au paramètre régional (autrement dit, si aucune valeur n’est spécifiée, un ordre spécifique au paramètre régional est utilisé). Dans la plupart des cas, l’ordre correspond à fl.

De plus, ce spécificateur n’a aucun impact sur les comparaisons d’égalité.

Conversion de casse:

Il en résulte que les chaînes sont converties en minuscules ou en majuscules avant les comparaisons. Dans certaines situations, cela est plus rapide qu’un classement complet spécifique au paramètre régional. Valeurs possibles :

• upper - Convertir la chaîne en majuscules avant les comparaisons.

• lower - Convertir la chaîne en minuscules avant les comparaisons.

Ce spécificateur n’a pas de valeur par défaut (c’est-à-dire que si aucune valeur n’est spécifiée, aucune des conversions n’a lieu).

Coupe d’espace:

Supprime les espaces de début / fin des chaînes avant les comparaisons. Cette fonctionnalité peut être utile pour effectuer des comparaisons équivalentes en sémantique (sauf dans des cas extrêmement rares) au type de données SQL CHAR.

Valeurs possibles :

• trim - Supprimer les espaces de début et de fin avant les comparaisons.

• ltrim - Supprimer uniquement les espaces avant les comparaisons.

• rtrim - Ne supprimer que les espaces de fin avant les comparaisons.

Ce spécificateur n’a pas de valeur par défaut (c’est-à-dire que si aucune valeur n’est spécifiée, le découpage n’est pas effectué).

Par exemple :

Classement Spécification	Valeur	Résultat	Remarques
'en-trim'	__ABC_ = ABC	Vrai	Pour les besoins de ces exemples, les caractères de soulignement représentent des espaces.
'en-ltrim'	__ABC_ = ABC	Faux
'en-rtrim'	__ABC_ = ABC	Faux
'en'	__ABC_ = ABC	Faux

Comparaisons non sensibles à la casse¶

Les sections suivantes décrivent les comparaisons non sensibles à la casse :

• Différences lors de la comparaison de chaînes en majuscules et de chaînes d’origine

• Poids des caractères

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

SELECT col1 = col2,
       COLLATE(col1, 'lower') = COLLATE(col2, 'lower'),
       COLLATE(col1, 'upper') = COLLATE(col2, 'upper')
  FROM test_table;

+-------------+-------------------------------------------------+-------------------------------------------------+
| COL1 = COL2 | COLLATE(COL1, 'LOWER') = COLLATE(COL2, 'LOWER') | COLLATE(COL1, 'UPPER') = COLLATE(COL2, 'UPPER') |
|-------------+-------------------------------------------------+-------------------------------------------------|
| False       | False                                           | True                                            |
+-------------+-------------------------------------------------+-------------------------------------------------+

Différences de tri lors de l’utilisation du classement en paramètres régionaux ou UTF-8¶

Les chaînes sont toujours stockées en interne dans Snowflake dans UTF-8 et peuvent représenter n’importe quel caractère dans n’importe quelle langue prise en charge par UTF-8. Par conséquent, le classement par défaut est UTF-8 (c’est-à-dire 'utf8').

Le classement UTF-8 est basé sur la représentation numérique du caractère, par opposition à l’ordre alphabétique du caractère.

Ceci est comparable au tri en fonction de la valeur ordinale de chaque caractère ASCII, ce qui est important à noter car les lettres majuscules ont des valeurs ordinales inférieures aux lettres minuscules :

En conséquence :

• Si vous triez dans l’ordre UTF-8, toutes les lettres majuscules sont renvoyées avant toutes les lettres minuscules :

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

• En revanche, la spécification de classement 'en' trie dans l’ordre alphabétique (au lieu d’utiliser la représentation interne UTF-8), ce qui a pour résultat que A et a sont renvoyés avant B et b :

  a , A , b , B , …

De plus, les différences entre les spécificateurs de sensibilité à la casse cs et ci ont une incidence sur le tri :

• cs (sensible à la casse) renvoie toujours la version minuscule d’une lettre avant la version majuscule de la même lettre. Par exemple, en utilisant 'en-cs' :

  a , A , b , B , …

  La sensible à la casse est la valeur par défaut et que, par conséquent, 'en-cs' et 'en' sont équivalents.

• ci (insensible à la casse) renvoie les versions majuscules et minuscules des lettres de façon aléatoire les unes par rapport aux autres, mais toujours avant les versions majuscules et minuscules des lettres ultérieures. Par exemple, en utilisant 'en-ci' :

  A , a , b , B , …

Certains caractères non alphabétiques peuvent également être triés différemment selon le paramètre de classement. L’exemple suivant montre que le caractère plus (+) et le caractère moins (-) sont triés différemment selon les paramètres de classement :

Créer la table :

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;

Interroger les données :

SELECT MAX(no_explicit_collation), MAX(en_ci), MAX(en), MAX(utf_8)
  FROM demo;

+----------------------------+------------+---------+------------+
| MAX(NO_EXPLICIT_COLLATION) | MAX(EN_CI) | MAX(EN) | MAX(UTF_8) |
|----------------------------+------------+---------+------------|
| -                          | +          | +       | -          |
+----------------------------+------------+---------+------------+

Précédence de classement dans les opérations multi-chaînes¶

Lors de l’exécution d’une opération sur deux chaînes (ou plus), des classements différents peuvent être spécifiés pour des chaînes différentes. La détermination du classement à appliquer dépend de la manière dont le classement a été spécifié pour chaque entrée et de la priorité de chaque élément spécifique.

Il y a trois niveaux de priorité (du plus élevé au plus bas) :

Fonction:

Le classement est spécifié à l’aide de la Fonction COLLATE dans une instruction SQL.

Colonne:

Le classement a été spécifié dans la définition de colonne.

Aucun:

Aucun classement n’est/n’a été spécifié pour une expression/colonne donnée, ou un classement avec une spécification vide est/a été utilisé (par exemple COLLATE(col1, '') ou col1 STRING COLLATE '').

Lors de la détermination du classement à utiliser, la spécification du classement avec la priorité la plus élevée est utilisée. Si plusieurs classements sont spécifiés avec le même niveau de priorité, leurs valeurs sont comparées et, si elles ne sont pas égales, une erreur est renvoyée.

Par exemple, considérons une table avec les spécifications de classement au niveau de la colonne suivantes :

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
);

Si la table est utilisée dans une instruction comparant deux chaînes, le classement est appliqué comme suit :

• Cette comparaison utilise le classement 'fr' car la préséance pour col2_fr est plus élevé que la préséance pour col1 :

  ... WHERE col1 = col2_fr ...
  

  Copy

• Cette comparaison utilise le classement 'en', car elle est explicitement spécifiée dans l’instruction, qui a préséance sur le classement pour col2_fr :

  ... WHERE col1 COLLATE 'en' = col2_fr ...
  

  Copy

• Cette comparaison renvoie une erreur car les expressions ont des classements différents au même niveau de préséance :

  ... WHERE col2_fr = col3_de ...
  

  Copy

• Cette comparaison utilise le classement 'de' car le classement de col2_fr a été supprimé :

  ... WHERE col2_fr COLLATE '' = col3_de ...
  

  Copy

• Cette comparaison renvoie une erreur car les expressions ont des classements différents au même niveau de préséance :

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

  Copy

Bien que le classement par défaut de Snowflake soit 'utf8', la spécification d’une chaîne vide (ou l’absence de classement) est différente de la spécification explicite de 'utf8', car le classement explicite a une priorité supérieure à l’absence de classement. Les deux dernières instructions dans les exemples de code ci-dessous montrent cette différence :

Par exemple, considérons une table avec les spécifications de classement au niveau de la colonne suivantes :

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

Si la table est utilisée dans une instruction comparant deux chaînes, le classement est appliqué comme suit :

• Cette comparaison utilise 'utf8' parce que s1 n’a pas de classement et 'utf8' est la valeur par défaut :

  ... WHERE s1 = 'a' ...
  

  Copy

• Cette comparaison utilise 'utf8' parce que s1 n’a pas de classement et s2 a un classement 'utf8' explicite

  ... WHERE s1 = s2 ...
  

  Copy

• Cette comparaison s’exécute sans erreur car s1 n’a pas de classement et s3 a un classement explicite fr, donc le classement explicite a préséance :

  ... WHERE s1 = s3 ...
  

  Copy

• Cette comparaison provoque une erreur car s2 et s3 ont des classements différents spécifiés au même niveau de préséance :

  ... WHERE s2 = s3 ...
  

  Copy

  002322 (42846): SQL compilation error: Incompatible collations: 'fr' and 'utf8'
  

Prise en charge limitée du classement dans les fonctions intégrées¶

Le classement n’est pris en charge que dans un sous-ensemble de fonctions de chaîne. Les fonctions qui pourraient raisonnablement être attendues pour implémenter le classement, mais qui ne prennent pas encore en charge le classement, renvoient une erreur lorsqu’elles sont utilisées avec le classement. Ces messages d’erreur sont affichés non seulement lors de l’appel de la fonction COLLATE, mais également lors de l’appel d’une fonction de chaîne sur une colonne définie comme étant assemblée dans l’instruction CREATE TABLE ou ALTER TABLE ayant créé cette colonne.

Actuellement, le classement n’influence que les opérations de comparaison simples. Par exemple, POSITION('abc' in COLLATE('ABC', 'en-ci')) ne trouve pas abc dans ABC, même si un classement ne respectant pas la casse est spécifié.

Conséquences de l’utilisation du classement sur les performances¶

L’utilisation du classement peut affecter les performances de diverses opérations de base de données :

• Les opérations impliquant des comparaisons peuvent être plus lentes.

  Cela peut avoir un impact sur les clauses WHERE simples, ainsi que sur les jointures, les tris, les opérations GROUP BY, etc.

• Utilisée avec certaines fonctions dans les prédicats WHERE, le nettoyage des micro-partitions pourrait être moins efficace.

• L’utilisation d’un classement dans un prédicat WHERE différent du classement spécifié pour la colonne peut entraîner une diminution de l’efficacité du nettoyage ou une élimination complète du nettoyage.

Considérations supplémentaires sur l’utilisation du classement¶

• N’oubliez pas que, malgré des appellations similaires, les fonctions de classement suivantes donnent des résultats différents :

  • COLLATE spécifie explicitement le classement à utiliser.

  • COLLATION indique quel classement est utilisé si aucun n’est spécifié explicitement.

• Une colonne avec une spécification de classement peut utiliser des caractères ne provenant pas du paramètre régional du classement, ce qui peut avoir une incidence sur le tri.

  Par exemple, si une colonne est créée avec une clause COLLATE 'en', les données de la colonne peuvent contenir le caractère non anglais É. Dans cette situation, le caractère É est trié de façon proche de E.

• Vous pouvez spécifier des opérations de classement qui ne sont pas nécessairement significatives.

  Par exemple, vous pouvez spécifier que les données polonaises sont comparées aux données françaises à l’aide d’un classement allemand :

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

  Copy

  Cependant, Snowflake ne recommande pas d’utiliser cette fonctionnalité de cette manière, car elle pourrait générer des résultats inattendus ou non souhaités.

• Une fois qu’une colonne de table est définie, vous ne pouvez pas modifier le classement de la colonne. En d’autres termes, une fois qu’une colonne a été créée avec un classement particulier à l’aide d’une instruction CREATE TABLE, vous ne pouvez pas utiliser ALTER TABLE pour modifier le classement.

  Toutefois, vous pouvez spécifier un classement différent dans une instruction DML, telle qu’une instruction SELECT, faisant référence à la colonne.

Différences dans les comparaisons de largeurs, d’espaces et de scripts¶

Lors des comparaisons de chaînes, la spécification de classement ci reconnaît que différentes représentations visuelles d’un caractère peuvent tout de même faire référence au même caractère et les traite en conséquence. Pour permettre des comparaisons plus performantes, les spécifications de classement upper et lower ne reconnaissent pas ces différentes représentations visuelles d’un caractère comme étant le même caractère.

Plus précisément, la spécification de classement ci ignore certaines différences dans les catégories suivantes, alors que les spécifications de classement upper et lower ne les ignorent pas :

• Largeurs des caractères

• Types d’espaces

• Scripts de caractères

Les sections suivantes incluent des exemples qui illustrent ces différences.

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

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

SELECT codepoint VISUAL_CHAR,
       'U+'  || TO_CHAR(UNICODE(codepoint), '0XXX') codepoint_representation,
       description
  FROM different_widths;

+-------------+--------------------------+--------------+
| VISUAL_CHAR | CODEPOINT_REPRESENTATION | DESCRIPTION  |
|-------------+--------------------------+--------------|
| a           | U+0061                   | ASCII a      |
| A           | U+0041                   | ASCII A      |
| ａ          | U+FF41                   | Full-width a |
| Ａ          | U+FF21                   | Full-width A |
+-------------+--------------------------+--------------+

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;

+---------+--------------------+-------------------------+--------------------------+--------------------------+
| NUMROWS | DISTINCTCODEPOINTS | DISTINCTCODEPOINTS_ENCI | DISTINCTCODEPOINTS_UPPER | DISTINCTCODEPOINTS_LOWER |
|---------+--------------------+-------------------------+--------------------------+--------------------------|
|       4 |                  4 |                       1 |                        2 |                        2 |
+---------+--------------------+-------------------------+--------------------------+--------------------------+

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;

+-------------+--------------------------+--------------------+
| 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           |
+-------------+--------------------------+--------------------+

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;

+---------+-----------------------+-------------------------+--------------------------+--------------------------+
| NUMROWS | NUMDISTINCTCODEPOINTS | DISTINCTCODEPOINTS_ENCI | DISTINCTCODEPOINTS_UPPER | DISTINCTCODEPOINTS_LOWER |
|---------+-----------------------+-------------------------+--------------------------+--------------------------|
|       5 |                     5 |                       1 |                        5 |                        5 |
+---------+-----------------------+-------------------------+--------------------------+--------------------------+

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;

+-------------+--------------------------+------------------+
| 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    |
+-------------+--------------------------+------------------+

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;

+---------+--------------------+-------------------------+--------------------------+--------------------------+
| NUMROWS | DISTINCTCODEPOINTS | DISTINCTCODEPOINTS_ENCI | DISTINCTCODEPOINTS_UPPER | DISTINCTCODEPOINTS_LOWER |
|---------+--------------------+-------------------------+--------------------------+--------------------------|
|       6 |                  6 |                       1 |                        6 |                        6 |
+---------+--------------------+-------------------------+--------------------------+--------------------------+

Différences de traitement des points de code ignorables¶

L’algorithme de classement Unicode spécifie que les éléments de classement (points de code) peuvent être ignorables, ce qui signifie qu’un point de code n’est pas pris en compte lors de la comparaison et du tri des chaînes.

• Avec la spécification de classement ci, ces points de code sont ignorés. Cela peut rendre difficile la recherche ou le remplacement de points de code ignorables.

• Avec les spécifications de classement upper et lower, ces points de code ne sont pas ignorés.

Par exemple, le point de code U+0001 est ignorable. Si vous comparez ce point de code à une chaîne vide avec la spécification de classement en-ci, le résultat est TRUE car U+0001 est ignoré :

SELECT '\u0001' = '' COLLATE 'en-ci';

+-------------------------------+
| '\U0001' = '' COLLATE 'EN-CI' |
|-------------------------------|
| True                          |
+-------------------------------+

En revanche, si vous utilisez les spécifications de classement upper ou lower, le résultat est FALSE car U+0001 n’est pas ignoré :

SELECT '\u0001' = '' COLLATE 'upper';

+-------------------------------+
| '\U0001' = '' COLLATE 'UPPER' |
|-------------------------------|
| False                         |
+-------------------------------+

De même, supposons que vous appeliez la fonction REPLACE pour supprimer ce point de code d’une chaîne. Si vous utilisez la spécification de classement en-ci, la fonction ne supprime pas le point de code car U+0001 est ignoré.

Comme le montre l’exemple ci-dessous, la chaîne renvoyée par la fonction REPLACE a la même longueur que la chaîne passée dans la fonction, car celle-ci ne supprime pas le caractère U+0001.

SELECT
  LEN('abc\u0001') AS original_length,
  LEN(REPLACE('abc\u0001' COLLATE 'en-ci', '\u0001')) AS length_after_replacement;

+-----------------+--------------------------+
| ORIGINAL_LENGTH | LENGTH_AFTER_REPLACEMENT |
|-----------------+--------------------------|
|               4 |                        4 |
+-----------------+--------------------------+

En revanche, si vous utilisez la spécification de classement upper ou lower, la fonction supprime le point de code de la chaîne et renvoie une chaîne plus courte.

SELECT
  LEN('abc\u0001') AS original_length,
  LEN(REPLACE('abc\u0001' COLLATE 'upper', '\u0001')) AS length_after_replacement;

+-----------------+--------------------------+
| ORIGINAL_LENGTH | LENGTH_AFTER_REPLACEMENT |
|-----------------+--------------------------|
|               4 |                        3 |
+-----------------+--------------------------+

Différences lorsque les caractères sont représentés par des points de code différents¶

Dans Unicode, différentes séquences de points de code peuvent représenter le même caractère. Par exemple, la petite lettre grecque Iota avec Dialytika et Tonos peut être représentée par le caractère précomposé avec le point de code U+0390 ou par la séquence de points de code U+03b9 U+0308 U+0301 pour les caractères décomposés.

Si vous utilisez la spécification de classement ci, les différentes séquences de points de code pour un caractère sont traitées comme le même caractère. Par exemple, le point de code U+0390 et la séquence de points de code U+03b9 U+0308 U+0301 sont considérés comme équivalents :

SELECT '\u03b9\u0308\u0301' = '\u0390' COLLATE 'en-ci';

+-------------------------------------------------+
| '\U03B9\U0308\U0301' = '\U0390' COLLATE 'EN-CI' |
|-------------------------------------------------|
| True                                            |
+-------------------------------------------------+

Afin d’améliorer les performances pour les spécifications de classement upper et lower, les séquences ne sont pas traitées de la même manière. Deux séquences de points de code ne sont considérées comme équivalentes que si elles aboutissent à la même représentation binaire après avoir été converties en majuscules ou en minuscules.

Par exemple, l’utilisation de la spécification upper avec le point de code U+0390 et la séquence de points de code U+03b9 U+0308 U+0301 donne des caractères qui sont traités comme égaux :

SELECT '\u03b9\u0308\u0301' = '\u0390' COLLATE 'upper';

+-------------------------------------------------+
| '\U03B9\U0308\U0301' = '\U0390' COLLATE 'UPPER' |
|-------------------------------------------------|
| True                                            |
+-------------------------------------------------+

L’utilisation de la spécification lower produit des caractères qui ne sont pas égaux :

SELECT '\u03b9\u0308\u0301' = '\u0390' COLLATE 'lower';

+-------------------------------------------------+
| '\U03B9\U0308\U0301' = '\U0390' COLLATE 'LOWER' |
|-------------------------------------------------|
| False                                           |
+-------------------------------------------------+

Ces différences sont moins susceptibles de se produire lors de l’utilisation de upper (plutôt que lower) car il n’y a qu’un seul point de code majuscule composite (U+0130), contre plus de 100 points de code minuscules composites.

Différences des séquences de points de code représentant un seul caractère¶

Dans les cas où une séquence de points de code représente un caractère unique, la spécification de classement ci reconnaît que la séquence représente un caractère unique et ne fait pas correspondre les points de code individuels de la séquence.

Par exemple, la séquence de points de code U+03b9 U+0308 U+0301 représente un seul caractère (la petite lettre grecque Iota avec Dialytika et Tonos). U+0308 et U+0301 représentent les accents appliqués à U+03b9.

Pour la spécification de classement ci, si vous utilisez la fonction CONTAINS pour déterminer si la séquence U+03b9 U+0308 contient U+03b9 ou U+0308, la fonction renvoie FALSE car la séquence U+03b9 U+0308 est traitée comme un seul caractère :

SELECT CONTAINS('\u03b9\u0308', '\u03b9' COLLATE 'en-ci');

+----------------------------------------------------+
| CONTAINS('\U03B9\U0308', '\U03B9' COLLATE 'EN-CI') |
|----------------------------------------------------|
| False                                              |
+----------------------------------------------------+

SELECT CONTAINS('\u03b9\u0308', '\u0308' COLLATE 'en-ci');

+----------------------------------------------------+
| CONTAINS('\U03B9\U0308', '\U0308' COLLATE 'EN-CI') |
|----------------------------------------------------|
| False                                              |
+----------------------------------------------------+

Pour améliorer les performances, les spécifications upper et lower ne traitent pas ces séquences comme un seul caractère. Dans l’exemple ci-dessus, la fonction CONTAINS renvoie TRUE car ces spécifications traitent la séquence de points de code comme des caractères distincts :

SELECT CONTAINS('\u03b9\u0308', '\u03b9' COLLATE 'upper');

+----------------------------------------------------+
| CONTAINS('\U03B9\U0308', '\U03B9' COLLATE 'UPPER') |
|----------------------------------------------------|
| True                                               |
+----------------------------------------------------+

SELECT CONTAINS('\u03b9\u0308', '\u0308' COLLATE 'upper');

+----------------------------------------------------+
| CONTAINS('\U03B9\U0308', '\U0308' COLLATE 'UPPER') |
|----------------------------------------------------|
| True                                               |
+----------------------------------------------------+

Différences lorsque des modifications apportées à la casse produisent plusieurs points de code¶

Pour certains caractères composites, la version majuscule ou minuscule du caractère est représentée par une séquence de points de code. Par exemple, le caractère majuscule du caractère allemand ß est une séquence de deux caractères S (SS).

Même si ß et SS sont équivalents, lorsque vous utilisez la spécification de classement upper, les recherches de ß et SS renvoient des résultats différents. Les séquences produites par la conversion des cas correspondent soit intégralement, soit pas du tout.

SELECT CONTAINS('ß' , 's' COLLATE 'upper');

+--------------------------------------+
| CONTAINS('SS' , 'S' COLLATE 'UPPER') |
|--------------------------------------|
| False                                |
+--------------------------------------+

SELECT CONTAINS('ss', 's' COLLATE 'upper');

+-------------------------------------+
| CONTAINS('SS', 'S' COLLATE 'UPPER') |
|-------------------------------------|
| True                                |
+-------------------------------------+

Différences dans l’ordre de tri¶

Le tri pour les spécifications de classement upper et lower fonctionne différemment du tri pour la spécification ci :

• Avec la spécification ci, les chaînes sont triées par clé de classement. En général, la clé de classement peut tenir compte de la sensibilité à la casse, de la sensibilité à l’accent, de la langue, etc.

• Avec les spécifications upper et lower, les chaînes sont triées par point de code pour améliorer les performances.

Par exemple, certains caractères de la plage ASCII (tels que + et -) sont triés différemment :

SELECT '+' < '-' COLLATE 'en-ci';

+---------------------------+
| '+' < '-' COLLATE 'EN-CI '|
|---------------------------|
| False                     |
+---------------------------+

SELECT '+' < '-' COLLATE 'upper';

+---------------------------+
| '+' < '-' COLLATE 'UPPER' |
|---------------------------|
| True                      |
+---------------------------+

Autre exemple, les chaînes dont les points de code sont ignorés sont triées dans un ordre différent :

SELECT 'a\u0001b' < 'ab' COLLATE 'en-ci';

+-----------------------------------+
| 'A\U0001B' < 'AB' COLLATE 'EN-CI' |
|-----------------------------------|
| False                             |
+-----------------------------------+

SELECT 'a\u0001b' < 'ab' COLLATE 'upper';

+-----------------------------------+
| 'A\U0001B' < 'AB' COLLATE 'UPPER' |
|-----------------------------------|
| True                              |
+-----------------------------------+

En outre, les emojis sont triés différemment :

SELECT 'abc' < '❄' COLLATE 'en-ci';

+-----------------------------+
| 'ABC' < '❄' COLLATE 'EN-CI' |
|-----------------------------|
| False                       |
+-----------------------------+

SELECT 'abc' < '❄' COLLATE 'upper';

+-----------------------------+
| 'ABC' < '❄' COLLATE 'UPPER' |
|-----------------------------|
| True                        |
+-----------------------------+

Le classement n’est pris en charge que pour les chaînes jusqu’à 8 MB¶

Bien que le type de données VARCHAR de Snowflake prenne en charge les chaînes allant jusqu’à 16 MB, Snowflake ne prend en charge le classement que lorsque la chaîne résultante est de 8 MB ou moins. (Certaines opérations de classement peuvent allonger une chaîne).

Classement non pris en charge avec les UDFs¶

Snowflake ne prend pas en charge le classement avec des UDFs (fonctions définies par l’utilisateur) :

• Vous ne pouvez pas renvoyer une valeur de chaîne assemblée à partir d’une UDF ; le serveur indique l’incompatibilité du type de retour réel avec le type de retour déclaré.

• Si vous envoyez une valeur de chaîne assemblée à une UDF, les informations de classement ne sont pas transmises ; l’UDF voit la chaîne comme une chaîne non assemblée.

Classement non pris en charge pour les chaînes dans les valeurs VARIANT, ARRAY ou OBJECT¶

Les chaînes stockées dans une valeur VARIANT, OBJECT ou ARRAY n’incluent pas de spécification de classement. Donc :

• La comparaison de ces valeurs utilise toujours le classement 'utf8'.

• Lorsqu’une valeur VARCHAR avec une spécification de classement est utilisée pour construire une valeur ARRAY, OBJECT ou VARIANT, la spécification de classement n’est pas conservée.

• Vous pouvez toujours comparer une valeur stockée dans un ARRAY, OBJECT ou VARIANT en extrayant la valeur, en la convertissant en VARCHAR et en ajoutant une spécification de classement. Par exemple :

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

  Copy