照合のサポート¶

照合では、文字列を比較するための代替ルールを指定できます。これを使用して、特定の言語または他のユーザー指定のルールに従ってデータを比較し、ソートします。

概要¶

以下のセクションでは、照合順序とは何か、また文字列を比較するときに照合順序をどのように使用するかを説明します。

照合順序の理解
照合の用途
照合制御

照合順序の理解¶

Snowflakeのテキスト文字列は UTF-8文字セットを使用して保存され、デフォルトでは、文字列内の文字を表すUnicodeコードに従って文字列が比較されます。

ただし、UTF-8の文字表現に基づいて文字列を比較すると、望ましい/期待される動作が得られない場合があります。例：

特定の言語の特殊文字がその言語の順序付け基準に従って並べ替えられない場合、並べ替えは予期しない結果を返す可能性があります。
文字が大文字や小文字であることを無視するなど、他の規則に従って文字列を並べる場合があります。

照合を使用すると、以下に基づいて、文字列の比較に使用するルールを明示的に指定できます。

異なるロケール（異なる言語の異なる文字セット）。
大文字と小文字の区別（UPPER または LOWER 関数を明示的に呼び出して文字列を変換せずに、大文字と小文字を区別する、または大文字と小文字を区別しない文字列比較を使用する）。
アクセント記号の区別（Z、 Ź、 Ż などが同じ文字または異なる文字と見なされるかどうか）。
句読点の区別（比較に文字のみを使用するか、すべての文字を含めるか）。たとえば、比較で句読点が区別されない場合、 A-B-C と ABC は同等として扱われます。
文字列の最初の文字に基づいた並べ替えの設定や、先頭および/または末尾の空白スペースのトリミングなどの追加オプション。

照合の用途¶

照合は、次のようなさまざまな操作で使用できます（ただし、これらに限定されません）。

使用法	例	リンク
簡単な比較	`... WHERE column1 = column2 ...`	WHERE
結合	`... ON table1.column1 = table2.column2 ...`	JOIN
並び替え	`... ORDER BY column1 ...`	ORDER BY
Top-Kソート	`... ORDER BY column1 LIMIT N ...`	LIMIT / FETCH
集計	`... GROUP BY ...`	GROUP BY
ウィンドウ句	`... PARTITION BY ... ORDER BY ...`	ウィンドウ関数
スカラー関数	`... LEAST(column1, column2, column3) ...`	スカラー関数
集計関数	`... MIN(column1), MAX(column1) ...`	集計関数
データクラスタリング	`... CLUSTER BY (column1) ...`	クラスタリングキーとクラスタ化されたテーブル

照合制御¶

照合順序は細かく制御されます。使用する照合順序を次のように明示的に指定できます。

アカウントレベルのパラメーター DEFAULT_DDL_COLLATION を使用するアカウント。
ALTER DATABASE コマンドを使用して、データベースに追加されたすべてのテーブルのすべての列。
ALTER SCHEMA コマンドを使用して、スキーマに追加されたすべてのテーブルのすべての列。
ALTER TABLE コマンドを使用して、テーブルに追加されたすべての列。
CREATE TABLE コマンドを使用した、テーブル内の個々の列。
SQL ステートメント内の特定の比較（例： WHERE col1 = col2）。ステートメントに複数の照合順序が適用される場合、Snowflakeは優先順位に基づいて使用する照合順序を決定します。優先順位の詳細については、このトピック内の複数文字列操作での照合順序をご参照ください。

照合 SQL 構造¶

照合順序には以下の SQL 構文を使用できます。

COLLATE 句（テーブル列定義の場合）
COLLATE 関数
COLLATION 関数

COLLATE 句（テーブル列定義の場合）¶

オプションの COLLATE 句をテーブル列の定義に追加すると、指定された照合順序が列内のデータに対して実行される比較およびその他の関連操作に使用されることが示されます。

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

Copy

