カテゴリ：: 文字列とバイナリ関数（全文検索）

SEARCH¶

VARIANT 、 OBJECT 、 ARRAY の列のフィールドを含む、1つ以上のテーブルから指定された列の文字データ（テキスト）を検索します。テキストアナライザーは、テキストをトークン（単語や数値など、テキストの個別の単位）に分割します。指定しない場合は、デフォルトのアナライザーが適用されます。

これらの関数の使用に関する詳細については、全文検索を使用するをご参照ください。

構文¶

SEARCH( <search_data>, <search_string>
  [ , ANALYZER => '<analyzer_name>' ]
  [ , SEARCH_MODE => { 'OR' | 'AND' } ] )

Copy

必要な引数¶

search_data

検索したいデータ。文字列リテラル、列名、または VARIANT 列フィールドへのパスのコンマ区切りリストで表されます。検索データは単一のリテラル文字列でも可能で、これは関数をテストするときに便利な場合があります。

ワイルドカード文字（*）を指定することができます。 * は、関数のスコープ内にあるすべてのテーブルで対象となるすべての列に展開されます。対象となる列は、データ型が VARCHAR （テキスト）、 VARIANT、 ARRAY、 OBJECT の列です。VARIANT、 ARRAY、 OBJECT のデータは検索用にテキストに変換されます。

関数にワイルドカードを渡す場合、ワイルドカードをテーブルの名前またはエイリアスで修飾することができます。例えば、 mytable というテーブルからすべての列を渡すには、以下のように指定します。

(mytable.*)

Copy

ILIKE と EXCLUDE キーワードを使ってフィルタリングすることもできます。

ILIKE は、指定されたパターンに一致する列名でフィルターします。許されるパターンは1つのみです。例:
```
(* ILIKE 'col1%')
```
Copy
EXCLUDE は、指定された列に一致しない列名をフィルターします。例:
```
(* EXCLUDE col1)

(* EXCLUDE (col1, col2))
```
Copy

修飾子は、これらのキーワードを使用する場合に有効です。以下の例では、 ILIKE キーワードを使用して、テーブル mytable 内のパターン col1% に一致するすべての列をフィルターします。

(mytable.* ILIKE 'col1%')

Copy

ILIKE と EXCLUDE キーワードは、1つの関数呼び出し内で組み合わせることはできません。

ILIKE と EXCLUDE キーワードの詳細については、 SELECT の「パラメータ」セクションをご参照ください。

複数のテーブルがスコープ内にある場合、テーブルを結合するか、 UNION セット演算子を使用することで、複数のテーブルから列を検索することができます。結合や UNION クエリの出力に含まれるすべての列を検索するには、以下のように修飾されていない * ワイルドカードを使用することができます。

SELECT *
  FROM t AS T1
    JOIN t AS T2 USING (col1)
  WHERE SEARCH((*), 'string');

Copy

テーブルを結合する際に特定の列を検索するには、列名を修飾する必要がある場合があります（例えば、 table2.colname）。次のように、 * のワイルドカードを使うこともできます。

SELECT *
  FROM t AS T1
    JOIN t AS T2 USING (col1)
  WHERE SEARCH((T2.*), 'string');

Copy

ただし、 * または table.* を関数に複数回指定することはできません。前述の結合例では、 SEARCH((T1.*, T2.*), 'string') を指定することはできません。この構文はエラーを返します。

*、 table.*、または複数の項目が記載されている場合、 search_data 引数には丸括弧が必要です。例:

SEARCH((col1, col2, col3), 'string')
SEARCH((t1.*), 'string')
SEARCH((*), 'string')

Copy

丸括弧で複数の項目を区切らない場合、コンマは関数引数の区切り文字として解析されます。

予期されるエラーケースの例もご参照ください。

列名、コロンまたはドット、およびドットで区切られたサブフィールドを指定することで、 VARIANT データのフィールドを検索できます。例: colname:fieldname.subfieldname。このような列のフィールドの指定についての詳細は、半構造化データの走査をご参照ください。

search_string

1つ以上の検索語を含む VARCHAR 文字列。この引数はリテラル文字列でなければなりません。列名はサポートされていません。文字列全体に一重引用符の1つのペアを指定します。個々の用語や語句を引用符で囲まないでください。使用例：

'blue red green'

次を使用しないでください。

'blue' 'red' 'green'

引数 SEARCH_MODE に設定された値に基づいて、用語のリストは論理和（OR）または論理積（AND）として扱われます。しかし、 NO_OP_ANALYZER を使用すると、クエリ文字列はトークン化されず、論理和（OR）または論理積（AND）のセマンティクスもなく、そのままマッチングされます。

検索では大文字と小文字は区別されない（ただし、 NO_OP_ANALYZER を使用した場合を除く）ため、 'ONCE' という用語を 'Once upon a time' という文字列に対して検索すると、 TRUE が返されます。

検索語句の順序は、検索対象データ内での出現に関しては関係ありません。

オプションの引数¶

ANALYZER => 'analyzer_name'

テキストアナライザー名前。名前を一重引用符で囲む必要があります。

アナライザーは、検索語（および検索対象の列のテキスト）をトークンに分割します。検索文字列から抽出されたトークンと、検索対象の列またはフィールドから抽出されたトークンのマッチングセマンティクス（論理和または論理積）は、 SEARCH_MODE 引数の値に依存します。

アナライザーは、文字列を特定の区切り文字を見つけたところで区切ることによって、文字列をトークン化します。これらの区切り文字は結果のトークンには含まれず、空のトークンは抽出されません。

このパラメーターは、以下の値を1つ以上受け付けます。

DEFAULT_ANALYZER: 以下の区切り文字に基づいてテキストをトークンに分割します。

文字	ユニコード	説明
	`U+0020`	スペース
