SEARCH_IP

SEARCH_IP 함수는 하나 이상의 테이블에서 지정된 문자열 열에서 유효한 IPv4 및 IPv6 주소를 검색하며, VARIANT, OBJECT, ARRAY 열의 필드도 포함해요. 검색은 지정한 단일 IP 주소 또는 IP 주소 범위를 기준으로 해요. 열이나 필드의 IP 주소가 지정한 IP 주소와 일치하거나 지정된 범위 안에 있으면 함수는 TRUE를 반환해요.

이 함수 사용에 대한 자세한 내용은 전체 텍스트 검색 사용(Using full-text search)을 참조하세요.

출처: 문서

본문

문법 (Syntax)

SEARCH_IP( <search_data>, '<search_string>' )

인자 (Arguments)

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) 범위가 있는 표준 IPv4 또는 IPv6 형식의 유효한 IP 주소 (예: 192.0.2.1/24 또는 2001:db8:85a3::/64).
- 앞에 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 주소의 각 부분에 대해 최대 세 자리, IPv6 주소의 각 부분에 대해 최대 네 자리를 받아들여요.
- 유효한 압축 IPv6 주소 (예: 2001:db8:85a3:0000:0000:0000:0000:0000 또는 2001:db8:85a3:: 대신 2001:db8:85a3:0:0:0:0:0).
- IPv6와 IPv4 주소를 결합한 IPv6 이중 주소 (예: 2001:db8:85a3::192.0.2.1).
- 이 인자는 리터럴 문자열이어야 해요. 문자열 주위에 작은따옴표 한 쌍을 지정하세요.
- 지원되지 않는 인자 유형: 열 이름, 빈 문자열, 둘 이상의 IP 주소, 부분 IPv4 및 IPv6 주소.

반환 (Returns)

BOOLEAN을 반환해요:

search_string에 유효한 IP 주소가 지정되고 search_data에서 일치하는 IP 주소가 발견되면 TRUE를 반환해요.
search_string에 CIDR 범위가 있는 유효한 IP 주소가 지정되고 지정된 범위 안의 IP 주소가 search_data에서 발견되면 TRUE를 반환해요.
두 인자 중 하나라도 NULL이면 NULL을 반환해요.
그 외에는 FALSE를 반환해요.

사용 메모 (Usage notes)

SEARCH_IP 함수는 VARCHAR(텍스트), VARIANT, ARRAY, OBJECT 데이터에 대해서만 동작해요. search_data 인자가 이런 데이터 타입의 데이터를 포함하지 않으면 함수는 오류를 반환해요. search_data 인자에 지원되는 데이터 타입과 지원되지 않는 데이터 타입의 데이터가 모두 포함되면 함수는 지원되는 데이터 타입의 데이터를 검색하고 지원되지 않는 데이터 타입의 데이터는 조용히 무시해요. 예제는 예상 오류 사례 예제(Examples of expected error cases)를 참조하세요.
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 검색 최적화 활성화를 참조하세요.

예제 (Examples)

다음 예제들은 SEARCH_IP 함수를 사용해요:

VARCHAR 열에서 일치하는 IP 주소 검색
VARIANT 열에서 일치하는 IP 주소 검색
긴 텍스트 문자열에서 일치하는 IP 주소 검색
예상 오류 사례 예제

VARCHAR 열에서 일치하는 IP 주소 검색

다음 예제들은 SEARCH_IP 함수를 사용해 VARCHAR(텍스트) 열을 조회하는 방법을 보여줘요.

먼저 IPv4 주소를 저장하는 두 열과 IPv6 주소를 저장하는 한 열이 있는 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 주소 검색
WHERE 절에서 함수를 사용해 일치하는 IP 주소 검색
VARCHAR 열에 FULL_TEXT 검색 최적화 활성화

SELECT 목록에서 함수를 사용해 일치하는 IP 주소 검색

SELECT 목록에서 SEARCH_IP 함수를 사용하고 테이블의 세 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 |
+-------------+-----------------+---------------------+

다음 예제처럼 SEARCH 함수의 첫 번째 인자로 * 문자(또는 table.*)를 사용할 수 있어요. 검색은 선택하는 테이블의 모든 자격이 되는 열에 대해 수행돼요:

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 열을 조회하는 방법을 보여줘요.

다음 예제는 VARIANT 열의 필드 경로를 검색하기 위해 SEARCH_IP 함수를 사용해요. 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.   |
+----+---------------------------------------------------+

예상 오류 사례 예제 (Examples of expected error cases)

다음 예제들은 예상되는 구문 오류를 반환하는 쿼리를 보여줘요.

다음 예제는 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                                       |
+---------------------------------------------+

더 알아보기 (Learn more)

PARSE_IP