SEARCH_IP¶
VARIANT、 OBJECT、 ARRAY 列のフィールドを含む、1つ以上のテーブルから指定された文字列の列で有効な IPv4 アドレスを検索します。検索は、単一の IP アドレス、または指定した IP アドレスの範囲に基づいて行われます。この IP アドレスが、指定された列またはフィールドの IP アドレスと一致する場合、関数は TRUE を返します。
これらの関数の使用に関する詳細については、 全文検索を使用する をご参照ください。
構文¶
SEARCH_IP( <search_data>, <search_string> )
引数¶
search_data検索したいデータ。文字列リテラル、列名、または VARIANT 列フィールドへの パス のコンマ区切りリストで表されます。検索データは単一のリテラル文字列でも可能で、これは関数をテストするときに便利な場合があります。
ワイルドカード文字（
*）を指定することができます。*は、関数のスコープ内にあるすべてのテーブルで対象となるすべての列に展開されます。対象となる列は、データ型が VARCHAR （テキスト）、 VARIANT、 ARRAY、 OBJECT の列です。VARIANT、 ARRAY、 OBJECT のデータは検索用にテキストに変換されます。また、 ILIKE と EXCLUDE キーワードを使用してフィルターすることできます。この引数の詳細については、 SEARCH 関数の
search_dataの説明をご参照ください。search_string以下のいずれかを含む VARCHAR 文字列:
192.0.2.1のような標準的な IPv4 形式の完全かつ有効な IP アドレス。192.0.2.1/24などの、クラスレスドメイン間ルーティング（CIDR）の範囲を持つ標準 IPv4 形式の有効な IP アドレス。（
192.0.2.1の代わりに）192.000.002.001などの、先頭のゼロが標準の IPv4 形式で有効な IP アドレス。この機能では、 IP アドレスの各部分に最大3桁の数字を使用できます。
この引数はリテラル文字列である必要があります。列名はサポートされていません。文字列を囲む一重引用符の1つのペアを指定します。
IP アドレスは複数指定できません。IPv6 アドレス、または部分的な IPv4 アドレスは指定できません。
戻り値¶
BOOLEAN を返します。
search_stringに有効な IP アドレスが指定され、search_dataに一致する IP アドレスが見つかった場合、 TRUE を返します。CIDR の範囲を持つ有効な IP アドレスが
search_stringに指定され、指定された範囲の IP アドレスがsearch_dataで見つかった場合、 TRUE を返します。search_stringが空で、有効な IP アドレスがsearch_dataに見つからない場合、 TRUE を返します。これらの引数のいずれかが NULL の場合、 NULL を返します。
それ以外の場合は、 FALSE を返します。
使用上の注意¶
SEARCH_IP 関数は、 VARCHAR、 VARIANT、 ARRAY、 OBJECT データに対してのみ動作します。引数
search_dataにこれらのデータ型のデータが含まれていない場合、この関数はエラーを返します。引数search_dataにサポートされているデータ型とサポートされていないデータ型の両方のデータが含まれている場合、関数はサポートされているデータ型のデータを検索し、サポートされていないデータ型のデータを黙って無視します。例については、 予期されるエラーケースの例 をご参照ください。SEARCH_IP 関数は、 VARCHAR、 VARIANT、 ARRAY、 OBJECT データに対してのみ動作します。他のデータ型を検索しようとすると、この関数はエラーを返します。例については、 予期されるエラーケースの例 をご参照ください。
ENTITY_ANALYZER を指定する ALTER TABLE コマンドを使用することで、 SEARCH_IP 関数呼び出しの対象となる列に FULL_TEXT 検索最適化を追加できます。例:
ALTER TABLE ipt ADD SEARCH OPTIMIZATION ON FULL_TEXT( ip1, ANALYZER => 'ENTITY_ANALYZER');
詳細については、 FULL_TEXT 検索最適化の有効化 をご参照ください。
例¶
次の例は、 SEARCH_IP 関数を使用して VARCHAR と VARIANT 列をクエリする方法を示しています。
VARCHAR 列での一致する IP アドレスの検索¶
ipt という名前のテーブルを作成し、テーブルに2つの行を挿入します。
CREATE OR REPLACE TABLE ipt(id INT, ip1 VARCHAR(20), ip2 VARCHAR(20));
INSERT INTO ipt VALUES(1, '192.0.2.146', '203.0.113.5');
INSERT INTO ipt VALUES(2, '192.0.2.111', '192.000.002.146');
以下の検索クエリを実行します。最初のクエリは、 SELECT リストで SEARCH_IP 関数を使用し、テーブルの2つの VARCHAR 列を検索します。
SELECT ip1,
ip2,
SEARCH_IP((ip1, ip2), '192.0.2.146')
FROM ipt
ORDER BY ip1;
+-------------+-----------------+--------------------------------------+
| IP1 | IP2 | SEARCH_IP((IP1, IP2), '192.0.2.146') |
|-------------+-----------------+--------------------------------------|
| 192.0.2.111 | 192.000.002.146 | True |
| 192.0.2.146 | 203.0.113.5 | True |
+-------------+-----------------+--------------------------------------+
192.000.002.146 には先頭のゼロがありますが、 search_data 192.000.002.146 は search_string 192.0.2.146 と一致することに注意してください。
このクエリは、 CIDR の範囲を持つ search_string を示しています。
SELECT ip1,
ip2,
SEARCH_IP((ip1, ip2), '192.0.2.1/20')
FROM ipt
ORDER BY ip1;
+-------------+-----------------+---------------------------------------+
| IP1 | IP2 | SEARCH_IP((IP1, IP2), '192.0.2.1/20') |
|-------------+-----------------+---------------------------------------|
| 192.0.2.111 | 192.000.002.146 | True |
| 192.0.2.146 | 203.0.113.5 | True |
+-------------+-----------------+---------------------------------------+
このクエリは、先頭のゼロがある search_string が先頭のゼロが省略された IP アドレスに対して True を返すことを示しています。
SELECT ip1,
ip2,
SEARCH_IP((ip1, ip2), '203.000.113.005')
FROM ipt
ORDER BY ip1;
+-------------+-----------------+------------------------------------------+
| IP1 | IP2 | SEARCH_IP((IP1, IP2), '203.000.113.005') |
|-------------+-----------------+------------------------------------------|
| 192.0.2.111 | 192.000.002.146 | False |
| 192.0.2.146 | 203.0.113.5 | True |
+-------------+-----------------+------------------------------------------+
このクエリは、 WHERE 句内で関数を使用し、 ip2 列のみを検索します。
SELECT ip1,
ip2
FROM ipt
WHERE SEARCH_IP(ip2, '203.0.113.5')
ORDER BY ip1;
+-------------+-------------+
| IP1 | IP2 |
|-------------+-------------|
| 192.0.2.146 | 203.0.113.5 |
+-------------+-------------+
この関数が WHERE 句で使用され、一致するものがなかった場合、値を返しません。
SELECT ip1,
ip2
FROM ipt
WHERE SEARCH_IP(ip2, '203.0.113.1')
ORDER BY ip1;
+-----+-----+
| IP1 | IP2 |
|-----+-----|
+-----+-----+
この例のように、 SEARCH 関数の1番目の引数として * 文字（または table.*）を使用することができます。この検索は、選択するテーブルの対象となるすべての列に対して行われます。
SELECT ip1,
ip2
FROM ipt
WHERE SEARCH_IP((*), '203.0.113.5')
ORDER BY ip1;
+-------------+-------------+
| IP1 | IP2 |
|-------------+-------------|
| 192.0.2.146 | 203.0.113.5 |
+-------------+-------------+
また、 ILIKE と EXCLUDE キーワードを使用してフィルターすることできます。これらのキーワードの詳細については、 SELECT をご参照ください。
この検索は、 ILIKE キーワードを使用して、文字列 ip で始まる列のみを検索します。
SELECT ip1,
ip2
FROM ipt
WHERE SEARCH_IP(* ILIKE 'ip%', '192.0.2.111')
ORDER BY ip1;
+-------------+-----------------+
| IP1 | IP2 |
|-------------+-----------------|
| 192.0.2.111 | 192.000.002.146 |
+-------------+-----------------+
ipt テーブル列の FULL_TEXT 検索最適化を有効にする には、以下の ALTER TABLE コマンドを実行します。
ALTER TABLE ipt ADD SEARCH OPTIMIZATION ON FULL_TEXT(
ip1,
ip2,
ANALYZER => 'ENTITY_ANALYZER');
注釈
指定する列は VARCHAR または VARIANT 列にする必要があります。他のデータ型の列はサポートされていません。
VARIANT 列での一致する IP アドレスの検索¶
この例は、 SEARCH_IP 関数を使用して VARIANT 列のフィールドへのパスを検索します。 iptv という名前のテーブルを作成し、テーブルに2つの行を挿入します。
CREATE OR REPLACE TABLE iptv(ip1 VARIANT);
INSERT INTO iptv(ip1)
SELECT PARSE_JSON(' { "ipv1": "203.0.113.5", "ipv2": "203.0.113.5" } ');
INSERT INTO iptv(ip1)
SELECT PARSE_JSON(' { "ipv1": "192.0.2.146", "ipv2": "203.0.113.5" } ');
以下の検索クエリを実行します。最初のクエリは、 ipv1 フィールドのみを検索します。2番目のクエリは ipv1 と ipv2 を検索します。
SELECT * FROM iptv
WHERE SEARCH_IP((ip1:"ipv1"), '203.0.113.5');
+--------------------------+
| IP1 |
|--------------------------|
| { |
| "ipv1": "203.0.113.5", |
| "ipv2": "203.0.113.5" |
| } |
+--------------------------+
SELECT * FROM iptv
WHERE SEARCH_IP((ip1:"ipv1",ip1:"ipv2"), '203.0.113.5');
+--------------------------+
| IP1 |
|--------------------------|
| { |
| "ipv1": "203.0.113.5", |
| "ipv2": "203.0.113.5" |
| } |
| { |
| "ipv1": "192.0.2.146", |
| "ipv2": "203.0.113.5" |
| } |
+--------------------------+
この ip1 VARIANT 列とそのフィールドの FULL_TEXT 検索最適化を有効にする には、以下の ALTER TABLE コマンドを実行します。
ALTER TABLE iptv ADD SEARCH OPTIMIZATION ON FULL_TEXT(
ip1:"ipv1",
ip1:"ipv2",
ANALYZER => 'ENTITY_ANALYZER');
注釈
指定する列は VARCHAR または VARIANT 列にする必要があります。他のデータ型の列はサポートされていません。
予期されるエラーケースの例¶
以下の例は、予期される構文エラーを返すクエリを示しています。
この例は、 5 が search_string 引数のデータ型としてサポートされていないため、失敗します。
SELECT SEARCH_IP(ip1, 5) FROM ipt;
001045 (22023): SQL compilation error:
argument needs to be a string: '1'
この例は、 search_data 引数にサポートされているデータ型の列が指定されていないため、失敗します。
SELECT SEARCH_IP(id, '192.0.2.146') FROM ipt;
001173 (22023): SQL compilation error: error line 1 at position 7: Expected non-empty set of columns supporting full-text search.
この例は、 search_data 引数にサポートされているデータ型の列が指定されているため、成功します。関数は id 列がサポートされていないデータ型であるため、無視します。
SELECT SEARCH_IP((id, ip1), '192.0.2.146') FROM ipt;
+-------------------------------------+
| SEARCH_IP((ID, IP1), '192.0.2.146') |
|-------------------------------------|
| True |
| False |
+-------------------------------------+