`[`	`U+005B`	左角括弧
`]`	`U+005D`	右角括弧
`;`	`U+003B`	セミコロン
`<`	`U+003C`	小なり符号
`>`	`U+003E`	大なり記号
`(`	`U+0028`	左括弧
`)`	`U+0029`	右括弧
`{`	`U+007B`	左中括弧
`}`	`U+007D`	右中括弧
`\|`	`U+007C`	垂直バー
`!`	`U+0021`	感嘆符
`,`	`U+002C`	コンマ
`'`	`U+0027`	アポストロフィ
`"`	`U+0022`	引用符
`*`	`U+002A`	アスタリスク
`&`	`U+0026`	アンパサンド
`?`	`U+003F`	疑問符
`+`	`U+002B`	プラス記号
`/`	`U+002F`	スラッシュ
`:`	`U+003A`	コロン
`=`	`U+003D`	等号
`@`	`U+0040`	サイン
`.`	`U+002E`	ピリオド（フルストップ）
`-`	`U+002D`	ハイフン
`$`	`U+0024`	ドル記号
`%`	`U+0025`	パーセント記号
`\`	`U+005C`	バックスラッシュ
`_`	`U+005F`	アンダースコア（低いライン）
`\n`	`U+000A`	改行（ラインフィード）
`\r`	`U+000D`	キャリッジリターン
`\t`	`U+0009`	水平タブ

UNICODE_ANALYZER: 空白と特定の句読点を区切り文字として扱うユニコードの分割規則に基づいてトークン化します。これらの内部ルールは、（さまざまな言語による）自然言語検索のために設計されています。たとえば、デフォルトのアナライザは、 IP アドレスのピリオドや短縮形のアポストロフィを区切り記号として扱いますが、ユニコードアナライザーはそうしません。アナライザーを使用した検索動作の調整をご参照ください。

Unicode Text Segmentation アルゴリズムについての詳細は https://unicode.org/reports/tr29/をご参照ください。
NO_OP_ANALYZER: データもクエリ文字列もトークン化しません。検索語は、大文字小文字の区別を含め、列またはフィールドの全文と完全に一致する必要があります。そうでない場合、 SEARCH 関数は FALSE を返します。クエリ文字列が複数のトークンを含んでいるように見えても（例えば、 'sky blue'）、列またはフィールドはクエリー文字列全体と正確に等しくなければなりません。この場合、 'sky blue' だけが一致します。 'sky' と 'blue' は一致しません。

異なるアナライザーの動作の詳細については、検索語のトークン化方法をご参照ください。

SEARCH_MODE => { 'OR' | 'AND' }

'OR' に設定すると、この関数は論理和セマンティクスを使用します。検索対象の列またはフィールドから抽出されたトークンのいずれかが、検索文字列から抽出されたトークンのいずれかと一致する場合、一致があると見なされます。例えば、 search_string の値が 'blue red green' の場合、関数は検索対象の列またはフィールドのいずれかに blue OR red OR green を含む行に対して TRUE を返します。

'AND' に設定すると、この関数は論理積セマンティクスを使用します。検索される列またはフィールドの少なくとも1つから抽出されたトークンが、検索文字列から抽出されたトークンのすべてと一致する場合、一致があると見なされます。一致するトークンはすべて1つの列またはフィールドになければなりません。複数の列やフィールドにまたがることはできません。例えば、 search_string の値が 'blue red green' の場合、関数は検索対象の列またはフィールドの少なくとも 1 つに blue AND red AND green を含む行に対して TRUE を返します。

デフォルト: 'OR'

戻り値¶

BOOLEAN を返します。

SEARCH_MODE 引数で指定されたセマンティクスに基づき、 search_string トークンが search_data トークンと一致する場合、値は TRUE となります。
これらの引数のいずれかが NULL の場合、 NULL を返します。
それ以外の場合は、 FALSE を返します。

使用上の注意¶

SEARCH 関数は、 VARCHAR、 VARIANT、 ARRAY、 OBJECT データに対してのみ動作します。引数 search_data にこれらのデータ型のデータが含まれていない場合、この関数はエラーを返します。引数 search_data にサポートされているデータ型とサポートされていないデータ型の両方のデータが含まれている場合、関数はサポートされているデータ型のデータを検索し、サポートされていないデータ型のデータを黙って無視します。例については、予期されるエラーケースの例をご参照ください。
ALTER TABLE コマンドを使用して、 SEARCH 関数呼び出しの対象となる列に対して FULL_TEXT 検索最適化を追加することができます。例:
```
ALTER TABLE lines ADD SEARCH OPTIMIZATION
  ON FULL_TEXT(play, character, line);
```
Copy
詳細については、 FULL_TEXT 検索最適化の有効化をご参照ください。

検索語のトークン化方法¶

以下の表は、入力された検索語がどのようにトークンに分割されるかの例をいくつか示したもので、使用するアナライザーが適用するルールによって異なります。表内で、コンマはトークンが分割される場所（分割される場合）を示しています。

検索語	トークン: DEFAULT_ANALYZER	トークン: UNICODE_ANALYZER	NO_OP_ANALYZER （分割不可）
`10.210.158.44`	`10`, `210`, `158`, `44`	`10.210.158.44`	`10.210.158.44`
`192.0.2.0/24`	`192`, `0`, `2`, `24`	`192.0.2.0`、 `24`	`192.0.2.0/24`
`high-tech`	`high`、 `tech`	`high`、 `tech`	`high-tech`
`Bob's Burgers`	`bob`、 `s`、 `burgers`	`bob's`、 `burgers`	`Bob's Burgers`
`Three spaces`	`three`、 `spaces`	`three`、 `spaces`	`Three spaces`
`docs@snowflake.com`	`docs`、 `snowflake`、 `com`	`docs`、 `snowflake.com`	`docs@snowflake.com`
`/opt/homebrew/README.md`	`opt`、 `homebrew`、 `readme`、 `md`	`opt`、 `homebrew`、 `readme.md`	`/opt/homebrew/README.md`