列に COLLATE 句が指定されていない場合、Snowflakeはデフォルトを使用し、 UTF-8文字表現に基づいて文字列を比較します。

また、Snowflakeは、照合順序仕様で空の文字列（例： COLLATE ''）の指定をサポートしています。これは、列に照合順序なしを指定するのと同等です。

ただし、優先順位により、列に COLLATE '' を指定しても、 COLLATE 'utf8' を明示的に指定した場合と同じ効果はありません。詳細については、このトピック内の複数文字列操作での照合順序をご参照ください。

テーブルの列に照合順序が指定されているかどうかを確認するには、 DESCRIBE TABLE を使用します。または COLLATION 関数を使用して、特定の列の照合順序を表示します（存在する場合）。

COLLATE 関数¶

この関数は、入力文字列式で指定された照合順序を使用します。

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

Copy

中置記法を使用して、この関数を呼び出すこともできます。

<expression> COLLATE '[<collation_specification>]'

Copy

この関数は、特定の操作（並べ替えなど）に対して特定の照合順序を明示的に指定する場合に特に役立ちますが、次の目的にも使用できます。

サブクエリの SELECT 句で照合順序を許可し、外部クエリの指定された列に対するすべての操作で照合順序を使用します。
指定された照合順序で CTAS を使用してテーブルを作成します。

例:

-- Evaluates using "English case-insensitive" collation:
SELECT * FROM t1 WHERE COLLATE(col1 , 'en-ci') = 'Tango';

-- Sorts the results using German (Deutsch) collation.
SELECT * FROM t1 ORDER BY COLLATE(col1 , 'de');

-- Creates a table with a column using French collation.
CREATE TABLE t2 AS SELECT COLLATE(col1, 'fr') AS col1 FROM t1;

-- Creates a table with a column using French collation.
CREATE TABLE t2 AS SELECT col1 COLLATE 'fr' AS col1 FROM t1;

Copy

COLLATION 関数¶

この関数は、表の列を含む式で使用される照合順序仕様を返します。

COLLATION( <expression> )

Copy

式に照合順序が指定されていない場合、関数は NULL を返します。

通常、これを列名で使用する場合は、 DISTINCT を使用して、テーブルの各行に対して出力の1行を取得しないようにする必要があります。例：

SELECT DISTINCT COLLATION(column1) FROM table1;

Copy

注釈

この関数は、優先順位レベルではなく、照合順序仕様のみを返します。優先順位の詳細については、このトピック内の複数文字列操作での照合順序をご参照ください。

照合仕様¶

COLLATE 句（テーブル列の場合）または COLLATE 関数（式の場合）を使用する場合、列/式に使用される比較ロジックを決定する照合順序指定を含める必要があります。

照合順序仕様は、次の形式のハイフン（-）で区切られた1つ以上の指定子の文字列で構成されます。

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

次の指定子がサポートされています（詳細については、このトピック内のサポートされている指定子をご参照ください）。

ロケール。
大文字と小文字の区別。
アクセント記号の区別。
句読点の区別。
最初の文字の設定。
大文字と小文字の変換。
スペーストリミング。

指定子は大文字と小文字を区別せず、使用する場合は常に最初にする必要があるロケールを除いて、任意の順序にすることができます。

次のセクションで、照合順序仕様についての詳細を説明します。

仕様例
サポートされている指定子

仕様例¶

照合順序仕様の文字列の例を次に示します。

'de' ：ドイツ語ロケール。
'de-ci-pi' ：ドイツ語ロケール。大文字と小文字の区別なし、句読点の区別なしの比較。
'fr_CA-ai' ：カナダフランス語ロケール。アクセント記号の区別なしの比較。
'en_US-trim' ： US 英語ロケール。比較の前に先頭のスペースと末尾のスペースがトリミングされる比較。

照合順序仕様に空の文字列を指定することもでき（例： COLLATE '' または COLLATE(col1, '')）、これは照合順序を使用しないことを示します。

サポートされている指定子¶

ロケール:

