SEARCH_IP

Pesquisa endereços IPv4 válidos em colunas de cadeia de caracteres especificadas de uma ou mais tabelas, incluindo campos nas colunas VARIANT, OBJECT e ARRAY. A pesquisa é baseada em um único endereço IP ou em um intervalo de endereços IP que você especifica. Se este endereço IP corresponder a um endereço IP na coluna ou campo especificado, a função retornará TRUE.

Para obter mais informações sobre como usar esta função, consulte Uso da pesquisa de texto completo.

Sintaxe

SEARCH_IP( <search_data>, <search_string> )
Copy

Argumentos

search_data

Os dados que você deseja pesquisar, expressos como uma lista delimitada por vírgulas de literais de cadeia de caracteres, nomes de colunas ou caminhos para campos em colunas VARIANT. Os dados de pesquisa também podem ser uma única cadeia de caracteres literal, o que pode ser útil quando você estiver testando a função.

É possível especificar o caractere curinga (*), onde * se expande para todas as colunas qualificadas em todas as tabelas que estiverem no escopo da função. Colunas qualificadas são aquelas que possuem os tipos de dados VARCHAR (texto), VARIANT, ARRAY e OBJECT. Os dados VARIANT, ARRAY e OBJECT são convertidos em texto para pesquisa. Você também pode usar as palavras-chave ILIKE e EXCLUDE para filtrar.

Para mais informações sobre este argumento, consulte a descrição search_data da função SEARCH.

search_string

Uma cadeia de caracteres VARCHAR com um dos seguintes:

  • Um endereço IP completo e válido no formato IPv4 padrão, como 192.0.2.1.

  • Um endereço IP válido no formato IPv4 padrão com um intervalo de roteamento entre domínios sem classe (CIDR), como 192.0.2.1/24.

  • Um endereço IP válido no formato IPv4 padrão com zeros à esquerda, como 192.000.002.001 (em vez de 192.0.2.1). A função aceita até três dígitos para cada parte do endereço IP.

Esse argumento deve ser uma cadeia de caracteres literal. Especifique um par de aspas simples ao redor da cadeia de caracteres.

Os seguintes tipos de argumentos não são compatíveis:

  • Nomes de coluna

  • Cadeias de caracteres vazias

  • Mais de um endereço IP

  • Endereços parciais de IPv4

  • Endereços IPv6

Retornos

Retorna um BOOLEAN.

  • Retorna TRUE se um endereço IP válido for especificado em search_string e um endereço IP correspondente for encontrado em search_data.

  • Retorna TRUE se um endereço IP válido com um intervalo CIDR for especificado em search_string e um endereço IP no intervalo especificado for encontrado em search_data.

  • Retorna NULL se qualquer um desses argumentos for NULL.

  • Caso contrário, retorna FALSE.

Notas de uso

  • A função SEARCH_IP opera somente em dados VARCHAR, VARIANT, ARRAY e OBJECT. A função retorna um erro se o argumento search_data não contiver dados desses tipos de dados. Quando o argumento search_data inclui dados de tipos de dados compatíveis e incompatíveis, a função pesquisa os dados dos tipos de dados compatíveis e ignora silenciosamente os dados dos tipos de dados incompatíveis. Para exemplos, consulte Exemplos de casos de erro esperados.

  • A função retornará um erro se o argumento search_string não for um endereço IP válido. Para exemplos, consulte Exemplos de casos de erro esperados.

  • É possível adicionar uma otimização de pesquisa FULL_TEXT em colunas que são o destino de chamadas de função SEARCH_IP usando um comando ALTER TABLE que especifique ENTITY_ANALYZER. Por exemplo:

    ALTER TABLE ipt ADD SEARCH OPTIMIZATION ON FULL_TEXT(
  ip1,
  ANALYZER => 'ENTITY_ANALYZER');
    Copy

    O ENTITY_ANALYZER reconhece apenas as entidades (por exemplo, endereços IP). Portanto, o caminho de acesso à pesquisa é normalmente muito menor do que a otimização de pesquisa FULL_TEXT com um analisador diferente.

    Para obter mais informações, consulte Como habilitar a otimização de pesquisa FULL_TEXT.

Exemplos

Os exemplos a seguir usam a função SEARCH_IP:

Pesquisa de endereços IP correspondentes em colunas VARCHAR

Os exemplos a seguir mostram como usar a função SEARCH_IP para consultar as colunas VARCHAR.

Crie uma tabela nomeada ipt e insira duas linhas:

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