例¶

以下の例は、 SEARCH 関数のさまざまな使い方を示しています。簡単な使い方から始まり、より複雑な使用例へと進んでいきます。

リテラルに対する一致¶

SEARCH 関数の最も単純な例は、文字列リテラルに対する TRUE または FALSE のテストです。最初の例では、比較で大文字と小文字が区別されないことから、第1引数と第2引数のリテラルが一致するため、 TRUE が返されます。

SELECT SEARCH('king','KING');

Copy

+-----------------------------+
| SEARCH('KING','KING')       |
|-----------------------------|
| True                        |
+-----------------------------+

2番目の例では、 32 というトークンが、最初の引数に指定された 5.1.33 というリテラルに含まれていないため、 FALSE が返されます。

SELECT SEARCH('5.1.33','32');

Copy

+-----------------------------+
| SEARCH('5.1.33','32')       |
|-----------------------------|
| False                       |
+-----------------------------+

列参照に対する一致¶

この例では、テーブルの列を1番目の引数として使用します。この関数は、 character 列に検索語のひとつ（king）が存在するため、 TRUE を返します。引数 SEARCH_MODE のデフォルト値は 'OR' であるため、用語リストは論理和となります。（ここで使用されているテーブルと、この後のいくつかの例で使用されているテーブルの詳細については、 SEARCH のサンプルデータをご参照ください。）

SELECT SEARCH(character, 'king queen'), character
  FROM lines
  WHERE line_id=4;

Copy

+---------------------------------+---------------+
| SEARCH(CHARACTER, 'KING QUEEN') | CHARACTER     |
|---------------------------------+---------------|
| True                            | KING HENRY IV |
+---------------------------------+---------------+

次の例は前の例と似ていますが、 SEARCH_MODE 引数が 'AND' に設定されているため、検索セマンティクスは論理積です。 character 列には検索語（king）が1つしか存在しないため、この関数は FALSE を返します。 queen という用語は検索データには表示されません。

SELECT SEARCH(character, 'king queen', SEARCH_MODE => 'AND'), character
  FROM lines
  WHERE line_id=4;

Copy

+-------------------------------------------------------+---------------+
| SEARCH(CHARACTER, 'KING QUEEN', SEARCH_MODE => 'AND') | CHARACTER     |
|-------------------------------------------------------+---------------|
| False                                                 | KING HENRY IV |
+-------------------------------------------------------+---------------+

1つの列に対する WHERE 句検索¶

次のクエリは、 SEARCH 関数を使用して、 line 列に wherefore という単語を含む行を検索します。

SELECT *
  FROM lines
  WHERE SEARCH(line, 'wherefore')
  ORDER BY character LIMIT 5;

Copy

+---------+----------------------+------------+----------------+-----------+-----------------------------------------------------+
| LINE_ID | PLAY                 | SPEECH_NUM | ACT_SCENE_LINE | CHARACTER | LINE                                                |
|---------+----------------------+------------+----------------+-----------+-----------------------------------------------------|
|  100109 | Troilus and Cressida |         31 | 2.1.53         | ACHILLES  | Why, how now, Ajax! wherefore do you thus? How now, |
|   16448 | As You Like It       |          2 | 2.3.6          | ADAM      | And wherefore are you gentle, strong and valiant?   |
|   24055 | The Comedy of Errors |         14 | 5.1.41         | AEMELIA   | Be quiet, people. Wherefore throng you hither?      |
|   99330 | Troilus and Cressida |         30 | 1.1.102        | AENEAS    | How now, Prince Troilus! wherefore not afield?      |
|   92454 | The Tempest          |        150 | 2.1.343        | ALONSO    | Wherefore this ghastly looking?                     |
+---------+----------------------+------------+----------------+-----------+-----------------------------------------------------+

複数列に対する WHERE 句検索¶

次のクエリは、 SEARCH 関数を使用して、 play 列、 character 列、または両方の列に king という単語を含む行を検索します。1番目の引数には丸括弧が必要です。

SELECT play, character
  FROM lines
  WHERE SEARCH((play, character), 'king')
  ORDER BY play, character LIMIT 10;

Copy

+---------------------------+-----------------+
| PLAY                      | CHARACTER       |
|---------------------------+-----------------|
| All's Well That Ends Well | KING            |
| Hamlet                    | KING CLAUDIUS   |
| Hamlet                    | KING CLAUDIUS   |
| Henry IV Part 1           | KING HENRY IV   |
| Henry IV Part 1           | KING HENRY IV   |
| King John                 | CHATILLON       |
| King John                 | KING JOHN       |
| King Lear                 | GLOUCESTER      |
| King Lear                 | KENT            |
| Richard II                | KING RICHARD II |
+---------------------------+-----------------+

テーブル内のすべての列を対象としたワイルドカード検索¶

この例のように、 SEARCH 関数の1番目の引数として * 文字（または table.*）を使用することができます。この検索は、選択するテーブル（この場合は lines テーブル）の対象となるすべての列に対して行われます。

lines テーブルには、検索機能でサポートされているデータ型を持つ4つの列があります。結果は、検索された4列のうち1列以上に king が含まれる行で構成されます。これらの列の1つ、 act_scene_line については、関数により一致が見つかりませんが、他の3列については一致が見つかります。引数 SEARCH_MODE のデフォルトは 'OR' です。

SELECT play, character, line, act_scene_line
  FROM lines
  WHERE SEARCH((lines.*), 'king')
  ORDER BY act_scene_line LIMIT 10;

Copy