適用する言語および国の固有ルールを指定します。

language_country の形式の言語コード（必須）と国コード（オプション）で構成される有効なロケール文字列をサポートします。ロケールの例を次に示します。

en - 英語。
en_US - アメリカ英語。
fr - フランス語。
fr_CA - カナダフランス語。

さらに、 utf8 疑似ロケールは、デフォルトではUnicode順序の使用を指定します。詳細については、このトピック内の UTF-8を使用した場合とロケール照合を使用した場合の並び替えをご参照ください。

ロケール指定子はオプションですが、使用する場合は、文字列の最初の指定子でなければなりません。

大文字と小文字の区別:

値を比較するときに大文字と小文字を区別するかどうかを決定します。可能な値：

cs - 大文字と小文字を区別します（デフォルト）。
ci - 大文字小文字を区別しません。

例:

照合仕様	値	結果
`'en-ci'`	`Abc = abc`	True
`'en-cs'` / `en`	`Abc = abc`	False

アクセント記号の区別:

アクセント付き文字を基本文字と同じと見なすか、異なると見なすかを決定します。可能な値：

as - アクセントト記号を区別します（デフォルト）。
ai - アクセント記号を区別しません。

例:

照合仕様	値	結果	注意
`'fr-ai'`	`E = É`	True
`'fr-as'` / `'fr'`	`E = É`	False
`'en-ai'`	`a = ą`	True	英語では、これらの文字はアクセントの違いのみを持つものとして扱われるため、アカウントを区別しないように指定すると、値は等しいと比較されます。
`'pl-ai'`	`a = ą`	False	ポーランド語では、これらの文字は個別のベース文字として扱われるため、アクセントを区別しないように指定されているかどうかに関係なく、常に等しくないものとして比較されます。
`'pl-as'` / `'pl'`	`a = ą`	False

アクセントの区別と照合順序の規則は言語に応じて異なります。例えば、一部の言語では照合順序は常にアクセントを区別するため、アクセントを区別しない照合順序を指定しても照合順序をオフにはできません。

句読点の区別:

文字以外の文字が重要かどうかを決定します。可能な値：

ps - 句読点を区別します。
pi - 句読点を区別しません。

デフォルトはロケール固有になっています（句読点の区別が指定されていない場合、ロケール固有のルールが使用されます）。ほとんどの場合、ルールは ps と同等です。

例:

照合仕様	値	結果	注意
`'en-pi'`	`A-B-C = ABC`	True
`'en-ps'`	`A-B-C = ABC`	False

最初の文字の設定:

並べ替え時に大文字または小文字を最初にするかどうかを決定します。可能な値：

fl - 小文字が最初にソートされます。
fu - 大文字が最初にソートされます。

デフォルトはロケール固有になっています（値が指定されていない場合、ロケール固有の順序が使用されます）。ほとんどの場合、順序は fl と同等です。

また、この指定子は等価比較に影響を与えません。

大文字と小文字の変換:

比較の前に文字列が小文字または大文字に変換されます。状況によっては、これはロケール固有の完全な照合順序よりも高速です。可能な値：

upper - 比較の前に文字列を大文字に変換します。
lower - 比較の前に文字列を小文字に変換します。

この指定子にはデフォルトがありません（値が指定されていない場合、変換は行われません）。

スペーストリミング:

比較の前に文字列の先頭/末尾のスペースを削除します。この機能は、セマンティクスで SQL CHAR データ型と同等の比較（非常にまれな場合を除く）を実行するのに役立ちます。

可能な値：

trim - 比較の前に先頭と末尾の両方のスペースを削除します。
ltrim - 比較の前に先頭のスペースのみを削除します。
rtrim - 比較の前に末尾のスペースのみを削除します。

この指定子にはデフォルトがありません（値が指定されていない場合、トリミングは実行されません）。

例:

