Categories:: 文字列とバイナリ関数 (Full-Text Search)

SEARCH_IP¶

Searches for valid IPv4 and IPv6 addresses in specified character-string columns from one or more tables, including fields in VARIANT, OBJECT, and ARRAY columns. The search is based on a single IP address or a range of IP addresses that you specify. If an IP address in the column or field matches a specified IP address or is in a specified range, then the function returns 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 文字列:

完全かつ有効なIPアドレスを標準的な IPv4 または IPv6 形式で記載します。例：192.0.2.1``または``2001:0db8:85a3:0000:0000:8a2e:0370:7334。
標準的なIPv4またはIPv6 形式の有効な IP アドレスで、クラスレスドメイン間ルーティングの（CIDR）範囲を持つもの。たとえば、192.0.2.1/24 または 2001:db8:85a3::/64。
有効な IP アドレスは、先頭のゼロが標準の IPv4 または IPv6 形式で、たとえば``192.000.002.001``ではなく``192.0.2.1``、``2001:0db8:85a3:0333:4444:8a2e:0370:7334``ではなく``2001:db8:85a3:333:4444:8a2e:370:7334``です。この関数は、IPv4 アドレスの各部分に最大3桁、IPv6 アドレスの各部分に最大4桁の数字を受け入れます。
有効な圧縮済み IPv6 アドレス、たとえば 2001:db8:85a3:0:0:0:0:0 または 2001:db8:85a3:: の代わりに 2001:db8:85a3:0000:0000:0000:0000:0000。
また IPv6 を組み合わせるデュアルアドレスは、たとえば ``2001:db8:85a3::192.0.2.1``のように、IPv6 および IPv4 などのアドレスを組み合わせたものです。

この引数はリテラル文字列にする必要があります。文字列を囲む一重引用符の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(
  ipv4_source,
  ANALYZER => 'ENTITY_ANALYZER');
