- カテゴリ：
文字列とバイナリ関数 （全文検索）
SEARCH¶
VARIANT 、 OBJECT 、 ARRAY の列のフィールドを含む、1つ以上のテーブルから指定された列の文字データ（テキスト）を検索します。テキストアナライザーは、テキストをトークン（単語や数値など、テキストの個別の単位）に分割します。指定しない場合は、デフォルトのアナライザーが適用されます。
これらの関数の使用に関する詳細については、 全文検索を使用する をご参照ください。
構文¶
SEARCH( <search_data>, <search_string> [ , ANALYZER => '<analyzer_name>' ] )
引数¶
search_data検索したいデータ。文字列リテラル、列名、または VARIANT 列フィールドへの パス のコンマ区切りリストで表されます。検索データは単一のリテラル文字列でも可能で、これは関数をテストするときに便利な場合があります。
ワイルドカード文字（
*）を指定することができます。*は、関数のスコープ内にあるすべてのテーブルで対象となるすべての列に展開されます。対象となる列は、データ型が VARCHAR （テキスト）、 VARIANT、 ARRAY、 OBJECT の列です。VARIANT、 ARRAY、 OBJECT のデータは検索用にテキストに変換されます。関数にワイルドカードを渡す場合、ワイルドカードをテーブルの名前またはエイリアスで修飾することができます。例えば、
mytableというテーブルからすべての列を渡すには、以下のように指定します。(mytable.*)
ILIKE と EXCLUDE キーワードを使ってフィルタリングすることもできます。
ILIKE は、指定されたパターンに一致する列名でフィルターします。許されるパターンは1つのみです。例:
(* ILIKE 'col1%')
EXCLUDE は、指定された列に一致しない列名をフィルターします。例:
(* EXCLUDE col1) (* EXCLUDE (col1, col2))
修飾子は、これらのキーワードを使用する場合に有効です。以下の例では、 ILIKE キーワードを使用して、テーブル
mytable内のパターンcol1%に一致するすべての列をフィルターします。(mytable.* ILIKE 'col1%')
ILIKE と EXCLUDE キーワードは、1つの関数呼び出し内で組み合わせることはできません。
ILIKE と EXCLUDE キーワードの詳細については、 SELECT の「パラメータ」セクションをご参照ください。
複数のテーブルがスコープ内にある場合、テーブルを結合するか、 UNION セット演算子を使用することで、複数のテーブルから列を検索することができます。結合や UNION クエリの出力に含まれるすべての列を検索するには、以下のように修飾されていない
*ワイルドカードを使用することができます。SELECT * FROM t AS T1 JOIN t AS T2 USING (col1) WHERE SEARCH((*), 'string');
テーブルを結合する際に特定の列を検索するには、列名を修飾する必要がある場合があります（例えば、
table2.colname）。次のように、*のワイルドカードを使うこともできます。SELECT * FROM t AS T1 JOIN t AS T2 USING (col1) WHERE SEARCH((T2.*), 'string');
ただし 、関数に
*またはtable.*を複数回指定することはできません。先の結合例では、SEARCH((T1.*, T2.*), 'string')を指定できませんでした。この構文はエラーを返します。*、table.*、または複数の項目が記載されている場合、search_data引数には丸括弧が必要です。例:SEARCH((col1, col2, col3), 'string') SEARCH((t1.*), 'string') SEARCH((*), 'string')
丸括弧で複数の項目を区切らない場合、コンマは関数引数の区切り文字として解析されます。
予期されるエラーケースの例 もご参照ください。
列名、コロンまたはドット、およびドットで区切られたサブフィールドを指定することで、 VARIANT データのフィールドを検索できます。例:
colname:fieldname.subfieldname。このような列のフィールドの指定についての詳細は、 半構造化データの走査 をご参照ください。search_string1つ以上の検索語を含む VARCHAR 文字列。この引数はリテラル文字列である必要があります。列名はサポートされていません。文字列全体に一重引用符の1つのペアを指定します。個々の用語やフレーズを引用符で囲まないでください。使用例：
'blue red green'以下は使用しないでください。
'blue' 'red' 'green'用語のリストは分離可能です。この場合、検索には
blueORredORgreenが含まれます。しかし、 NO_OP_ANALYZER が使用される場合、クエリ文字列はそのまま一致され、トークン化も分離セマンティクスもありません。検索では大文字と小文字は区別されない（ただし、 NO_OP_ANALYZER を使用した場合を除く）ため、
'ONCE'という用語を'Once upon a time'という文字列に対して検索すると、 TRUE が返されます。検索されたデータ内に存在するかどうかに関しては、検索語の順序は関係ありません。
ANALYZER => 'analyzer_name'テキストアナライザーの名前を指定するオプションの引数。名前を一重引用符で囲む必要があります。
アナライザーは、検索語（および検索対象の列のテキスト）をトークンに分割します。検索文字列から抽出されたトークンのいずれかが、検索対象の列またはフィールドから抽出されたトークンと完全に一致する場合、その行は一致します。
アナライザーは、文字列を特定の区切り文字を見つけたところで区切ることによって、文字列をトークン化します。これらの区切り文字は結果のトークンには含まれず、空のトークンは抽出されません。
このパラメーターは、以下の値を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アンダースコア（低いライン）
\nU+000A改行（ラインフィード）
\rU+000Dキャリッジリターン
\tU+0009水平タブ
UNICODE_ANALYZER: 空白と特定の句読点を区切り文字として扱うユニコードの分割規則に基づいてトークン化します。これらの内部ルールは、（さまざまな言語による）自然言語検索のために設計されています。たとえば、デフォルトのアナライザは、 IP アドレスのピリオドや短縮形のアポストロフィを区切り記号として扱いますが、ユニコードアナライザーはそうしません。 アナライザーを使用した検索動作の調整 をご参照ください。
Unicode Text Segmentation アルゴリズムについての詳細は https://unicode.org/reports/tr29/をご参照ください。
NO_OP_ANALYZER: データもクエリ文字列もトークン化しません。検索語は、大文字小文字の区別を含め、列またはフィールドの全文と完全に一致する必要があります。そうでない場合、 SEARCH 関数は FALSE を返します。クエリ文字列が複数のトークンを含んでいるように見えても（例えば、
'sky blue'）、列またはフィールドはクエリー文字列全体と正確に等しくなければなりません。この場合、'sky blue'だけが一致します。'sky'と'blue'は一致しません。
異なるアナライザーの動作の詳細については、 検索語のトークン化方法 をご参照ください。
戻り値¶
BOOLEAN を返します。
search_dataにsearch_stringトークンがあれば、値は 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);
詳細については、 FULL_TEXT 検索最適化の有効化 をご参照ください。
検索語のトークン化方法¶
以下の表は、入力された検索語がどのようにトークンに分割されるかの例をいくつか示したもので、使用するアナライザーが適用するルールによって異なります。表内で、コンマはトークンが分割される場所（分割される場合）を示しています。
検索語 |
トークン: DEFAULT_ANALYZER |
トークン: UNICODE_ANALYZER |
NO_OP_ANALYZER （分割不可） |
|---|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
例¶
以下の例は、 SEARCH 関数のさまざまな使い方を示しています。簡単な使い方から始まり、より複雑な使用例へと進んでいきます。
リテラルに対する一致¶
SEARCH 関数の最も単純な例は、文字列リテラルに対する TRUE または FALSE のテストです。最初の例は、比較が大文字と小文字を区別しない場合、最初と2番目の引数のリテラルは一致するため TRUE を返します。
SELECT SEARCH('king','KING');
+-----------------------------+
| SEARCH('KING','KING') |
|-----------------------------|
| True |
+-----------------------------+
2番目の例では、トークン 32 が、最初の引数に指定されたリテラル 5.1.33 に含まれていないため、 FALSE を返します。
SELECT SEARCH('5.1.33','32');
+-----------------------------+
| SEARCH('5.1.33','32') |
|-----------------------------|
| False |
+-----------------------------+
列参照に対する一致¶
この例では、テーブルの列を1番目の引数として使用します。この関数は、 character 列に検索語のひとつ（king）が存在するため、 TRUE を返します。用語のリストは分離可能です。（ここで使用されているテーブルと、この後のいくつかの例で使用されているテーブルの詳細については、 SEARCH のサンプルデータ をご参照ください。）
SELECT SEARCH(character, 'king queen'),character
FROM lines
WHERE line_id=4;
+--------------------------------------+---------------+
| SEARCH(CHARACTER,'KING QUEEN') | CHARACTER |
|--------------------------------------+---------------|
| True | KING HENRY IV |
+--------------------------------------+---------------+
1つの列に対する WHERE 句検索¶
以下のクエリは、 SEARCH 関数を使用して line 列に「wherefore」という単語を含む行を検索します。
SELECT *
FROM lines
WHERE SEARCH(line, 'wherefore')
ORDER BY character LIMIT 5;
+---------+----------------------+------------+----------------+-----------+-----------------------------------------------------+
| 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;
+---------------------------+-----------------+
| 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列については一致が見つかります。
SELECT play, character, line, act_scene_line
FROM lines
WHERE SEARCH((lines.*), 'king')
ORDER BY act_scene_line LIMIT 10;
+-----------------+-----------------+----------------------------------------------------+----------------+
| 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 という文字列で終わる列のみを検索します。
SELECT play, character, line, act_scene_line
FROM lines
WHERE SEARCH((lines.* ILIKE '%line'), 'king')
ORDER BY act_scene_line LIMIT 10;
+-----------------+-----------------+--------------------------------------------------+----------------+
| 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;
+-----------------+-----------------+----------------------------------------------------+----------------+
| 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 を返します。
SELECT SEARCH((*), 'king') result, *
FROM lines
ORDER BY act_scene_line LIMIT 10;
+--------+---------+---------------------------+------------+----------------+-----------------+--------------------------------------------------------+
| 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 をご参照ください。
この検索では、 ILIKE キーワードを使用して line という文字列で終わる列のみを検索します。
SELECT SEARCH(* ILIKE '%line', 'king') result, play, character, line
FROM lines
ORDER BY act_scene_line LIMIT 10;
+--------+---------------------------+-----------------+--------------------------------------------------------+
| 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;
+--------+---------------------------+-----------------+--------------------------------------------------------+
| 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');
t1.* と t2.* を検索すると、以下の2つのクエリの結果は異なります。 t1 では2列のみが検索対象ですが、 t2 では3列が検索対象となります。
SELECT * FROM t1 JOIN t2 USING(col1)
WHERE SEARCH((t1.*),'s all4');
+------+------+--------------+------+--------------+-------------+
| 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');
+------+------+--------------+------+---------------+-------------+
| 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');
+------+------+--------------+
| COL1 | COL2 | COL3 |
|------+------+--------------|
| 2 | Mini | Cooper S |
| 4 | Mini | Countryman S |
+------+------+--------------+
複数の検索文字列に一致する行を見つける¶
次の例では、 SEARCH を同じクエリで2回使用する方法を示しています。この場合、行が結果の対象となるには、検索文字列の両方が TRUE を返す必要があります。
SELECT act_scene_line, character, line
FROM lines
WHERE SEARCH(line, 'Rosencrantz')
AND SEARCH(line, 'Guildenstern')
AND act_scene_line IS NOT NULL;
+----------------+------------------+-----------------------------------------------------------+
| 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: |
+----------------+------------------+-----------------------------------------------------------+
結合での 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');
クエリを実行します。
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;
+-----------------------+--------------+------------------------+--------------------------+
| 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;
+-----------------------+--------------+------------------------+--------------------------+
| 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');
+---------+----------------+
| 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');
+----------------------+
| PLAY |
|----------------------|
| Love's Labour's Lost |
+----------------------+
デフォルトのアナライザーを使用した2番目のクエリでは、4行が返されます。これは、デフォルトのアナライザーがアポストロフィ文字を区切り文字として扱うためです。トークンとして文字「s」を含む文字列はすべて検索対象になります。この例では、関数は「アポストロフィs」（'s）を含むすべての文字列に対して TRUE を返します。
SELECT DISTINCT(play) FROM lines WHERE SEARCH(play, 'love''s');
+---------------------------+
| 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;
001045 (22023): SQL compilation error:
argument needs to be a string: '1'
この例は、 search_data 引数にサポートされているデータ型の列が指定されていないため、失敗します。
SELECT SEARCH(line_id, 'dream') FROM lines;
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;
+----------------------------------+
| SEARCH((LINE_ID, PLAY), 'DREAM') |
|----------------------------------|
| True |
| True |
| False |
| False |
| False |
+----------------------------------+
この例は、最初の引数に丸括弧なしで複数の文字列リテラルを列挙しているため引数が不一致となり、失敗します。
SELECT SEARCH('docs@snowflake.com', 'careers@snowflake.com', '@');
001881 (42601): SQL compilation error: Expected 1 named argument(s), found 0
この例は、最初の引数に丸括弧なしで複数の列名を列挙しているため引数が多すぎて、失敗します。
SELECT SEARCH(play,line,'king', ANALYZER=>'UNICODE_ANALYZER') FROM lines;
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;
001015 (22023): SQL compilation error:
argument 2 to function SEARCH needs to be constant, found 'LINES.CHARACTER'
SEARCH のサンプルデータ¶
このセクションのいくつかの例では、シェイクスピアの戯曲のテキストを含むテーブルをクエリします。各行のテキストはテーブルの1行に保管されます。その他の列は、戯曲の名前、登場人物の名前などを特定します。 lines テーブルは以下の構造を使用します。
DESCRIBE TABLE lines;
+----------------+---------------+--------+-------+-
| 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;
+---------+--------+------------+----------------+-----------+--------------------------------------------+
| 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')
;