照合順序

重要

この機能はパブリック プレビュー段階にあります。

適用対象: Databricks SQL Databricks Runtime 16.1 以降

照合順序は、文字列比較の実行方法を決定する一連の規則です。 照合順序は、大文字と小文字を区別しない、アクセントを区別しない、または末尾にスペースを区別しない方法で文字列を比較したり、特定の言語対応の順序で文字列を並べ替えたりするために使用されます。

Azure Databricks の文字列は、UTF-8 エンコードの Unicode 文字として表されます。 既定では、Azure Databricks はバイナリ UTF8 表現によって文字列を比較します。 これは、UTF8_BINARY 照合順序と呼ばれます。 UTF8_BINARY 比較は、多くの場合は高速で適切ですが、一部のアプリケーション、特に言語認識型の並べ替えや比較を必要とするアプリケーションには適していない場合があります。

言語認識型の比較に加えて、一般的なユースケースとして大文字と小文字の区別を無効にすることがあります。 Azure Databricks には、この目的に特化した UTF8_LCASE 照合順序があります。 文字列を小文字に変換してから、高速 UTF8_BINARY 照合を使用して比較します。

言語認識型の比較のために、Azure Databricks では次のテクノロジが採用されています。

• International Components for Unicode (ICU) ライブラリ: 照合順序の計算用
• 共通ロケール データ リポジトリ (CLDR) テーブル: 特定のロケール対応の照合順序用。
• Unicode ロケール データ マークアップ言語 (LDML) を使用して、照合順序を内部的にエンコードします。

これらのテクノロジは、SQL ステートメントで使用できる一連の名前付き照合順序にカプセル化されています。

照合順序名

LDML 仕様による照合順序の識別は複雑で読み取りが難しい場合があるため、Azure Databricks には一連の使いやすい名前付きシステム照合順序があります。

構文

{ UTF8_BINARY |
  UTF8_LCASE |
  { UNICODE | locale } [ _ modifier [...] ] }

locale
  language_code [ _ script_code ] [ _ country_code ]

modifier
  { CS | CI | AS | AI | RTRIM }

• UTF8_BINARY

  UTF-8 バイト表現に基づいて文字列をバイト単位で比較するメタロケール バイナリ照合順序。 UTF8_BINARY は、Azure Databricks の文字列比較で既定の最も軽量な照合順序です。

  この照合順序では、'A' (x'65') < 'B' (x'66') < … < 'Z' (x'90')。

  ただし、'Z' (x'90') < 'a' (x'97')、および 'A' (x'65') <> 'a' (x'97') です。

  さらに、この照合順序では、'Ä' (x'C384') などの文字が 'Z' および 'z' より大きくなります。

• UTF8_LCASE

  文字列を小文字に変換した後、UTF-8 バイト表現を使用して文字列を比較する、大文字と小文字を区別しない軽量のメタロケール照合順序。

  UTF8_LCASE は、Azure Databricks の識別子に使用される照合順序です。

  次に例を示します。

  ORDER BY col COLLATE UTF8_LCASE
  

  は次と同等

  ORDER BY LOWER(col) COLLATE UTF8_BINARY
  

• UNICODE

  ICU ルート ロケール。

  CLDR で "root" ロケールと呼ばれるこの照合順序 (LDML 仕様: 'und-u') では、言語に依存しない順序が適用され、全体的に直感的になります。 この照合順序では、や といった文字が一緒にグループ化されます。 たとえば、a’ < ‘A’ < ‘Ä’ < ‘b’ です。 'A' は 'a' と同等とは見なされません。 したがって、照合順序では大文字と小文字が区別されます。 'a' は 'ä' と同等とは見なされません。 したがって、照合順序ではアクセントを区別します。