```
ENTITY_ANALYZER はエンティティのみを認識します（例 IP アドレス）。そのため、検索アクセスパスは通常、別のアナライザーを使用した検索最適化（FULL_TEXT）よりもはるかに小さくなります。

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

例¶

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

VARCHAR 列での一致する IP アドレスの検索
VARIANT 列での一致する IP アドレスの検索
テキストの長い文字列の中から IP に一致するアドレスを検索します。
予期されるエラーケースの例

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

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

まず、ipt という名前のテーブルを作成します。このテーブルには、IPv4 アドレスを格納する2つの列と IPv6 アドレスを格納する1つの列があります：

CREATE OR REPLACE TABLE ipt(
  id INT,
  ipv4_source VARCHAR(20),
  ipv4_target VARCHAR(20),
  ipv6_target VARCHAR(40));

テーブルに2つの行を挿入します。

INSERT INTO ipt VALUES(
  1,
  '192.0.2.146',
  '203.0.113.5',
  '2001:0db8:85a3:0000:0000:8a2e:0370:7334');

INSERT INTO ipt VALUES(
  2,
  '192.0.2.111',
  '192.000.002.146',
  '2001:db8:1234::5678');

テーブルをクエリします。

SELECT * FROM ipt;

+----+-------------+-----------------+-----------------------------------------+
| ID | IPV4_SOURCE | IPV4_TARGET     | IPV6_TARGET                             |
|----+-------------+-----------------+-----------------------------------------|
|  1 | 192.0.2.146 | 203.0.113.5     | 2001:0db8:85a3:0000:0000:8a2e:0370:7334 |
|  2 | 192.0.2.111 | 192.000.002.146 | 2001:db8:1234::5678                     |
+----+-------------+-----------------+-----------------------------------------+

次のセクションでは、SEARCH_IP このテーブルデータの関数を使用するクエリを実行します。

関数を使用してSELECTリスト内で一致するIP アドレスを検索します
WHEREの関数を使用し、一致するものを検索 IP アドレスを検索します
FULL_TEXT 列で VARCHAR 検索最適化を有効にする

関数を使用してSELECTリスト内で一致するIP アドレスを検索します¶

SEARCH_IP 関数を使用し、 SELECT リスト内の3つの VARCHAR 列をを検索するクエリを実行します。

SELECT ipv4_source,
       ipv4_target,
       ipv6_target,
       SEARCH_IP((ipv4_source, ipv4_target, ipv6_target), '192.0.2.146') AS "Match found?"
  FROM ipt
  ORDER BY ipv4_source;

+-------------+-----------------+-----------------------------------------+--------------+
| IPV4_SOURCE | IPV4_TARGET     | IPV6_TARGET                             | Match found? |
|-------------+-----------------+-----------------------------------------+--------------|
| 192.0.2.111 | 192.000.002.146 | 2001:db8:1234::5678                     | True         |
| 192.0.2.146 | 203.0.113.5     | 2001:0db8:85a3:0000:0000:8a2e:0370:7334 | True         |
+-------------+-----------------+-----------------------------------------+--------------+

192.000.002.146 には先頭のゼロがありますが、 search_data 192.000.002.146 が search_string 192.0.2.146 と一致することに注意してください。

`2001:0db8:85a3:0000:0000:8a2e:0370:7334 `に一致する IPv6 アドレスを検索するクエリを実行します：

SELECT ipv4_source,
       ipv4_target,
       ipv6_target,
       SEARCH_IP((ipv6_target), '2001:0db8:85a3:0000:0000:8a2e:0370:7334') AS "Match found?"
  FROM ipt
  ORDER BY ipv4_source;

+-------------+-----------------+-----------------------------------------+--------------+
| IPV4_SOURCE | IPV4_TARGET     | IPV6_TARGET                             | Match found? |
|-------------+-----------------+-----------------------------------------+--------------|
| 192.0.2.111 | 192.000.002.146 | 2001:db8:1234::5678                     | False        |
| 192.0.2.146 | 203.0.113.5     | 2001:0db8:85a3:0000:0000:8a2e:0370:7334 | True         |
+-------------+-----------------+-----------------------------------------+--------------+

次のクエリは前のクエリと同じですが、:samp:`{search_string}`の先頭のゼロとゼロセグメントが除外されています :

SELECT ipv4_source,
       ipv4_target,
       ipv6_target,
       SEARCH_IP((ipv6_target), '2001:db8:85a3::8a2e:370:7334') AS "Match found?"
  FROM ipt
  ORDER BY ipv4_source;

+-------------+-----------------+-----------------------------------------+--------------+
| IPV4_SOURCE | IPV4_TARGET     | IPV6_TARGET                             | Match found? |
|-------------+-----------------+-----------------------------------------+--------------|
| 192.0.2.111 | 192.000.002.146 | 2001:db8:1234::5678                     | False        |
| 192.0.2.146 | 203.0.113.5     | 2001:0db8:85a3:0000:0000:8a2e:0370:7334 | True         |
+-------------+-----------------+-----------------------------------------+--------------+

次のクエリは search_string の CIDR 範囲の IPv4 アドレスを示しています：

SELECT ipv4_source,
       ipv4_target,
       SEARCH_IP((ipv4_source, ipv4_target), '192.0.2.1/20') AS "Match found?"
  FROM ipt
  ORDER BY ipv4_source;

+-------------+-----------------+--------------+
| IPV4_SOURCE | IPV4_TARGET     | Match found? |
|-------------+-----------------+--------------|
| 192.0.2.111 | 192.000.002.146 | True         |
| 192.0.2.146 | 203.0.113.5     | True         |
+-------------+-----------------+--------------+

このクエリは、先頭のゼロがあるアドレス search_string が先頭のゼロが省略されたアドレスIPv4に対して ``True``を返すことを示しています。

SELECT ipv4_source,
       ipv4_target,
       SEARCH_IP((ipv4_source, ipv4_target), '203.000.113.005') AS "Match found?"
  FROM ipt
  ORDER BY ipv4_source;

+-------------+-----------------+--------------+
| IPV4_SOURCE | IPV4_TARGET     | Match found? |
|-------------+-----------------+--------------|
| 192.0.2.111 | 192.000.002.146 | False        |
| 192.0.2.146 | 203.0.113.5     | True         |
+-------------+-----------------+--------------+

WHEREの関数を使用し、一致するものを検索 IP アドレスを検索します¶

このクエリは、WHERE 句内で関数を使用し、 ipv4_target 列のみを検索します。

SELECT ipv4_source,
       ipv4_target,
       ipv6_target
  FROM ipt
  WHERE SEARCH_IP(ipv4_target, '203.0.113.5')
  ORDER BY ipv4_source;

+-------------+-------------+-----------------------------------------+
| IPV4_SOURCE | IPV4_TARGET | IPV6_TARGET                             |
|-------------+-------------+-----------------------------------------|
| 192.0.2.146 | 203.0.113.5 | 2001:0db8:85a3:0000:0000:8a2e:0370:7334 |
+-------------+-------------+-----------------------------------------+

この関数が WHERE 句で使用され、一致するものがなかった場合、値を返しません。

SELECT ipv4_source,
       ipv4_target,
       ipv6_target
  FROM ipt
  WHERE SEARCH_IP(ipv4_target, '203.0.113.1')
  ORDER BY ipv4_source;

+-------------+-------------+-------------+
| IPV4_SOURCE | IPV4_TARGET | IPV6_TARGET |
|-------------+-------------+-------------|
+-------------+-------------+-------------+

このクエリは、WHERE 句内で関数を使用し、 ipv6_target 列のみを検索します。

SELECT ipv4_source,
       ipv4_target,
       ipv6_target
  FROM ipt
  WHERE SEARCH_IP(ipv6_target, '2001:db8:1234::5678')
  ORDER BY ipv4_source;

+-------------+-----------------+---------------------+
| IPV4_SOURCE | IPV4_TARGET     | IPV6_TARGET         |
|-------------+-----------------+---------------------|
| 192.0.2.111 | 192.000.002.146 | 2001:db8:1234::5678 |
+-------------+-----------------+---------------------+

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

SELECT ipv4_source,
       ipv4_target,
       ipv6_target
  FROM ipt
  WHERE SEARCH_IP((*), '192.0.2.146')
  ORDER BY ipv4_source;

+-------------+-----------------+-----------------------------------------+
| IPV4_SOURCE | IPV4_TARGET     | IPV6_TARGET                             |
|-------------+-----------------+-----------------------------------------|
| 192.0.2.111 | 192.000.002.146 | 2001:db8:1234::5678                     |
| 192.0.2.146 | 203.0.113.5     | 2001:0db8:85a3:0000:0000:8a2e:0370:7334 |
+-------------+-----------------+-----------------------------------------+

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

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

SELECT ipv4_source,
       ipv4_target,
       ipv6_target
  FROM ipt
  WHERE SEARCH_IP(* ILIKE '%_target', '192.0.2.146')
  ORDER BY ipv4_source;

+-------------+-----------------+---------------------+
| IPV4_SOURCE | IPV4_TARGET     | IPV6_TARGET         |
|-------------+-----------------+---------------------|
| 192.0.2.111 | 192.000.002.146 | 2001:db8:1234::5678 |
+-------------+-----------------+---------------------+

FULL_TEXT 列で VARCHAR 検索最適化を有効にする¶

ipt テーブル列の FULL_TEXT 検索最適化を有効にするには、以下の ALTER TABLE コマンドを実行します。

ALTER TABLE ipt ADD SEARCH OPTIMIZATION ON FULL_TEXT(
  ipv4_source,
  ipv4_target,
  ipv6_target,
  ANALYZER => 'ENTITY_ANALYZER');

注釈

指定する列は 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" } ');

以下の検索クエリを実行します。最初のクエリは、 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 列にする必要があります。他のデータタイプの列はサポートされていません。

テキストの長い文字列の中から 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.');

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;

+----+---------------------------------------------------+
| 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(ipv4_source, 5) FROM ipt;

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

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

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

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

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

SELECT SEARCH_IP(ipv4_source, '') FROM ipt;

000937 (22023): SQL compilation error: error line 1 at position 30
invalid argument for function [SEARCH_IP(IPT.IPV4_SOURCE, '')] unexpected argument [] at position 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, ipv4_source), '192.0.2.146') FROM ipt;

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