Categories:

문자열 및 이진 함수 (Full-Text Search)

SEARCH_IP

하나 이상의 테이블에서 지정된 문자 문자열 열(VARIANT, OBJECT 및 ARRAY 열의 필드 포함)의 유효한 IPv4 및 IPv6 주소를 검색합니다. 검색은 단일 IP 주소 또는 사용자가 지정하는 IP 주소 범위를 기준으로 합니다. 열 또는 필드의 IP 주소가 지정된 IP 주소와 일치하거나 지정된 범위에 있는 경우 이 함수는 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 문자열입니다.

  • 표준 IPv4 또는 IPv6 형식의 완전하고 유효한 IP 주소(예: 192.0.2.1 또는 2001:0db8:85a3:0000:0000:8a2e:0370:7334)

  • CIDR(Classless Inter-Domain Routing) 범위(예: 192.0.2.1/24 또는 2001:db8:85a3::/64)를 갖는 표준 IPv4 또는 IPv6 형식의 유효한 IP 주소

  • 선행 0이 있는 표준 IPv4 또는 IPv6 형식의 유효한 IP 주소(예: 192.0.2.1 대신 192.000.002.001, 2001:db8:85a3:333:4444:8a2e:370:7334 대신 2001:0db8:85a3:0333:4444:8a2e:0370:7334). 이 함수는 IPv4 주소의 각 부분에 대해 최대 3자리 숫자를 허용하고, IPv6 주소의 각 부분에 대해 최대 4자리 숫자를 허용합니다.

  • 유효한 압축 IPv6 주소(예: 2001:db8:85a3:0000:0000:0000:0000:0000 대신 2001:db8:85a3:0:0:0:0:0 또는 2001:db8:85a3::).

  • IPv6 및 IPv4 주소를 결합하는 IPv6 이중 주소(예: 2001:db8:85a3::192.0.2.1).

이 인자는 리터럴 문자열이어야 합니다. 문자열을 작은따옴표 한 쌍으로 묶어 지정합니다.

다음 유형의 인자는 지원되지 않습니다.

  • 열 이름

  • 빈 문자열

  • 2개 이상의 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(text), 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(text) 열을 쿼리하는 방법을 보여줍니다.

먼저, IPv4 주소를 저장하는 두 개의 열과 IPv6 주소를 저장하는 1개의 열이 있는 ipt 라는 테이블을 만듭니다.

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

테이블에 두 행을 삽입합니다.

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 주소를 검색합니다.

SELECT 목록의 SEARCH_IP 함수를 사용하고 테이블에 있는 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 앞에 0이 오지만 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         |
+-------------+-----------------+-----------------------------------------+--------------+

다음 쿼리는 이전 쿼리와 동일하지만, search_string 에서 선행 0과 0 세그먼트를 제외합니다.

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

다음 쿼리는 IPv4 주소에 대해 CIDR 범위가 있는 search_string 이 다음과 같음을 보여줍니다.

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

다음 쿼리는 앞에 0이 오는 search_string 이 앞에 0이 생략된 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 함수의 첫 번째 인자로 사용할 수 있습니다. 검색은 선택하는 테이블의 모든 적격 열에서 수행됩니다.

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

VARCHAR 열에서 FULL_TEXT 검색 최적화 활성화

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 인 테이블을 만들고 두 행을 삽입합니다.

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 필드만 검색합니다. 두 번째 쿼리는 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.');

ip_request_log 열에서 192.0.2.146 IP 주소가 포함된 로그 항목을 검색합니다.

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