+-----------------+-----------------+----------------------------------------------------+----------------+
| PLAY            | CHARACTER       | LINE                                               | ACT_SCENE_LINE |
|-----------------+-----------------+----------------------------------------------------+----------------|
| Pericles        | LODOVICO        | This king unto him took a fere,                    | 1.0.21         |
| Richard II      | KING RICHARD II | Old John of Gaunt, time-honour'd Lancaster,        | 1.1.1          |
| Henry VI Part 3 | WARWICK         | I wonder how the king escaped our hands.           | 1.1.1          |
| King John       | KING JOHN       | Now, say, Chatillon, what would France with us?    | 1.1.1          |
| King Lear       | KENT            | I thought the king had more affected the Duke of   | 1.1.1          |
| Henry IV Part 1 | KING HENRY IV   | So shaken as we are, so wan with care,             | 1.1.1          |
| Henry IV Part 1 | KING HENRY IV   | Which, like the meteors of a troubled heaven,      | 1.1.10         |
| King Lear       | GLOUCESTER      | so often blushed to acknowledge him, that now I am | 1.1.10         |
| Cymbeline       | First Gentleman | Is outward sorrow, though I think the king         | 1.1.10         |
| King John       | CHATILLON       | To this fair island and the territories,           | 1.1.10         |
+-----------------+-----------------+----------------------------------------------------+----------------+

また、 ILIKE と EXCLUDE キーワードを使用してフィルターすることできます。これらのキーワードの詳細については、 SELECT をご参照ください。

この検索では、 ILIKE キーワードを使用して line という文字列で終わる列のみを検索します。つまり、この関数は line と act_scene_line 列を検索します。

SELECT play, character, line, act_scene_line
  FROM lines
  WHERE SEARCH((lines.* ILIKE '%line'), 'king')
  ORDER BY act_scene_line LIMIT 10;

Copy

+-----------------+-----------------+--------------------------------------------------+----------------+
| PLAY            | CHARACTER       | LINE                                             | ACT_SCENE_LINE |
|-----------------+-----------------+--------------------------------------------------+----------------|
| Pericles        | LODOVICO        | This king unto him took a fere,                  | 1.0.21         |
| Henry VI Part 3 | WARWICK         | I wonder how the king escaped our hands.         | 1.1.1          |
| King Lear       | KENT            | I thought the king had more affected the Duke of | 1.1.1          |
| Cymbeline       | First Gentleman | Is outward sorrow, though I think the king       | 1.1.10         |
+-----------------+-----------------+--------------------------------------------------+----------------+

この検索では、 character 列のデータを検索しないように、 EXCLUDE キーワードを使用しています。

SELECT play, character, line, act_scene_line
  FROM lines
  WHERE SEARCH((lines.* EXCLUDE character), 'king')
  ORDER BY act_scene_line LIMIT 10;

Copy

+-----------------+-----------------+----------------------------------------------------+----------------+
| PLAY            | CHARACTER       | LINE                                               | ACT_SCENE_LINE |
|-----------------+-----------------+----------------------------------------------------+----------------|
| Pericles        | LODOVICO        | This king unto him took a fere,                    | 1.0.21         |
| Henry VI Part 3 | WARWICK         | I wonder how the king escaped our hands.           | 1.1.1          |
| King John       | KING JOHN       | Now, say, Chatillon, what would France with us?    | 1.1.1          |
| King Lear       | KENT            | I thought the king had more affected the Duke of   | 1.1.1          |
| Cymbeline       | First Gentleman | Is outward sorrow, though I think the king         | 1.1.10         |
| King Lear       | GLOUCESTER      | so often blushed to acknowledge him, that now I am | 1.1.10         |
| King John       | CHATILLON       | To this fair island and the territories,           | 1.1.10         |
+-----------------+-----------------+----------------------------------------------------+----------------+

SELECT リストでのワイルドカード検索¶

SELECT リスト内で、 * 文字（または table.*）を使用できます。

以下の検索は、選択するテーブル（この場合は lines テーブル）内の対象となる列すすべてに対して実行されます。検索された4列のうち1列以上に king が含まれる場合、検索は TRUE を返します。引数 SEARCH_MODE のデフォルトは 'OR' です。

SELECT SEARCH((*), 'king') result, *
  FROM lines
  ORDER BY act_scene_line LIMIT 10;

Copy

+--------+---------+---------------------------+------------+----------------+-----------------+--------------------------------------------------------+
| RESULT | LINE_ID | PLAY                      | SPEECH_NUM | ACT_SCENE_LINE | CHARACTER       | LINE                                                   |
|--------+---------+---------------------------+------------+----------------+-----------------+--------------------------------------------------------|
| True   |   75787 | Pericles                  |        178 | 1.0.21         | LODOVICO        | This king unto him took a fere,                        |
| True   |   43494 | King John                 |          1 | 1.1.1          | KING JOHN       | Now, say, Chatillon, what would France with us?        |
| True   |   49031 | King Lear                 |          1 | 1.1.1          | KENT            | I thought the king had more affected the Duke of       |
| True   |   78407 | Richard II                |          1 | 1.1.1          | KING RICHARD II | Old John of Gaunt, time-honour'd Lancaster,            |
| False  |   67000 | A Midsummer Night's Dream |          1 | 1.1.1          | THESEUS         | Now, fair Hippolyta, our nuptial hour                  |
| True   |       4 | Henry IV Part 1           |          1 | 1.1.1          | KING HENRY IV   | So shaken as we are, so wan with care,                 |
| False  |   12664 | All's Well That Ends Well |          1 | 1.1.1          | COUNTESS        | In delivering my son from me, I bury a second husband. |
| True   |    9526 | Henry VI Part 3           |          1 | 1.1.1          | WARWICK         | I wonder how the king escaped our hands.               |
| False  |   52797 | Love's Labour's Lost      |          1 | 1.1.1          | FERDINAND       | Let fame, that all hunt after in their lives,          |
| True   |   28487 | Cymbeline                 |          3 | 1.1.10         | First Gentleman | Is outward sorrow, though I think the king             |
+--------+---------+---------------------------+------------+----------------+-----------------+--------------------------------------------------------+