照合仕様	値	結果	注意
`'en-trim'`	`__ABC_ = ABC`	True	例を示すために、ここではアンダースコア文字は空白スペースを表しています。
`'en-ltrim'`	`__ABC_ = ABC`	False
`'en-rtrim'`	`__ABC_ = ABC`	False
`'en'`	`__ABC_ = ABC`	False

照合の実装の詳細¶

次のセクションで、照合順序のサポートについての詳細を説明します。

大文字と小文字を区別しない比較
UTF-8を使用した場合とロケール照合を使用した場合の並び替え
複数文字列操作での照合順序
組み込み関数での照合の限定サポート
照合の使用におけるパフォーマンスへの影響
照合順序の使用に関する追加の考慮事項

大文字と小文字を区別しない比較¶

大文字の文字列の比較と元の文字列の比較¶

一部の言語では、2つの小文字と対応する大文字が同一である場合があります。たとえば、一部の言語には、ドット付きとドットなしの小文字両方の I （i と ı）があります。文字列を大文字に強制すると、比較に影響します。

以下に違いを示します。

テーブルを作成します。

create or replace table test_table (col1 varchar, col2 varchar);
insert into test_table values ('ı', 'i');

Copy

データをクエリします。

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

Copy

文字の重み¶

Snowflakeは、次の照合順序仕様をサポートしています。

ICU （Unicode用の国際化コンポーネント）。
Snowflake固有の照合順序仕様（例: upper および lower）。

ICU で定義された大文字と小文字を区別しない比較操作の場合、Snowflakeは Unicode照合順序アルゴリズム（UCA）に従い、Unicode文字の3次の重みではなく、1次および2次の重みのみを考慮します。3次の重みのみが異なる文字は、同一として扱われます。たとえば、 en-ci 照合順序仕様を使用すると、スペースと改行なしスペースは同一であると見なされます。

UTF-8を使用した場合とロケール照合を使用した場合の並び替え¶

文字列は常に内部的にSnowflakeの UTF-8に保存され、 UTF-8でサポートされているすべての言語のすべての文字を表すことができます。したがって、デフォルトの照合順序は UTF-8（'utf8'）です。

UTF-8照合順序は、文字のアルファベット順ではなく、文字の数値表現に基づいています。

これは、各 ASCII 文字を序数値で並べ替えることに似ています。大文字は序数値が小文字よりも小さいため、注意が必要です。

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

その結果：

UTF-8の順序で並べ替えると、すべての大文字がすべての小文字の前に返されます。

A 、 B 、 ... 、 Y 、 Z 、 ... 、 a 、 b 、 ... 、 y 、 z
対照的に、 'en' 照合順序仕様は（ UTF-8内部表現を使用する代わりに）アルファベット順に並べ替えられ、 B と b の両方の前に A と a の両方が返されます。

a 、 A 、 b 、 B 、 ...

さらに、 cs と ci の大文字と小文字の区別指定子の違いは、並べ替えに影響します。

cs （大文字と小文字を区別する）は、常に同じ文字の大文字バージョンの前に小文字バージョンの文字を返します。例えば、 'en-cs' を使用する場合：

a 、 A 、 b 、 B 、 ...

デフォルトでは大文字と小文字が区別されるため、 'en-cs' と 'en' は同等です。
ci （大文字と小文字を区別しない）は、大文字と小文字の文字を相互にランダムに返しますが、それ以降の大文字と小文字のバージョン両方の前になります。たとえば、 'en-ci' を使用する場合、

A 、 a 、 b 、 B 、 ...

一部のアルファベット以外の文字は、照合順序設定に応じて異なる方法でソートできます。次のコード例は、プラス文字（+）とマイナス文字（-）が、異なる照合順序設定に対して異なる方法でソートされていることを示しています。

テーブルを作成します。

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

データをクエリします。

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

Copy

複数文字列操作での照合順序¶

2つ（またはそれ以上）の文字列で操作を実行すると、異なる文字列に対して異なる照合順序が指定される場合があります。適用する照合順序の決定は、各入力に対して照合順序が指定された方法と、各指定子の優先順位によって異なります。