• locale

  CLDR テーブルに基づくロケール対応の照合順序。

  ロケールは、言語コード、オプションのスクリプト コード、およびオプションの国コードとして指定されます。 locale では大文字と小文字が区別されません。

  • language_code: 2 文字の ISO 639-1 言語コード。
  • script_code: 4 文字の ISO 15924 スクリプト コード。
  • country_code: 3 文字の ISO 3166-1 alpha-3 国コード。

• modifier

  大文字と小文字の区別とアクセントの区別に関する照合順序の動作を指定します。

  • CS: 大文字と小文字が区別されます。 既定の動作。
  • CI: 大文字と小文字が区別されません。
  • AS: アクセントを区別します。 既定の動作。
  • AI: アクセントを区別しません。

  適用対象: Databricks Runtime 16.2 以降

  • RTRIM: 末尾のスペースが無視されます。 比較の前に末尾のスペース ('u0020') をトリミングします。

  適用対象: Databricks Runtime 16.2 以降

  RTRIM、CS または CI、および AS または AI を最大 1 回、任意の順序で指定できます。 修飾子自体では大文字と小文字が区別されません。

照合順序を処理する場合、Azure Databricks では既定値を削除することで照合順序名が正規化されます。 たとえば、SR_CYR_SRN_CS_AS は SR に正規化されます。

サポートされている照合順序の一覧については、「サポートされている照合順序」を参照してください。

例

-- You can fully qualify collations, and case doesn't matter.
system.builtin.unicode

-- Since all collations are system defined you don't need to qualify them
unicode

-- Using 2-letter language code only for german collation
DE

-- Using 2-letter language code and 3-letter country code for french-canadian collation
-- It is common to use lower case 2-letter language codes and upper case 3-letter country codes
-- But collation names are case insensitive
fr_CAN

-- Using 2-letter language code and 4-letter script code and 3-letter country code for traditional chinese in Macao
zh_Hant_MAC

-- Using a 2 letter german language code and 2 modifiers for case insensitive and accent insensitive
-- So 'Ä', 'A', and 'a' are all considered equal
de_CI_AI

-- Using back ticks is allowed, but unnecessary for builtin collations
`UTF8_BINARY`

既定の照合順序

既定の照合順序が適用されるのは、文字列を生成する STRING パラメーターを使用せずに STRING リテラル、パラメーター マーカー、関数を使用する場合、および COLLATE 句を使用せずに列、フィールド、または変数の型を定義する場合です。

既定の照合順序は、UTF8_BINARY で派生されます。

照合順序の優先順位

特定の文字列に使用する照合順序を決定するには、Azure Databricks で照合順序の優先順位規則を定義します。

規則では、照合順序に 4 つのレベルの優先順位が割り当てられます。

1. 明示的

   照合順序は、COLLATE 式を使用して文字列に明示的に割り当てられています。

   例

   -- Force fast binary collation to check whether a vin matches a Ferrari
   vin COLLATE UTF8_BINARY LIKE 'ZFF%'
   
   -- Force German collation to order German first names
   ORDER BY vorname COLLATE DE
   

2. 暗黙的

   照合順序は、列、フィールド、列の別名、変数、またはルーチン パラメーターの参照によって暗黙的に割り当てられます。 照合順序が[なし]でない限り、これにはサブクエリの結果が含まれます。

   例

   -- Use the collation of the column as it was defined
   employee.name LIKE 'Mc%'
   
   -- Use the collation of the variable as it was defined.
   translate(session.tempvar, 'Z', ',')
   

3. 既定値

   STRING リテラル、名前付きまたは名前なしのパラメーター マーカー、または別の型から関数によって生成された STRING。

   例

   -- A literal string has the default collation
   'Hello'
   
   -- :parm1 is a parameter marker using session default collation
   EXECUTE IMMEDIATE 'SELECT :parm1' USING 'Hello' AS parm1;
   
   -- ? is a parameter marker using session default collation
   EXECUTE IMMEDIATE 'SELECT ?' USING 'Hello';
   
   -- The result of a cast of a non-STRING to a STRING is a STRING with the default collation
   CAST(5 AS STRING)
   
   -- The date is converted to a string using the default collation
   to_char(DATE'2016-04-08', 'y')
   
   -- The collation of the session_user STRING is the default collation
   session_user()
   

   割り当てられた照合順序は既定の照合順序です。