また、 ILIKE と EXCLUDE キーワードを使用してフィルターすることできます。これらのキーワードの詳細については、 SELECT をご参照ください。

SELECT SEARCH(* ILIKE '%line', 'king') result, play, character, line
  FROM lines
  ORDER BY act_scene_line LIMIT 10;

Copy

+--------+---------------------------+-----------------+--------------------------------------------------------+
| RESULT | PLAY                      | CHARACTER       | LINE                                                   |
|--------+---------------------------+-----------------+--------------------------------------------------------|
| True   | Pericles                  | LODOVICO        | This king unto him took a fere,                        |
| False  | King John                 | KING JOHN       | Now, say, Chatillon, what would France with us?        |
| True   | King Lear                 | KENT            | I thought the king had more affected the Duke of       |
| False  | Richard II                | KING RICHARD II | Old John of Gaunt, time-honour'd Lancaster,            |
| False  | A Midsummer Night's Dream | THESEUS         | Now, fair Hippolyta, our nuptial hour                  |
| False  | Henry IV Part 1           | KING HENRY IV   | So shaken as we are, so wan with care,                 |
| False  | All's Well That Ends Well | COUNTESS        | In delivering my son from me, I bury a second husband. |
| True   | Henry VI Part 3           | WARWICK         | I wonder how the king escaped our hands.               |
| False  | Love's Labour's Lost      | FERDINAND       | Let fame, that all hunt after in their lives,          |
| True   | Cymbeline                 | First Gentleman | Is outward sorrow, though I think the king             |
+--------+---------------------------+-----------------+--------------------------------------------------------+

この検索では、 play または line 列のデータを検索しないように、 EXCLUDE キーワードを使用しています。

SELECT SEARCH(* EXCLUDE (play, line), 'king') result, play, character, line
  FROM lines
  ORDER BY act_scene_line LIMIT 10;

Copy

+--------+---------------------------+-----------------+--------------------------------------------------------+
| RESULT | PLAY                      | CHARACTER       | LINE                                                   |
|--------+---------------------------+-----------------+--------------------------------------------------------|
| False  | Pericles                  | LODOVICO        | This king unto him took a fere,                        |
| True   | King John                 | KING JOHN       | Now, say, Chatillon, what would France with us?        |
| False  | King Lear                 | KENT            | I thought the king had more affected the Duke of       |
| True   | Richard II                | KING RICHARD II | Old John of Gaunt, time-honour'd Lancaster,            |
| False  | A Midsummer Night's Dream | THESEUS         | Now, fair Hippolyta, our nuptial hour                  |
| True   | Henry IV Part 1           | KING HENRY IV   | So shaken as we are, so wan with care,                 |
| False  | All's Well That Ends Well | COUNTESS        | In delivering my son from me, I bury a second husband. |
| False  | Henry VI Part 3           | WARWICK         | I wonder how the king escaped our hands.               |
| False  | Love's Labour's Lost      | FERDINAND       | Let fame, that all hunt after in their lives,          |
| False  | Cymbeline                 | First Gentleman | Is outward sorrow, though I think the king             |
+--------+---------------------------+-----------------+--------------------------------------------------------+

結合されたテーブルの対象となる列に対するワイルドカード検索¶

この例では、車種に関する情報を含む2つの小さなテーブルを使用しています。テーブル t1 には2つの文字列があり、テーブル t2 には3つの文字列がありますテーブルの作成とロードは次のとおりです。

CREATE OR REPLACE TABLE t1 (col1 INT, col2 VARCHAR(20), col3 VARCHAR(20));
INSERT INTO t1 VALUES
  (1,'Mini','Cooper'),
  (2,'Mini','Cooper S'),
  (3,'Mini','Countryman'),
  (4,'Mini','Countryman S');
CREATE OR REPLACE TABLE t2 (col1 INT, col2 VARCHAR(20), col3 VARCHAR(20), col4 VARCHAR(20));
INSERT INTO t2 VALUES
  (1,'Mini','Cooper', 'Convertible'),
  (2,'Mini','Cooper S', 'Convertible'),
  (3,'Mini','Countryman SE','ALL4'),
  (4,'Mini','Countryman S','ALL4');

Copy

t1.* と t2.* を検索すると、以下の2つのクエリの結果は異なります。 t1 では2列のみが検索対象ですが、 t2 では3列が検索対象となります。

SELECT * FROM t1 JOIN t2 USING(col1)
  WHERE SEARCH((t1.*),'s all4');

Copy

+------+------+--------------+------+--------------+-------------+
| COL1 | COL2 | COL3         | COL2 | COL3         | COL4        |
|------+------+--------------+------+--------------+-------------|
|    2 | Mini | Cooper S     | Mini | Cooper S     | Convertible |
|    4 | Mini | Countryman S | Mini | Countryman S | ALL4        |
+------+------+--------------+------+--------------+-------------+

SELECT * FROM t1 JOIN t2 USING(col1)
  WHERE SEARCH((t2.*),'s all4');

Copy

+------+------+--------------+------+---------------+-------------+
| COL1 | COL2 | COL3         | COL2 | COL3          | COL4        |
|------+------+--------------+------+---------------+-------------|
|    2 | Mini | Cooper S     | Mini | Cooper S      | Convertible |
|    3 | Mini | Countryman   | Mini | Countryman SE | ALL4        |
|    4 | Mini | Countryman S | Mini | Countryman S  | ALL4        |
+------+------+--------------+------+---------------+-------------+

UNION サブクエリの出力に対するワイルドカード検索¶

次の例では、前の例と同じ2つのテーブルを使用します。この場合、検索はサブクエリの結果であるテーブル、 t3 の対象となるすべての列に対して行われます。このサブクエリは、 t1 と t2 の最初の3列（5行）の UNION を計算します。検索は、 UNION の結果から一致する2行を返します。