次の3つの優先レベルがあります（最高から最低まで）。

関数:: 照合は、 SQL ステートメントで COLLATE 関数関数を使用して指定されます。
列:: 列の定義で照合が指定されました。
なし:: 特定の式/列に照合順序が指定されていない/使用されていなかったか、空の仕様を持つ照合順序が使用されている/使用されていた（COLLATE(col1, '') または col1 STRING COLLATE '' など）。

使用する照合順序を決定するときに、優先順位が最高の照合順序仕様が使用されます。複数の照合順序が指定され、それらの優先レベルが同じ場合、それらの値が比較され、等しくない場合、エラーが返されます。

例えば、次の列レベルの照合順序仕様を持つテーブルについて考えます。

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

2つの文字列を比較するステートメントでテーブルが使用される場合、照合順序は次のように適用されます。

-- Uses the 'fr' collation because the precedence for col2_fr is higher than
-- the precendence for col1.
... WHERE col1 = col2_fr ...

-- Uses the 'en' collation, because it is explicitly specified in the statement,
-- which takes precedence over the collation for col2_fr.
... WHERE col1 COLLATE 'en' = col2_fr ...

-- Returns an error because the expressions have different collations at the same
-- precedence level.
... WHERE col2_fr = col3_de ...

-- Uses the 'de' collation because collation for col2_fr has been removed.
... WHERE col2_fr COLLATE '' = col3_de ...

-- Returns an error because the expressions have different collations at the same
-- precedence level.
... WHERE col2_fr COLLATE 'en' = col3_de COLLATE 'de' ...

Copy

注釈

Snowflakeのデフォルトの照合順序は 'utf8' ですが、空の文字列を指定する（または照合順序なしを指定する）ことは、明示的な照合順序が照合順序なしよりも優先されるため、明示的に 'utf8' を指定することとは異なります。以下のコード例の最後にあるステートメント2つに違いが示されています。

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

-- Uses 'utf8' because s1 has no collation and 'utf8' is the default.
SELECT * FROM collation_precedence_example2 WHERE s1 = 'a';

-- Uses 'utf8' because s1 has no collation and s2 has explicit 'utf8' collation.
SELECT * FROM collation_precedence_example2 WHERE s1 = s2;

Copy

s1には照合順序がなく、s3には明示的な fr 照合順序があるため、この例はエラーなしで実行され、明示的な照合順序が優先されます。

SELECT * FROM collation_precedence_example2 WHERE s1 = s3;
+----+----+----+
| S1 | S2 | S3 |
|----+----+----|
+----+----+----+

Copy

この例では、s2とs3に同じ優先レベルで異なる照合順序が指定されているため、エラーが発生します。

SELECT * FROM collation_precedence_example2 WHERE s2 = s3;

Copy

出力：

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

Copy

組み込み関数での照合の限定サポート¶

照合順序は、文字列関数のサブセットでのみサポートされています。照合順序を実装することが合理的に期待できるが、照合順序をまだサポートしていない関数は、照合順序で使用するとエラーを返します。これらのエラーメッセージは、 COLLATE 関数を呼び出すときだけでなく、その列を作成した CREATE TABLE または ALTER TABLE ステートメントで照合順序されると定義された列で文字列関数を呼び出すときにも表示されます。

現在、照合順序は、単純な比較操作にのみ影響します。

たとえば、大文字と小文字を区別しない照合順序が指定されていても、 POSITION('abc' in COLLATE('ABC', 'en-ci')) は ABC で abc を検出しません。

照合をサポートする関数¶

これらの関数は照合順序をサポートします。

これらの関数の一部には、照合順序での使用に制限があります。詳細については、特定の各機能のドキュメントをご参照ください。

このリストは、経時的に拡大する可能性があります。

注意

|| （連結）や LIKE などの一部の SQL 演算子および述語は、関数として実装されています（また、 LIKE() や CONCAT() などの関数として使用可能）。述語または演算子が関数として実装され、その関数が照合順序をサポートしていない場合、述語または演算子は照合順序をサポートしません。

