SEARCH_IP¶

VARIANT、 OBJECT、 ARRAY 列のフィールドを含む、1つ以上のテーブルから指定された文字列の列で有効な IPv4 アドレスを検索します。検索は、単一の IP アドレス、または指定した IP アドレスの範囲に基づいて行われます。この IP アドレスが、指定された列またはフィールドの IP アドレスと一致する場合、関数は TRUE を返します。

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

構文¶

SEARCH_IP( <search_data>, <search_string> )

Copy

引数¶

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 アドレス
部分的な IPv4 アドレス
IPv6 アドレス

戻り値¶

BOOLEAN を返します。

search_string に有効な IP アドレスが指定され、 search_data に一致する IP アドレスが見つかった場合、 TRUE を返します。
CIDR の範囲を持つ有効な IP アドレスが search_string に指定され、指定された範囲の IP アドレスが search_data で見つかった場合、 TRUE を返します。
これらの引数のいずれかが NULL の場合、 NULL を返します。
それ以外の場合は、 FALSE を返します。

使用上の注意¶

SEARCH_IP 関数は、 VARCHAR、 VARIANT、 ARRAY、 OBJECT データに対してのみ動作します。引数 search_data にこれらのデータ型のデータが含まれていない場合、この関数はエラーを返します。引数 search_data にサポートされているデータ型とサポートされていないデータ型の両方のデータが含まれている場合、関数はサポートされているデータ型のデータを検索し、サポートされていないデータ型のデータを黙って無視します。例については、予期されるエラーケースの例をご参照ください。
引数 search_string が有効な IP アドレスではない場合、関数はエラーを返します。例については、予期されるエラーケースの例をご参照ください。
ENTITY_ANALYZER を指定する ALTER TABLE コマンドを使用することで、 SEARCH_IP 関数呼び出しの対象となる列に FULL_TEXT 検索最適化を追加できます。例:
```
ALTER TABLE ipt ADD SEARCH OPTIMIZATION ON FULL_TEXT(
  ip1,
  ANALYZER => 'ENTITY_ANALYZER');
```
Copy
ENTITY_ANALYZER はエンティティのみを認識します（例 IP アドレス）。そのため、検索アクセスパスは通常、別のアナライザーを使用した検索最適化（FULL_TEXT）よりもはるかに小さくなります。

詳細については、 FULL_TEXT 検索最適化の有効化をご参照ください。

例¶

以下の例では、 SEARCH_IP 関数を使用しています。

VARCHAR 列での一致する IP アドレスの検索
VARIANT 列での一致する IP アドレスの検索
予期されるエラーケースの例

VARCHAR 列での一致する IP アドレスの検索¶

以下の例では、 SEARCH_IP 関数を使用して、 VARCHAR の列をクエリする方法を示しています。

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');

Copy

以下の検索クエリを実行します。最初のクエリは、 SELECT リストで SEARCH_IP 関数を使用し、テーブルの2つの VARCHAR 列を検索します。

SELECT ip1,
       ip2,
       SEARCH_IP((ip1, ip2), '192.0.2.146')
  FROM ipt
  ORDER BY ip1;

Copy

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

Copy

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

Copy

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

Copy

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

Copy

+-----+-----+
| IP1 | IP2 |
|-----+-----|
+-----+-----+

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

SELECT ip1,
       ip2
  FROM ipt
  WHERE SEARCH_IP((*), '203.0.113.5')
  ORDER BY ip1;

Copy

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

Copy

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

Copy

注釈

指定する列は VARCHAR または VARIANT 列にする必要があります。他のデータ型の列はサポートされていません。

VARIANT 列での一致する IP アドレスの検索¶

以下の例では、 SEARCH_IP 関数を使用して、 VARIANT の列をクエリする方法を示しています。

この例は、 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" } ');

Copy

以下の検索クエリを実行します。最初のクエリは、 ipv1 フィールドのみを検索します。2番目のクエリは ipv1 と ipv2 を検索します。

SELECT * FROM iptv
  WHERE SEARCH_IP((ip1:"ipv1"), '203.0.113.5');

Copy

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

Copy

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

Copy

注釈

指定する列は VARCHAR または VARIANT 列にする必要があります。他のデータ型の列はサポートされていません。

テキストの長い文字列の中から IP に一致するアドレスを検索します。¶

ipt_log という名前のテーブルを作成し、行を挿入します。

CREATE OR REPLACE TABLE ipt_log(id INT, ip_request_log VARCHAR(200));
INSERT INTO ipt_log VALUES(1, 'Connection from IP address 192.0.2.146 succeeded.');
INSERT INTO ipt_log VALUES(2, 'Connection from IP address 203.0.113.5 failed.');
INSERT INTO ipt_log VALUES(3, 'Connection from IP address 192.0.2.146 dropped.');

Copy

192.0.2.146 IP アドレスを含む ip_request_log 列のログエントリを検索します。

SELECT * FROM ipt_log
  WHERE SEARCH_IP(ip_request_log, '192.0.2.146')
  ORDER BY id;

Copy

+----+---------------------------------------------------+
| ID | IP_REQUEST_LOG                                    |
|----+---------------------------------------------------|
|  1 | Connection from IP address 192.0.2.146 succeeded. |
|  3 | Connection from IP address 192.0.2.146 dropped.   |
+----+---------------------------------------------------+

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

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

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

SELECT SEARCH_IP(ip1, 5) FROM ipt;

Copy

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

search_string 引数は有効な IP アドレスではないため、この例は失敗します。

SELECT SEARCH_IP(ip1, '1925.0.2.146') FROM ipt;

Copy

000937 (22023): SQL compilation error: error line 1 at position 22
invalid argument for function [SEARCH_IP(IPT.IP1, '1925.0.2.146')] unexpected argument [1925.0.2.146] at position 1,

この例は search_string 引数が空文字列であるため失敗します。

SELECT SEARCH_IP(ip1, '') FROM ipt;

Copy

000937 (22023): SQL compilation error: error line 1 at position 22
invalid argument for function [SEARCH_IP(IPT.IP1, '')] unexpected argument [] at position 1,

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

SELECT SEARCH_IP(id, '192.0.2.146') FROM ipt;

Copy

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;

Copy

+-------------------------------------+
| SEARCH_IP((ID, IP1), '192.0.2.146') |
|-------------------------------------|
| True                                |
| False                               |
+-------------------------------------+