SELECT *
  FROM (
    SELECT col1, col2, col3 FROM t1
    UNION
    SELECT col1, col2, col3 FROM t2
    ) AS T3
  WHERE SEARCH((T3.*),'s');

Copy

+------+------+--------------+
| COL1 | COL2 | COL3         |
|------+------+--------------|
|    2 | Mini | Cooper S     |
|    4 | Mini | Countryman S |
+------+------+--------------+

複数の検索文字列に一致する行を見つける¶

以下の例では、 SEARCH_MODE 引数を使用して、両方の検索語が同じ列で同時に出現した場合に一致を見つける論理積セマンティクスを指定しています。論理積セマンティックスを使用するには、 SEARCH_MODE 引数を 'AND' に設定します。

SELECT act_scene_line, character, line
  FROM lines
  WHERE SEARCH(line, 'Rosencrantz Guildenstern', SEARCH_MODE => 'AND')
    AND act_scene_line IS NOT NULL;

Copy

+----------------+------------------+-----------------------------------------------------------+
| ACT_SCENE_LINE | CHARACTER        | LINE                                                      |
|----------------+------------------+-----------------------------------------------------------|
| 2.2.1          | KING CLAUDIUS    | Welcome, dear Rosencrantz and Guildenstern!               |
| 2.2.35         | KING CLAUDIUS    | Thanks, Rosencrantz and gentle Guildenstern.              |
| 2.2.36         | QUEEN GERTRUDE   | Thanks, Guildenstern and gentle Rosencrantz:              |
| 2.2.241        | HAMLET           | Guildenstern? Ah, Rosencrantz! Good lads, how do ye both? |
| 4.6.27         | HORATIO          | where I am. Rosencrantz and Guildenstern hold their       |
| 5.2.60         | HORATIO          | So Guildenstern and Rosencrantz go to't.                  |
| 5.2.389        | First Ambassador | That Rosencrantz and Guildenstern are dead:               |
+----------------+------------------+-----------------------------------------------------------+

論理積セマンティクスを使用する場合、同じ列で両方の検索語が一致する必要があります。例えば、次のクエリは、 KING と Rosencrantz という用語が検索データのどの行にも同じ列で現れないため、結果を返しません。

SELECT act_scene_line, character, line
  FROM lines
  WHERE SEARCH(line, 'KING Rosencrantz', SEARCH_MODE => 'AND')
    AND act_scene_line IS NOT NULL;

Copy

+----------------+-----------+------+
| ACT_SCENE_LINE | CHARACTER | LINE |
|----------------+-----------+------|
+----------------+-----------+------+

SEARCH_MODE 引数を 'OR' に設定して、論理和セマンティクス（デフォルト）を使用する同様のクエリは、検索データに一致を見つけます。

SELECT act_scene_line, character, line
  FROM lines
  WHERE SEARCH(line, 'KING Rosencrantz', SEARCH_MODE => 'OR')
    AND act_scene_line IS NOT NULL;

Copy

+----------------+------------------+-----------------------------------------------------------+
| ACT_SCENE_LINE | CHARACTER        | LINE                                                      |
|----------------+------------------+-----------------------------------------------------------|
| 1.1.1          | WARWICK          | I wonder how the king escaped our hands.                  |
| 1.1.10         | First Gentleman  | Is outward sorrow, though I think the king                |
| 2.2.1          | KING CLAUDIUS    | Welcome, dear Rosencrantz and Guildenstern!               |
| 2.2.35         | KING CLAUDIUS    | Thanks, Rosencrantz and gentle Guildenstern.              |
| 2.2.36         | QUEEN GERTRUDE   | Thanks, Guildenstern and gentle Rosencrantz:              |
| 2.2.241        | HAMLET           | Guildenstern? Ah, Rosencrantz! Good lads, how do ye both? |
| 4.6.27         | HORATIO          | where I am. Rosencrantz and Guildenstern hold their       |
| 5.2.60         | HORATIO          | So Guildenstern and Rosencrantz go to't.                  |
| 5.2.389        | First Ambassador | That Rosencrantz and Guildenstern are dead:               |
| 1.1.1          | KENT             | I thought the king had more affected the Duke of          |
| 1.0.21         | LODOVICO         | This king unto him took a fere,                           |
+----------------+------------------+-----------------------------------------------------------+

結合での VARIANT と VARCHAR データの検索¶

次の例は、 car_rentals と car_sales の2つのテーブルを結合し、両方のテーブル列に検索を適用しています。 car_sales テーブルには VARIANT データが含まれています。 car_sales テーブルとそのデータについては、半構造化データのクエリで説明しています。以下の SQL ステートメントは、 car_rentals テーブルを作成し、そこにデータを挿入します。

CREATE OR REPLACE TABLE car_rentals(
  vehicle_make VARCHAR(30),
  dealership VARCHAR(30),
  salesperson VARCHAR(30));

INSERT INTO car_rentals VALUES
  ('Toyota', 'Tindel Toyota', 'Greg Northrup'),
  ('Honda', 'Valley View Auto Sales', 'Frank Beasley'),
  ('Tesla', 'Valley View Auto Sales', 'Arturo Sandoval');

Copy

クエリを実行します。

SELECT SEARCH((r.vehicle_make, r.dealership, s.src:dealership), 'Toyota Tesla')
    AS contains_toyota_tesla, r.vehicle_make, r.dealership,s.src:dealership
  FROM car_rentals r JOIN car_sales s
    ON r.SALESPERSON=s.src:salesperson.name;

Copy

+-----------------------+--------------+------------------------+--------------------------+
| CONTAINS_TOYOTA_TESLA | VEHICLE_MAKE | DEALERSHIP             | S.SRC:DEALERSHIP         |
|-----------------------+--------------+------------------------+--------------------------|
| True                  | Toyota       | Tindel Toyota          | "Tindel Toyota"          |
| False                 | Honda        | Valley View Auto Sales | "Valley View Auto Sales" |
+-----------------------+--------------+------------------------+--------------------------+

