SEARCH_IP¶
Searches for valid IPv4 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 this IP address matches an IP address in the specified column or field, the function returns TRUE.
For more information about using this function, see Using full-text search.
Syntax¶
SEARCH_IP( <search_data>, <search_string> )
Arguments¶
search_data
The data you want to search, expressed as a comma-delimited list of string literals, column names, or paths to fields in VARIANT columns. The search data can also be a single literal string, which can be useful when you are testing the function.
You can specify the wildcard character (
*
), where*
expands to all qualifying columns in all of the tables that are in scope for the function. Qualifying columns are those that have VARCHAR (text), VARIANT, ARRAY, and OBJECT data types. VARIANT, ARRAY, and OBJECT data is converted to text for searching. You can also use the ILIKE and EXCLUDE keywords for filtering.For more information about this argument, see the
search_data
description for the SEARCH function.search_string
A VARCHAR string that contains one of the following:
A complete and valid IP address in standard IPv4 format, such as
192.0.2.1
.A valid IP address in standard IPv4 format with a Classless Inter-Domain Routing (CIDR) range, such as
192.0.2.1/24
.A valid IP address in standard IPv4 format with leading zeros, such as
192.000.002.001
(instead of192.0.2.1
). The function accepts up to three digits for each part of the IP address.
This argument must be a literal string; column names are not supported. Specify one pair of single quotes around the string.
You can’t specify more than one IP address. You can’t specify an IPv6 address or a partial IPv4 address.
Returns¶
Returns a BOOLEAN.
Returns TRUE if a valid IP address is specified in
search_string
and a matching IP address is found insearch_data
.Returns TRUE if a valid IP address with a CIDR range is specified in
search_string
and an IP address in the specified range is found insearch_data
.Returns TRUE if
search_string
is empty and no valid IP addresses are found insearch_data
.Returns NULL if either of these arguments is NULL.
Otherwise, returns FALSE.
Usage notes¶
The SEARCH_IP function operates only on VARCHAR, VARIANT, ARRAY, and OBJECT data. The function returns an error if the
search_data
argument doesn’t contain data of these data types. When thesearch_data
argument includes data of both supported data types and unsupported data types, the function searches the data of the supported data types and silently ignores the data of the unsupported data types. For examples, see Examples of expected error cases.The SEARCH_IP function operates only on VARCHAR, VARIANT, ARRAY, and OBJECT data. The function returns an error if you try to search other data types. For examples, see Examples of expected error cases.
You can add a FULL_TEXT search optimization on columns that are the target of SEARCH_IP function calls by using an ALTER TABLE command that specifies the ENTITY_ANALYZER. For example:
ALTER TABLE ipt ADD SEARCH OPTIMIZATION ON FULL_TEXT( ip1, ANALYZER => 'ENTITY_ANALYZER');
For more information, see enable FULL_TEXT search optimization.
Examples¶
The following examples show how to use the SEARCH_IP function to query VARCHAR and VARIANT columns.
Search for matching IP addresses in VARCHAR columns¶
Create a table named ipt
and insert two rows:
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');
Run the following search queries. The first query uses the SEARCH_IP function in the SELECT list and searches the two VARCHAR columns in the table:
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 |
+-------------+-----------------+--------------------------------------+
Notice that search_data
192.000.002.146
is a match for search_string
192.0.2.146
even though 192.000.002.146
has leading zeros.
This query shows a search_string
with a CIDR range:
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 |
+-------------+-----------------+---------------------------------------+
This query shows a search_string
with leading zeros returns True
for
IP addresses that omit the leading zeros:
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 |
+-------------+-----------------+------------------------------------------+
This query uses the function in the WHERE clause and searches the ip2
column only.
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 |
+-------------+-------------+
When the function is used in the WHERE clause and there is no match, no values are returned.
SELECT ip1,
ip2
FROM ipt
WHERE SEARCH_IP(ip2, '203.0.113.1')
ORDER BY ip1;
+-----+-----+
| IP1 | IP2 |
|-----+-----|
+-----+-----+
You can use the *
character (or table.*
) as the first argument to the SEARCH function, as shown in this example.
The search operates on all of the qualifying columns in the table you are selecting from.
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 |
+-------------+-------------+
You can also use the ILIKE and EXCLUDE keywords for filtering. For more information about these keywords, see SELECT.
This search uses the ILIKE keyword to search only in columns that start with the string 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 |
+-------------+-----------------+
To enable FULL_TEXT search optimization for the columns in the
ipt
table, run the following ALTER TABLE command:
ALTER TABLE ipt ADD SEARCH OPTIMIZATION ON FULL_TEXT(
ip1,
ip2,
ANALYZER => 'ENTITY_ANALYZER');
Note
The columns you specify must be VARCHAR or VARIANT columns. Columns with other data types are not supported.
Search for matching IP addresses in a VARIANT column¶
This example uses the SEARCH_IP function to search a path to a field in a VARIANT column. Create a table
named iptv
and insert two rows:
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" } ');
Run the following search queries. The first query searches the ipv1
field only. The
second searches ipv1
and 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" |
| } |
+--------------------------+
To enable FULL_TEXT search optimization for this ip1
VARIANT
column and its fields, run the following ALTER TABLE command:
ALTER TABLE iptv ADD SEARCH OPTIMIZATION ON FULL_TEXT(
ip1:"ipv1",
ip1:"ipv2",
ANALYZER => 'ENTITY_ANALYZER');
Note
The columns you specify must be VARCHAR or VARIANT columns. Columns with other data types are not supported.
Examples of expected error cases¶
The following examples show queries that return expected syntax errors.
This example fails because 5
is not a supported data type for the search_string
argument.
SELECT SEARCH_IP(ip1, 5) FROM ipt;
001045 (22023): SQL compilation error:
argument needs to be a string: '1'
This example fails because there is no column of a supported data type specified for the search_data
argument.
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.
This example succeeds because there is a column of a supported data type specified for the search_data
argument. The function
ignores the id
column because it is not a supported data type
SELECT SEARCH_IP((id, ip1), '192.0.2.146') FROM ipt;
+-------------------------------------+
| SEARCH_IP((ID, IP1), '192.0.2.146') |
|-------------------------------------|
| True |
| False |
+-------------------------------------+