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.

Este argumento deve ser uma cadeia de caracteres literal; nomes de colunas não são compatíveis. Especifique um par de aspas simples ao redor da cadeia de caracteres.

Não é possível especificar mais de um endereço IP. Não é possível especificar um endereço IPv6 ou um endereço IPv4 parcial.

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 TRUE se search_string estiver vazio e nenhum endereço IP válido 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 SEARCH_IP opera somente em dados VARCHAR, VARIANT, ARRAY e OBJECT. A função retorna um erro se você tentar pesquisar outros tipos de dados. 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

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

Exemplos

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

Pesquisa de endereços IP correspondentes em 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

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.

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'

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