この2番目の例では、同じデータに対して、異なる検索語が使用されています。

SELECT SEARCH((r.vehicle_make, r.dealership, s.src:dealership), 'Toyota Honda')
    AS contains_toyota_honda, r.vehicle_make, r.dealership, s.src:dealership
  FROM car_rentals r JOIN car_sales s
    ON r.SALESPERSON =s.src:salesperson.name;

Copy

+-----------------------+--------------+------------------------+--------------------------+
| CONTAINS_TOYOTA_HONDA | VEHICLE_MAKE | DEALERSHIP             | S.SRC:DEALERSHIP         |
|-----------------------+--------------+------------------------+--------------------------|
| True                  | Toyota       | Tindel Toyota          | "Tindel Toyota"          |
| True                  | Honda        | Valley View Auto Sales | "Valley View Auto Sales" |
+-----------------------+--------------+------------------------+--------------------------+

アナライザーを使用した検索動作の調整¶

以下の例は、 UNICODE_ANALYZER または NO_OP_ANALYZER というデフォルト以外のアナライザーを指定して、 SEARCH 関数の動作を調整する方法を示しています。

最初の例では、 NO_OP_ANALYZER を使用して、 1.2.500 という文字列が lines テーブルのいずれかの行の act_scene_line 列のコンテンツと完全に一致するかどうかをテストしています。2つの行が検索対象となります。

SELECT line_id, act_scene_line FROM lines
  WHERE SEARCH(act_scene_line, '1.2.500', ANALYZER=>'NO_OP_ANALYZER');

Copy

+---------+----------------+
| LINE_ID | ACT_SCENE_LINE |
|---------+----------------|
|   91998 | 1.2.500        |
|  108464 | 1.2.500        |
+---------+----------------+

この例の関数の引数として NO_OP_ANALYZER を削除すると、検索は大量の行を返します。デフォルトのアナライザーは、 1、 2、 500 を異なるトークンとして扱います。したがって、この関数は、 1、 2、 500 が存在するすべての行について、 TRUE を返します（順序や組み合わせは問いません）。

このクエリを変更して、2番目の引数にプレフィックス 1.2 だけを含めるようにすると、デフォルトのアナライザーは TRUE を返しますが、 UNICODE_ANALYZER と NO_OP_ANALYZER の両方は FALSE を返します。デフォルトのアナライザーはこれらの値のピリオドを区切り文字として扱いますが、Unicodeアナライザーはそのように扱いません。

次の2つのクエリは、デフォルトのアナライザーの代わりに UNICODE_ANALYZER を使用した別の効果を表示しています。UNICODE_ANALYZER を使用する最初のクエリは、1行のみを返します。2番目の引数の余分な一重引用符は、アポストロフィの一重引用符をエスケープするためにあることに注意してください。一重引用符付き文字列定数をご参照ください。

SELECT DISTINCT(play)
  FROM lines
  WHERE SEARCH(play, 'love''s', ANALYZER=>'UNICODE_ANALYZER');

Copy

+----------------------+
| PLAY                 |
|----------------------|
| Love's Labour's Lost |
+----------------------+

デフォルトのアナライザーを使用した2番目のクエリでは、4行が返されます。これは、デフォルトのアナライザーがアポストロフィ文字を区切り文字として扱うためです。トークンとして文字「s」を含む文字列はすべて検索対象になります。この例では、関数は「アポストロフィs」（'s）を含むすべての文字列に対して TRUE を返します。

SELECT DISTINCT(play) FROM lines WHERE SEARCH(play, 'love''s');

Copy

+---------------------------+
| PLAY                      |
|---------------------------|
| All's Well That Ends Well |
| Love's Labour's Lost      |
| A Midsummer Night's Dream |
| The Winter's Tale         |
+---------------------------+

予期されるエラーケースの例¶

以下の例は、予期される構文エラーを返すクエリを示しています。

この例は、 5 が search_string 引数のデータ型としてサポートされていないため、失敗します。

SELECT SEARCH(line, 5) FROM lines;

Copy

001045 (22023): SQL compilation error:
argument needs to be a string: '1'

この例は、 search_data 引数にサポートされているデータ型の列が指定されていないため、失敗します。

SELECT SEARCH(line_id, 'dream') FROM lines;

Copy

001173 (22023): SQL compilation error: error line 1 at position 7: Expected non-empty set of columns supporting full-text search.

この例は、 search_data 引数にサポートされているデータ型の列が指定されているため、成功します。関数は line_id 列がサポートされていないデータ型であるため、無視します。

SELECT SEARCH((line_id, play), 'dream') FROM lines
  ORDER BY play LIMIT 5;

Copy

+----------------------------------+
| SEARCH((LINE_ID, PLAY), 'DREAM') |
|----------------------------------|
| True                             |
| True                             |
| False                            |
| False                            |
| False                            |
+----------------------------------+

この例は、最初の引数に丸括弧なしで複数の文字列リテラルを列挙しているため引数が不一致となり、失敗します。

SELECT SEARCH('docs@snowflake.com', 'careers@snowflake.com', '@');

Copy

001881 (42601): SQL compilation error: Expected 1 named argument(s), found 0

この例は、最初の引数に丸括弧なしで複数の列名を列挙しているため引数が多すぎて、失敗します。

SELECT SEARCH(play,line,'king', ANALYZER=>'UNICODE_ANALYZER') FROM lines;

Copy

000939 (22023): SQL compilation error: error line 1 at position 7
too many arguments for function [SEARCH(LINES.PLAY, LINES.LINE, 'king', 'UNICODE_ANALYZER')] expected 3, got 4

この例は、検索文字列の引数として列名を受け付けないため、失敗します。

SELECT SEARCH(line, character) FROM lines;

Copy

001015 (22023): SQL compilation error:
argument 2 to function SEARCH needs to be constant, found 'LINES.CHARACTER'

