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> )
Copy

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 of 192.0.2.1). The function accepts up to three digits for each part of the IP address.

This argument must be a literal string. Specify one pair of single quotes around the string.

The following types of arguments aren’t supported:

  • Column names

  • Empty strings

  • More than one IP address

  • Partial IPv4 addresses

  • IPv6 addresses

Returns¶

Returns a BOOLEAN.

  • Returns TRUE if a valid IP address is specified in search_string and a matching IP address is found in search_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 in search_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 the search_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 function returns an error if the search_string argument isn’t a valid IP address. 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');
    Copy

    The ENTITY_ANALYZER recognizes only the entities (for example, IP addresses). Therefore, the search access path is typically much smaller than FULL_TEXT search optimization with a different analyzer.

    For more information, see enable FULL_TEXT search optimization.

Examples¶

The following examples use the SEARCH_IP function:

Search for matching IP addresses in VARCHAR columns¶

The following examples show how to use the SEARCH_IP function to query 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');
Copy

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

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

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

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

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¶

The following examples show how to use the SEARCH_IP function to query VARIANT columns.

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" } ');
Copy

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

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

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 long strings of text¶

Create a table named ipt_log and insert rows:

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

Search for log entries in the ip_request_log column that include the 192.0.2.146 IP address:

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

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;
Copy
001045 (22023): SQL compilation error:
argument needs to be a string: '1'

This example fails because the search_string argument isn’t a valid IP address.

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,

This example fails because the search_string argument is an empty 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,

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;
Copy
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;
Copy
+-------------------------------------+
| SEARCH_IP((ID, IP1), '192.0.2.146') |
|-------------------------------------|
| True                                |
| False                               |
+-------------------------------------+
Language: English