Execute as seguintes consultas de pesquisa. A primeira consulta usa a função SEARCH_IP na lista SELECT e pesquisa as duas colunas VARCHAR na tabela:

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

Observe que search_data 192.000.002.146 é uma correspondência para search_string 192.0.2.146, mesmo que 192.000.002.146 tenha zeros à esquerda.

Esta consulta mostra uma search_string com um intervalo de CIDR:

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

Esta consulta mostra que uma search_string com zeros à esquerda retorna True par endereços IP que omitem os zeros à esquerda:

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

Esta consulta usa a função na cláusula WHERE e pesquisa somente a coluna ip2.

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

Quando a função é usada na cláusula WHERE e não há correspondência, nenhum valor é retornado.

SELECT ip1,
       ip2
  FROM ipt
  WHERE SEARCH_IP(ip2, '203.0.113.1')
  ORDER BY ip1;
Copy
+-----+-----+
| IP1 | IP2 |
|-----+-----|
+-----+-----+

É possível usar o caractere * (ou table.*) como o primeiro argumento para a função SEARCH, conforme mostrado neste exemplo. A pesquisa opera em todas as colunas qualificadas na tabela que você está selecionando.

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

Você também pode usar as palavras-chave ILIKE e EXCLUDE para filtrar. Para obter mais informações sobre essas palavras-chave, consulte SELECT.

Esta pesquisa usa a palavra-chave ILIKE para pesquisar apenas em colunas que começam com a cadeia de caracteres 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 |
+-------------+-----------------+

Para habilitar a otimização de pesquisa FULL_TEXT para as colunas na tabela ipt, execute o seguinte comando ALTER TABLE:

ALTER TABLE ipt ADD SEARCH OPTIMIZATION ON FULL_TEXT(
  ip1,
  ip2,
  ANALYZER => 'ENTITY_ANALYZER');
Copy

Nota

As colunas que você especificar devem ser colunas.VARCHAR ou VARIANT. Colunas com outros tipos de dados não são suportadas.

Pesquisa de endereços IP correspondentes em uma coluna VARIANT

Os exemplos a seguir mostram como usar a função SEARCH_IP para consultar as colunas VARIANT.

Este exemplo usa a função SEARCH_IP para pesquisar um caminho para um campo em uma coluna VARIANT. Crie uma tabela nomeada iptv e insira duas linhas:

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

Execute as seguintes consultas de pesquisa. A primeira consulta pesquisa apenas o campo ipv1. A segunda pesquisa ipv1 e 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"  |
| }                        |
+--------------------------+

Para habilitar a otimização de pesquisa FULL_TEXT para esta coluna ip1 VARIANT e seus campos, execute o seguinte comando ALTER TABLE:

ALTER TABLE iptv ADD SEARCH OPTIMIZATION ON FULL_TEXT(
  ip1:"ipv1",
  ip1:"ipv2",
  ANALYZER => 'ENTITY_ANALYZER');
Copy

Nota

As colunas que você especificar devem ser colunas.VARCHAR ou VARIANT. Colunas com outros tipos de dados não são suportadas.

Procure por endereços IP correspondentes em longas cadeias de caracteres de texto

Crie uma tabela nomeada ipt_log e insira linhas:

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

Procure entradas de registro na coluna ip_request_log que incluam o endereço IP 192.0.2.146:

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

Exemplos de casos de erro esperados

Os exemplos a seguir mostram consultas que retornam erros de sintaxe esperados.

Este exemplo falha porque 5 não é um tipo de dados compatível para o argumento search_string.

SELECT SEARCH_IP(ip1, 5) FROM ipt;
Copy
001045 (22023): SQL compilation error:
argument needs to be a string: '1'

Esse exemplo falha porque o argumento search_string não é um endereço IP válido.

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,

Esse exemplo falha porque o argumento search_string é uma cadeia de caracteres vazia.

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,

Este exemplo falha porque não há nenhuma coluna de um tipo de dados compatível especificado para o argumento search_data.

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.

Este exemplo é bem-sucedido porque há uma coluna de um tipo de dados compatível especificado para o argumento search_data. A função ignora a coluna id porque não é um tipo de dados compatível.

SELECT SEARCH_IP((id, ip1), '192.0.2.146') FROM ipt;
Copy
+-------------------------------------+
| SEARCH_IP((ID, IP1), '192.0.2.146') |
|-------------------------------------|
| True                                |
| False                               |
+-------------------------------------+
Linguagem: Português