照合制限もご参照ください。

照合の使用におけるパフォーマンスへの影響¶

照合順序の使用は、さまざまなデータベース操作のパフォーマンスに影響を与える可能性があります。

比較を伴う操作により低速になる場合があります。

これは、単純な WHERE 句、結合、並べ替え、 GROUP BY 操作などに影響を与える可能性があります。
WHERE 述語内の一部の関数で使用すると、マイクロパーティションのプルーニングの効率が低下する場合があります。
列に指定された照合順序とは異なる WHERE 述語で照合順序を使用すると、プルーニングの効率が低下するか、プルーニングが完全になくなる可能性があります。

照合順序の使用に関する追加の考慮事項¶

名前が類似しているにもかかわらず、次の照合順序関数は異なる結果を返します。
- COLLATE は、使用する照合順序を明示的に指定します。
- COLLATION は、明示的に指定されていない場合に使用される照合順序を示します。
照合順序仕様の列では、照合順序のロケールにない文字を使用できます。これは、並べ替えに影響する可能性があります。

例えば、列が COLLATE 'en' 句で作成された場合、列のデータには英語以外の文字 É を含めることができます。この状況では、文字 É は E の近くに並べ替えられます。
意味のない場合がある照合順序操作を指定できます。

たとえば、ドイツ語の照合順序を使用してポーランド語のデータをフランス語のデータと比較するように指定できます。
SELECT ... WHERE COLLATE(French_column, 'de') = Polish_column;
Copy
ただし、予期しない結果または意図しない結果を返す可能性があるため、この機能をこの方法で使用することをSnowflakeは推奨しません。
テーブルの列が定義されると、その列の照合順序は変更できません。つまり、 CREATE TABLE ステートメントを使用して特定の照合順序で列が作成された後、 ALTER TABLE を使用した照合順序の変更はできません。

ただし、列を参照する SELECT ステートメントなどの DML ステートメントで別の照合順序を指定できます。

`ci` と `upper` / `lower` の相違¶

upper と lower 照合順序仕様は、文字列の比較とソート時に ci 照合順序仕様よりも良いパフォーマンスを提供することができます。しかし、 upper と lower には、次のセクションで説明するように、 ci と若干異なる効果があります。

無視できるコードポイントの処理の相違
異なるコードポイントで文字が表現されている場合の相違
単一文字を表すコードポイントのシーケンスとの相違
大文字と小文字の変更が複数のコードポイントになる場合の相違
並べ替え順序の相違

無視できるコードポイントの処理の相違¶

Unicode照合順序アルゴリズムは、照合順序要素（コードポイント）は無視できると指定しており、これは文字列の比較と並べ替えの際にコードポイントが考慮されないことを意味します。

ci 照合順序仕様により、これらのコードポイントは無視されます。このため、無視できるコードポイントの検索や置換が困難になる可能性があります。
upper と lower の照合順序仕様では、これらのコードポイントは無視されません。

たとえば、コードポイント U+0001 は無視できます。このコードポイントを en-ci 照合順序指定で空文字列と比較すると、 U+0001 は無視されるため、結果は TRUE になります。

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

Copy

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

一方、 upper または lower 照合順序仕様を使用する場合、 U+0001 は無視されないため、結果は FALSE になります。

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

Copy

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

同様に、文字列からこのコードポイントを削除するために REPLACE 関数を呼び出すとします。 en-ci 照合順序仕様を使用する場合、 U+0001 は無視されるため、この関数はコードポイントを削除しません。

以下の例に示すように、 REPLACE 関数が返す文字列は、関数が U+0001 文字を削除しないため、関数に渡された文字列と同じ長さです。

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

一方、照合順序に upper または lower 照合順序仕様を使用する場合、この関数は文字列からコードポイントを削除し、より短い文字列を返します。

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

