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
ou2001: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
ou2001:db8:85a3::/64
.Um endereço IP válido no formato IPv4 ou IPv6 com zeros à esquerda, como
192.000.002.001
em vez de192.0.2.1
ou2001:0db8:85a3:0333:4444:8a2e:0370:7334
em vez de2001: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
ou2001:db8:85a3::
em vez de2001: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 emsearch_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 emsearch_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 argumentosearch_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 uma coluna VARIANT
Procure por endereços IP correspondentes em longas cadeias de caracteres de texto
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
Pesquisar por correspondente endereços IP que usam a função na cláusula WHERE
Ativar a otimização de pesquisa FULL_TEXT em colunas VARCHAR
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 |
+---------------------------------------------+