Categories:

Funções de cadeia de caracteres e binários (Full-Text Search)

SEARCH_IP

Pesquisa endereços IPv4 e IPv6 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 um endereço IP na coluna ou campo corresponde a um endereço IP especificado ou que está em um intervalo especificado, então a função retorna 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>' )

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 string VARCHAR que contém um dos seguintes endereços:

  • Um endereço IP completo e válido no formato IPv4 ou IPv6 padrão, como 192.0.2.1 ou 2001:0db8:85a3:0000:0000:8a2e:0370:7334.

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

  • Um endereço IP válido no formato IPv4 ou IPv6 com zeros à esquerda, como 192.000.002.001 em vez de 192.0.2.1 ou 2001:0db8:85a3:0333:4444:8a2e:0370:7334 em vez de 2001:db8:85a3:333:4444:8a2e:370:7334. A função aceita até três dígitos para cada parte de um endereço IPv4, e até quatro dígitos para cada parte de um endereço IPv6.

  • Um endereço IPv6 compactado válido, como 2001:db8:85a3:0:0:0:0:0 ou 2001:db8:85a3:: em vez de 2001:db8:85a3:0000:0000:0000:0000:0000.

  • Um endereço IPv6 duplo que combina um endereço IPv6 e um IPv4, como 2001:db8:85a3::192.0.2.1.

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 IPv4 e IPv6 parciais

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 (texto), 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(
  ipv4_source,
  ANALYZER => 'ENTITY_ANALYZER');

    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 (texto).

Primeiro, crie uma tabela chamada ipt que tem duas colunas que armazenam endereços IPv4 e uma coluna que armazena endereços IPv6:

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

Insira duas linhas na tabela:

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

Consultar a tabela:

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

As seções a seguir executam consultas que usam a função SEARCH_IP sobre estes dados de tabela:

Pesquisa por endereços IP correspondentes usando a função em uma lista SELECT

Execute uma consulta que utilize a função SEARCH_IP na lista SELECT e pesquisa as três colunas VARCHAR na tabela:

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

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.

Execute uma consulta que pesquise por endereços IPv6 que correspondem a 2001:0db8:85a3:0000:0000:8a2e:0370:7334:

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

A consulta a seguir é similar à consulta anterior, mas exclui os zeros iniciais e os segmentos de zeros na search_string:

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

A consulta a seguir mostra que uma search_string com um intervalo CIDR de endereços IPv4:

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

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

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

Pesquisar por correspondente endereços IP que usam a função na cláusula WHERE

A consulta a seguir usa a função na cláusula WHERE e pesquisa somente a coluna 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 |
+-------------+-------------+-----------------------------------------+

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

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

A consulta a seguir usa a função na cláusula WHERE e pesquisa somente a coluna 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 |
+-------------+-----------------+---------------------+

Você pode usar o caractere * (ou table.*) como o primeiro argumento para a função SEARCH, conforme mostrado no exemplo a seguir. A pesquisa opera em todas as colunas qualificadas na tabela que você está selecionando:

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

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

A pesquisa a seguir usa a palavra-chave ILIKE para pesquisar apenas em colunas que terminam com a string _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 |
+-------------+-----------------+---------------------+

Ativar a otimização de pesquisa FULL_TEXT em colunas VARCHAR

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(
  ipv4_source,
  ipv4_target,
  ipv6_target,
  ANALYZER => 'ENTITY_ANALYZER');

Nota

As colunas que você especificar devem ser colunas.VARCHAR ou VARIANT. Não há suporte para colunas com outros tipos de dados.

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.

O exemplo a seguir 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" } ');

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

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

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

Nota

As colunas que você especificar devem ser colunas.VARCHAR ou VARIANT. Não há suporte para colunas com outros tipos de dados.

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

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;

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

O seguinte exemplo falha porque 5 não é um tipo de dados compatível com o argumento search_string:

SELECT SEARCH_IP(ipv4_source, 5) FROM ipt;

001045 (22023): SQL compilation error:
argument needs to be a string: '1'

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

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,

O exemplo a seguir falha porque o argumento search_string é uma string vazia.

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,

O exemplo a seguir falha porque nenhuma coluna com tipo de dados compatível é especificada para o argumento 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.

O exemplo a seguir é bem-sucedido porque uma coluna com um tipo de dados compatível é especificada 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, ipv4_source), '192.0.2.146') FROM ipt;

+---------------------------------------------+
| SEARCH_IP((ID, IPV4_SOURCE), '192.0.2.146') |
|---------------------------------------------|
| True                                        |
| False                                       |
+---------------------------------------------+