異なるコードポイントで文字が表現されている場合の相違¶

Unicodeでは、異なるコードポイント列が同じ文字を表すことができます。たとえば、DialytikaとTonosのあるギリシャ小文字イオタは、コードポイント U+0390 のある合成済み文字、または分解済み文字のコードポイント U+03b9 U+0308 U+0301 のシーケンスで表すことができます。

ci 照合順序仕様を使用する場合、1つの文字に対する異なるコードポイントシーケンスは同じ文字として扱われます。たとえば、コードポイント U+0390 とコードポイント U+03b9 U+0308 U+0301 のシーケンスは等価として扱われます。

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

Copy

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

upper と lower 照合順序仕様のパフォーマンスを向上させるために、シーケンスは同じようには扱われません。2つのコードポイントのシーケンスは、大文字または小文字に変換された後に同じバイナリ表現になる場合にのみ、等価とみなされます。

たとえば、 upper 仕様をコードポイント U+0390 とコードポイント U+03b9 U+0308 U+0301 のシーケンスと合わせて使用すると、等価として扱われます。

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

Copy

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

lower 仕様を使用すると、文字は不等価になります。

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

Copy

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

このような相違は、 upper （lower ではなく）を使用すると、こうした相違が発生する可能性が小さくなります。これは、小文字の合成コードポイントが100個以上ある場合に比べて、大文字の合成コードポイントは1個（U+0130）しかないためです。

単一文字を表すコードポイントのシーケンスとの相違¶

コードポイントのシーケンスが単一文字を表す場合、 ci 照合順序仕様は、シーケンスが単一文字を表すことを認識し、シーケンス内の個々のコードポイントを一致させません。

たとえば、コードポイント U+03b9 U+0308 U+0301 のシーケンスは単一文字（DialytikaとTonosのあるギリシャ小文字イオタと）を表します。 U+0308 と U+0301 は、 U+03b9 に適用されるアクセントを表します。

ci 照合順序仕様の場合、シーケンス U+03b9 U+0308 が U+03b9 または U+0308 を含むかどうかを判断するために CONTAINS 関数を使用すると、 U+03b9 U+0308 シーケンスは単一文字として扱われるため、関数は FALSE を返します。

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

パフォーマンスを向上させるために、 upper と lower 仕様はこれらのシーケンスを単一文字として扱いません。上記の例では、 CONTAINS 関数は TRUE を返しますが、これはこれらの仕様が一連のコードポイントを別々の文字として扱っているからです。

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

大文字と小文字の変更が複数のコードポイントになる場合の相違¶

いくつかの合成文字では、大文字または小文字のバージョンがコードポイントのシーケンスで表されます。たとえば、ドイツ語の「ß」の大文字は、2つのS文字（SS）のシーケンスです。

ßと SS は等価であるにもかかわらず、 upper 照合順序仕様を使用すると、ßと SS の検索結果は異なります。大文字と小文字の変換によって生成されたシーケンスは、その全体が一致するか、まったく一致しないかのいずれかです。

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

並べ替え順序の相違¶

upper と lower 照合順序仕様の並べ替えは、 ci 仕様の並べ替えとは動作が異なります。

ci 仕様では、文字列は照合順序キーで並べ替えられます。一般的に、照合順序キーは大文字と小文字の区別、アクセントの区別、ロケールなどを考慮することができます。
upper と lower 仕様では、パフォーマンスを向上させるために、文字列はコードポイントで並べ替えられます。

たとえば、 ASCII の範囲にある文字（+ や - など）は並べ替えが異なります。

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

Copy

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

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

Copy

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

別の例として、コードポイントを無視した文字列は異なる順序で並べ替えられます。

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

さらに、絵文字の並べ替えも異なります。

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

Copy

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

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

Copy

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

照合制限¶

照合は、最大 8MB の文字列に対してのみサポートされます¶