SEARCH のサンプルデータ¶

このセクションのいくつかの例では、シェイクスピアの戯曲のテキストを含むテーブルをクエリします。各行のテキストはテーブルの1行に保管されます。その他の列は、戯曲の名前、登場人物の名前などを特定します。 lines テーブルは以下の構造を使用します。

DESCRIBE TABLE lines;

Copy

+----------------+---------------+--------+-------+-
| name           | type          | kind   | null? |
|----------------+---------------+--------+-------+-
| LINE_ID        | NUMBER(38,0)  | COLUMN | Y     |
| PLAY           | VARCHAR(50)   | COLUMN | Y     |
| SPEECH_NUM     | NUMBER(38,0)  | COLUMN | Y     |
| ACT_SCENE_LINE | VARCHAR(10)   | COLUMN | Y     |
| CHARACTER      | VARCHAR(30)   | COLUMN | Y     |
| LINE           | VARCHAR(2000) | COLUMN | Y     |
+----------------+---------------+--------+-------+-

例えば、このテーブルの1つの行は次のようになります。

SELECT * FROM lines
  WHERE line_id=34230;

Copy

+---------+--------+------------+----------------+-----------+--------------------------------------------+
| LINE_ID | PLAY   | SPEECH_NUM | ACT_SCENE_LINE | CHARACTER | LINE                                       |
|---------+--------+------------+----------------+-----------+--------------------------------------------|
|   34230 | Hamlet |         19 | 3.1.64         | HAMLET    | To be, or not to be, that is the question: |
+---------+--------+------------+----------------+-----------+--------------------------------------------+

このセクションの例を実行したい場合、以下のコマンドを実行してこのテーブルを作成します。

CREATE OR REPLACE TABLE lines(
  line_id INT,
  play VARCHAR(50),
  speech_num INT,
  act_scene_line VARCHAR(10),
  character VARCHAR(30),
  line VARCHAR(2000)
  );

INSERT INTO lines VALUES
  (4,'Henry IV Part 1',1,'1.1.1','KING HENRY IV','So shaken as we are, so wan with care,'),
  (13,'Henry IV Part 1',1,'1.1.10','KING HENRY IV','Which, like the meteors of a troubled heaven,'),
  (9526,'Henry VI Part 3',1,'1.1.1','WARWICK','I wonder how the king escaped our hands.'),
  (12664,'All''s Well That Ends Well',1,'1.1.1','COUNTESS','In delivering my son from me, I bury a second husband.'),
  (15742,'All''s Well That Ends Well',114,'5.3.378','KING','Your gentle hands lend us, and take our hearts.'),
  (16448,'As You Like It',2,'2.3.6','ADAM','And wherefore are you gentle, strong and valiant?'),
  (24055,'The Comedy of Errors',14,'5.1.41','AEMELIA','Be quiet, people. Wherefore throng you hither?'),
  (28487,'Cymbeline',3,'1.1.10','First Gentleman','Is outward sorrow, though I think the king'),
  (33522,'Hamlet',1,'2.2.1','KING CLAUDIUS','Welcome, dear Rosencrantz and Guildenstern!'),
  (33556,'Hamlet',5,'2.2.35','KING CLAUDIUS','Thanks, Rosencrantz and gentle Guildenstern.'),
  (33557,'Hamlet',6,'2.2.36','QUEEN GERTRUDE','Thanks, Guildenstern and gentle Rosencrantz:'),
  (33776,'Hamlet',67,'2.2.241','HAMLET','Guildenstern? Ah, Rosencrantz! Good lads, how do ye both?'),
  (34230,'Hamlet',19,'3.1.64','HAMLET','To be, or not to be, that is the question:'),
  (35672,'Hamlet',7,'4.6.27','HORATIO','where I am. Rosencrantz and Guildenstern hold their'),
  (36289,'Hamlet',14,'5.2.60','HORATIO','So Guildenstern and Rosencrantz go to''t.'),
  (36640,'Hamlet',143,'5.2.389','First Ambassador','That Rosencrantz and Guildenstern are dead:'),
  (43494,'King John',1,'1.1.1','KING JOHN','Now, say, Chatillon, what would France with us?'),
  (43503,'King John',5,'1.1.10','CHATILLON','To this fair island and the territories,'),
  (49031,'King Lear',1,'1.1.1','KENT','I thought the king had more affected the Duke of'),
  (49040,'King Lear',4,'1.1.10','GLOUCESTER','so often blushed to acknowledge him, that now I am'),
  (52797,'Love''s Labour''s Lost',1,'1.1.1','FERDINAND','Let fame, that all hunt after in their lives,'),
  (55778,'Love''s Labour''s Lost',405,'5.2.971','ADRIANO DE ARMADO','Apollo. You that way: we this way.'),
  (67000,'A Midsummer Night''s Dream',1,'1.1.1','THESEUS','Now, fair Hippolyta, our nuptial hour'),
  (69296,'A Midsummer Night''s Dream',104,'5.1.428','PUCK','And Robin shall restore amends.'),
  (75787,'Pericles',178,'1.0.21','LODOVICO','This king unto him took a fere,'),
  (78407,'Richard II',1,'1.1.1','KING RICHARD II','Old John of Gaunt, time-honour''d Lancaster,'),
  (91998,'The Tempest',108,'1.2.500','FERDINAND','Were I but where ''tis spoken.'),
  (92454,'The Tempest',150,'2.1.343','ALONSO','Wherefore this ghastly looking?'),
  (99330,'Troilus and Cressida',30,'1.1.102','AENEAS','How now, Prince Troilus! wherefore not afield?'),
  (100109,'Troilus and Cressida',31,'2.1.53','ACHILLES','Why, how now, Ajax! wherefore do you thus? How now,'),
  (108464,'The Winter''s Tale',106,'1.2.500','CAMILLO','As or by oath remove or counsel shake')
  ;

Copy