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 アドレスの検索¶
以下の例では、 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 アドレスを検索します¶
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 |
+---------------------------------------------+