4. なし

   暗黙的な照合順序が異なる複数の STRING 引数を受け取る関数、演算子、または設定操作 (例: UNION) の STRING の結果。

   例

   -- Concatenating two strings with different explicit collations results in no collation
   SELECT fr || de AS freutsch FROM VALUES('Voulez vous ' COLLATE FR), 'Kartoffelsupp...' COLLATE DE) AS T(fr, de)
   
   -- A union of two strings with different excplicit collations results in no collation
   SELECT 'Voulez vous ' COLLATE FR UNION ALL SELECT 'Kartoffelsupp...' COLLATE DE
   

照合順序の派生

STRING 結果の照合順序を派生する場合、照合順序の優先順位規則は次のように適用されます。

式が次のとおりである場合:

1. 上記の定義と一致する場合

   照合順序と優先順位は定義されているとおりです。

2. 単一の STRING パラメーターを持つ関数または演算子であり、STRING を返す場合

   照合順序と優先順位は、STRING パラメーターのものです。

3. 2 つ以上の STRING パラメーターを持つ関数または演算子である場合

   1. 照合順序と優先順位が同じ場合

      照合順序と優先順位は、STRING パラメーターのものです。

   2. 照合順序または優先順位が異なる場合

      C1 と C2 を個別の照合順序にし、D を既定の照合順序にします。 優先順位と照合順序は、次の表によって決まります。

      照合順序と優先順位	C1 明示的	C1 暗黙的	D 既定値	なし
      C2 明示的	エラー	C2 明示的	C2 明示的	C2 明示的
      C2 暗黙的	明示的 C1	なし	C2 暗黙的	なし
      D 既定値	C1 明示的	C1 暗黙的	D 既定値	なし
      なし	C1 明示的	なし	なし	なし

例

> SELECT 'hello' = 'hello   ' COLLATE UNICODE_RTRIM;
  true

> CREATE TABLE words(fr STRING COLLATE FR, de STRING COLLATE DE, en STRING COLLATE EN);
> INSERT INTO words VALUES ('Salut', 'Hallo', 'Hello');

-- A literal string has the default collation
> SELECT collation('Ciao');
  UTF8_BINARY

-- A function producing a STRING has the default collation
> SELECT collation(user());
  UTF8_BINARY

-- Function modifying a STRING passes the collation through
> SELECT collation(upper('Ciao'));
  UTF8_BINARY

-- Implicit collation (French) wins over default collation
> SELECT collation(fr || 'Ciao') FROM words;
  FR

-- Explicit collation (French) wins over implicit collation (German)
> SELECT collation('Salut' COLLATE FR || de) FROM words;
  FR

-- Implicit collation German collides with implicit collation French
-- The result is no collation
> SELECT collation(de || fr) FROM words;
  null

-- Explicit collation (French) wins over default collation (Italian)
> SELECT collation('Salut' COLLATE FR || 'Ciao');
  FR

-- Explicit collation (French) collides with explicit collation (German)
> SELECT collation('Salut' COLLATE FR || 'Hallo' COLLATE DE);
  COLLATION_MISMATCH.EXPLICIT

-- The explicit collation wins over no collation
> SELECT collation('Ciao' COLLATE IT || (fr || de)) FROM words;
  IT

-- The implict collation (English) does not win over None
> SELECT collation(en || (fr || de)) FROM words;
  null

-- The explicit collation (English) wins over Implicit collation anywhere in the expression
> SELECT collation((fr || ltrim('H' COLLATE EN, fr)) || fr) FROM words;
  EN

関連項目

• collate 式
• collation 関数
• collations テーブル関数