Snowflake VARCHAR データ型は最大 16MB の文字列をサポートしますが、Snowflakeは結果の文字列が 8MB 以下の場合にのみ照合順序をサポートします。（一部の照合順序操作では、文字列が長くなる場合があります。）

照合と UDFsの制限¶

Snowflakeは、 UDFs （ユーザー定義関数）との照合順序をサポートしていません。

UDF からは、照合された文字列値を返すことはできません。サーバーは、実際の戻り型が宣言された戻り型と互換性のない場合にエラーを発生します。
照合順序された文字列値を UDF に渡す場合、照合順序情報は渡されません。UDF は、文字列を照合順序されていない文字列と見なします。

VARIANT、ARRAY、または OBJECT の文字列をサポートしていない照合¶

VARIANT、OBJECT、またはARRAY 内に格納されている文字列には、照合順序仕様が含まれていません。したがって、次のようになります。

これらの値の比較では、常に「utf8」照合順序が使用されます。
照合順序仕様を持つ VARCHAR 値を使用して、 ARRAY、 OBJECT、または VARIANT 値を構成する場合、照合順序仕様は保持されません。
ユーザーは値を抽出し、 VARCHAR にキャストし、照合順序仕様を追加することで、 ARRAY、 OBJECT、または VARIANT 内に格納されている値を比較できます。例：
```
COLLATE(VARIANT_COL:fld1::VARCHAR, 'en-ci') = VARIANT_COL:fld2::VARCHAR
```
Copy

例¶

次のステートメントは、列ごとに異なる照合順序を使用するテーブルを作成します。

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

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

Copy

表の次のクエリは、期待される値を示しています。

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

Copy

次のクエリは、文字 ñ が n と一致しないため、一致を検出しません。

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

Copy

照合順序を変更しても、関連する（ただし等しくはない）文字（ ñ と n など）を強制的に等しいものとして処理します。

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

Copy

以下の例は、並べ替え順に対する照合順序の効果を示しています。

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;
+------------------+
| SPANISH_PHRASE   |
|------------------|
| piña colada      |
| piñata           |
| Pinatubo (Mount) |
| pint             |
| Pinta            |
+------------------+

Copy

次のクエリは、照合順序を「sp」（スペイン語）から「utf8」に変更することにより、 ñ と n の順序を逆にします。

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

Copy

この例は、 COLLATION 関数を使用して、列などの式の照合順序を表示する方法を示しています。

CREATE 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;
+---------------+---------------+
| COLLATION(C1) | COLLATION(C2) |
|---------------+---------------|
| fr            | NULL          |
+---------------+---------------+

Copy

DESCRIBE TABLE を使用して、テーブルの列に関する照合順序情報を表示することもできます。

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

Copy

照合のサポート¶

概要¶

照合順序の理解¶

照合の用途¶

照合制御¶

照合 SQL 構造¶

COLLATE 句（テーブル列定義の場合）¶

COLLATE 関数¶

COLLATION 関数¶

照合仕様¶

仕様例¶

サポートされている指定子¶

照合の実装の詳細¶

大文字と小文字を区別しない比較¶

大文字の文字列の比較と元の文字列の比較¶

文字の重み¶

UTF-8を使用した場合とロケール照合を使用した場合の並び替え¶

複数文字列操作での照合順序¶

組み込み関数での照合の限定サポート¶

照合をサポートする関数¶

照合の使用におけるパフォーマンスへの影響¶

照合順序の使用に関する追加の考慮事項¶

ci と upper / lower の相違¶

無視できるコードポイントの処理の相違¶

異なるコードポイントで文字が表現されている場合の相違¶

単一文字を表すコードポイントのシーケンスとの相違¶

大文字と小文字の変更が複数のコードポイントになる場合の相違¶

並べ替え順序の相違¶

照合制限¶

照合は、最大 8MB の文字列に対してのみサポートされます¶

照合と UDFsの制限¶

VARIANT、ARRAY、または OBJECT の文字列をサポートしていない照合¶

例¶

`ci` と `upper` / `lower` の相違¶