SEARCH_IP

Recherches d’adresses IPv4 et IPv6 valides dans des colonnes de chaînes de caractères spécifiées à partir d’une ou plusieurs tables, y compris des champs dans des colonnes VARIANT, OBJECT et ARRAY. La recherche est basée sur une seule adresse IP ou une plage d’adresses IP que vous spécifiez. Si l’adresse IP dans la colonne ou le champ correspond à un adresse IP spécifiée ou est dans une plage spécifiée, alors la fonction retourne TRUE.

Pour plus d’informations sur l’utilisation de ces fonctions, voir Utilisation de la recherche en texte intégral.

Syntaxe

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

Arguments

search_data

Les données que vous souhaitez rechercher, exprimées sous la forme d’une liste délimitée par des virgules de littéraux de chaîne, de noms de colonnes ou chemins vers les champs dans les colonnes VARIANT. Les données de recherche peuvent également être une chaîne littérale unique, ce qui peut être utile lorsque vous testez la fonction.

Vous pouvez spécifier le caractère générique (*), où * s’étend à toutes les colonnes éligibles dans toutes les tables concernées par la fonction. Les colonnes éligibles sont celles qui ont les types de données VARCHAR (texte), VARIANT, ARRAY et OBJECT. Les données VARIANT, ARRAY et OBJECT sont converties en texte pour la recherche. Vous pouvez également utiliser les mots-clés ILIKE et EXCLUDE pour le filtrage.

Pour plus d’informations sur cet argument, voir la search_data description de la fonction SEARCH.

'search_string'

Une chaîne VARCHAR contenant l’une des adresses suivantes :

  • Une adresse IP complète et valide au format standard IPv4 ou IPv6, par exemple 192.0.2.1 ou 2001:0db8:85a3:0000:0000:8a2e:0370:7334.

  • Une adresse IP valide au format standard IPv4 ou IPv6 avec une plage Classless Inter-Domain Routing (CIDR), par exemple 192.0.2.1/24 ou 2001:db8:85a3::/64.

  • Une adresse IP valide au format standard IPv4 ou IPv6 avec des zéros non significatifs, par exemple 192.000.002.001 (et non pas 192.0.2.1) ou 2001:0db8:85a3:0333:4444:8a2e:0370:7334 (et non pas 2001:db8:85a3:333:4444:8a2e:370:7334). La fonction accepte jusqu’à trois chiffres pour chaque partie d’une adresse IPv4, et jusqu’à quatre chiffres pour chaque partie d’une adresse IPv6.

  • Une adresse IPv6 compressée valide, par exemple 2001:db8:85a3:0:0:0:0:0 ou 2001:db8:85a3:: (et non pas 2001:db8:85a3:0000:0000:0000:0000:0000).

  • Une adresse IPv6 double qui combine une adresse IPv6 et IPv4, par exemple 2001:db8:85a3::192.0.2.1.

Cet argument doit être une chaîne littérale. Spécifiez une paire de guillemets simples autour de la chaîne.

Les types d’arguments suivants ne sont pas pris en charge :

  • Noms de colonne

  • Chaînes vides

  • Plus d’une adresse IP

  • Adresses IPv4 et IPv6 partielles

Renvoie

Renvoie une valeur BOOLEAN :

  • Renvoie TRUE si une adresse IP valide est spécifiée dans search_string et une adresse IP correspondante se trouve dans search_data.

  • Renvoie TRUE si une adresse IP valide avec une plage CIDR la plage est spécifiée dans search_string et une adresse IP dans la plage spécifiée est trouvée dans search_data.

  • Renvoie NULL si l’un de ces arguments est NULL.

  • Sinon, renvoie FALSE.

Notes sur l’utilisation

  • La fonction SEARCH_IP fonctionne uniquement sur les données VARCHAR (texte), VARIANT, ARRAY et OBJECT. La fonction renvoie une erreur si l’argument search_data ne contient pas de données de ces types de données. Lorsque l’argument search_data inclut des données des types de données pris en charge et des types de données non pris en charge, la fonction recherche les données des types de données pris en charge et ignore silencieusement les données des types de données non pris en charge. Pour des exemples, voir Exemples de cas d’erreur prévus.

  • La fonction renvoie une erreur si l’argument search_string n’est pas une adresse IP valide. Pour des exemples, voir Exemples de cas d’erreur prévus.

  • Vous pouvez ajouter une optimisation FULL_TEXT de la recherche sur les colonnes qui sont la cible d’appels de fonction SEARCH_IP à l’aide d’une commande ALTER TABLE qui spécifie ENTITY_ANALYZER. Par exemple :

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

    L’ENTITY_ANALYZER ne reconnaît que les entités (par exemple, les adresses IP). Par conséquent, le chemin d’accès à la recherche est généralement beaucoup plus petit que l’optimisation de la recherche FULL_TEXT avec un analyseur différent.

    Pour plus d’informations, voir activer l’optimisation de la recherche FULL_TEXT.

Exemples

Les exemples suivants utilisent la fonction SEARCH_IP :

Rechercher une adresse IP correspondante dans les colonnes VARCHAR

Ces exemples montrent comment utiliser la fonction SEARCH_IP pour interroger les colonnes VARCHAR (texte).

Commencez par créer une table nommée ipt comportant deux colonnes qui stockent les adresses IPv4 et une colonne qui stocke les adresses IPv6 :

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

Insérez deux lignes dans la table :

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

Interrogez la table :

SELECT * FROM ipt;
Copy
+----+-------------+-----------------+-----------------------------------------+
| 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                     |
+----+-------------+-----------------+-----------------------------------------+

Les sections suivantes exécutent des requêtes qui utilisent la fonction SEARCH_IP sur ces données de table :

Rechercher les adresses IP correspondantes en utilisant la fonction dans une liste SELECT

Exécuter une requête qui utilise la fonction SEARCH_IP dans la liste SELECT et recherche les trois colonnes VARCHAR dans la table :

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

Notez que search_data 192.000.002.146 correspond à search_string 192.0.2.146 même si 192.000.002.146 comporte des zéros non significatifs.

Exécuter une requête qui recherche des adresses IPv6 qui correspondent à 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;
Copy
+-------------+-----------------+-----------------------------------------+--------------+
| 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         |
+-------------+-----------------+-----------------------------------------+--------------+

La requête suivante est identique à la requête précédente, mais elle exclut les zéros non significatifs et les segments de zéros dans la 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;
Copy
+-------------+-----------------+-----------------------------------------+--------------+
| 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         |
+-------------+-----------------+-----------------------------------------+--------------+

La requête suivante montre une search_string avec la plage CIDR pour les adresses 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;
Copy
+-------------+-----------------+--------------+
| IPV4_SOURCE | IPV4_TARGET     | Match found? |
|-------------+-----------------+--------------|
| 192.0.2.111 | 192.000.002.146 | True         |
| 192.0.2.146 | 203.0.113.5     | True         |
+-------------+-----------------+--------------+

La requête suivante montre une search_string avec des zéros non significatifs qui renvoie True pour les adresses IPv4 qui omettent les zéros non significatifs :

SELECT ipv4_source,
       ipv4_target,
       SEARCH_IP((ipv4_source, ipv4_target), '203.000.113.005') AS "Match found?"
  FROM ipt
  ORDER BY ipv4_source;
Copy
+-------------+-----------------+--------------+
| IPV4_SOURCE | IPV4_TARGET     | Match found? |
|-------------+-----------------+--------------|
| 192.0.2.111 | 192.000.002.146 | False        |
| 192.0.2.146 | 203.0.113.5     | True         |
+-------------+-----------------+--------------+

Rechercher les adresses IP correspondantes en utilisant la fonction dans une clause WHERE

La requête suivante utilise la fonction dans la clause WHERE et recherche la colonne ipv4_target seulement.

SELECT ipv4_source,
       ipv4_target,
       ipv6_target
  FROM ipt
  WHERE SEARCH_IP(ipv4_target, '203.0.113.5')
  ORDER BY ipv4_source;
Copy
+-------------+-------------+-----------------------------------------+
| IPV4_SOURCE | IPV4_TARGET | IPV6_TARGET                             |
|-------------+-------------+-----------------------------------------|
| 192.0.2.146 | 203.0.113.5 | 2001:0db8:85a3:0000:0000:8a2e:0370:7334 |
+-------------+-------------+-----------------------------------------+

Lorsque la fonction est utilisée dans la clause WHERE et qu’il n’y a pas de correspondance, aucune valeur n’est renvoyée :

SELECT ipv4_source,
       ipv4_target,
       ipv6_target
  FROM ipt
  WHERE SEARCH_IP(ipv4_target, '203.0.113.1')
  ORDER BY ipv4_source;
Copy
+-------------+-------------+-------------+
| IPV4_SOURCE | IPV4_TARGET | IPV6_TARGET |
|-------------+-------------+-------------|
+-------------+-------------+-------------+

La requête suivante utilise la fonction dans la clause WHERE et recherche la colonne ipv6_target seulement.

SELECT ipv4_source,
       ipv4_target,
       ipv6_target
  FROM ipt
  WHERE SEARCH_IP(ipv6_target, '2001:db8:1234::5678')
  ORDER BY ipv4_source;
Copy
+-------------+-----------------+---------------------+
| IPV4_SOURCE | IPV4_TARGET     | IPV6_TARGET         |
|-------------+-----------------+---------------------|
| 192.0.2.111 | 192.000.002.146 | 2001:db8:1234::5678 |
+-------------+-----------------+---------------------+

Vous pouvez utiliser le caractère * (ou table.*) comme premier argument de la fonction SEARCH, comme illustré dans l’exemple suivant. La recherche s’effectue sur toutes les colonnes éligibles de la table que vous sélectionnez :

SELECT ipv4_source,
       ipv4_target,
       ipv6_target
  FROM ipt
  WHERE SEARCH_IP((*), '192.0.2.146')
  ORDER BY ipv4_source;
Copy
+-------------+-----------------+-----------------------------------------+
| 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 |
+-------------+-----------------+-----------------------------------------+

Vous pouvez également utiliser les mots-clés ILIKE et EXCLUDE pour le filtrage. Pour plus d’informations sur ces mots-clés, voir SELECT.

La recherche suivante utilise le mot-clé ILIKE pour rechercher uniquement dans les colonnes qui se terminent par la chaîne _target.

SELECT ipv4_source,
       ipv4_target,
       ipv6_target
  FROM ipt
  WHERE SEARCH_IP(* ILIKE '%_target', '192.0.2.146')
  ORDER BY ipv4_source;
Copy
+-------------+-----------------+---------------------+
| IPV4_SOURCE | IPV4_TARGET     | IPV6_TARGET         |
|-------------+-----------------+---------------------|
| 192.0.2.111 | 192.000.002.146 | 2001:db8:1234::5678 |
+-------------+-----------------+---------------------+

Activer l’optimisation de la recherche FULL_TEXT sur les colonnes VARCHAR

Pour activer l’optimisation de la recherche FULL_TEXT pour les colonnes dans la table ipt, exécutez la commande ALTER TABLE suivante :

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

Note

Les colonnes que vous spécifiez doivent être les colonnes VARCHAR ou VARIANT. Les colonnes comportant d’autres types de données ne sont pas prises en charge.

Rechercher une adresse IP correspondante dans une colonne VARIANT

Ces exemples montrent comment utiliser la fonction SEARCH_IP pour interroger les colonnes VARIANT.

L’exemple suivant utilise la fonction SEARCH_IP pour rechercher un chemin vers un champ dans une colonne VARIANT. Créez une table nommée iptv et insérez deux lignes :

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

Exécutez les requêtes de recherche suivantes. La première requête recherche le champ ipv1 seulement. La deuxième recherche ipv1 et 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"  |
| }                        |
+--------------------------+

Pour activer l’optimisation de la recherche FULL_TEXT pour cette colonne ip1 VARIANT et ses champs, exécutez la commande ALTER TABLE suivante :

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

Note

Les colonnes que vous spécifiez doivent être les colonnes VARCHAR ou VARIANT. Les colonnes comportant d’autres types de données ne sont pas prises en charge.

Rechercher des adresses IP correspondantes dans de longues chaînes de texte

Créer une table nommée ipt_log et insérer des lignes :

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

Rechercher des entrées de journal dans la colonne ip_request_log qui incluent l’adresse 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.   |
+----+---------------------------------------------------+

Exemples de cas d’erreur prévus

Les exemples suivants montrent des requêtes qui renvoient des erreurs de syntaxe attendues.

L’exemple suivant échoue, parce que 5 n’est pas un type de données pris en charge pour l’argument search_string :

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

L’exemple suivant échoue car l’argument search_string n’est pas une adresse IP valide.

SELECT SEARCH_IP(ipv4_source, '1925.0.2.146') FROM ipt;
Copy
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,

L’exemple suivant échoue car l’argument search_string est une chaîne vide.

SELECT SEARCH_IP(ipv4_source, '') FROM ipt;
Copy
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,

L’exemple suivant échoue car aucune colonne avec les types de données pris en charge n’est spécifiée pour l’argument 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.

L’exemple suivant réussit, parce qu’une colonne avec un type de données pris en charge est spécifiée pour l’argument search_data. La fonction ignore la colonne id car ce n’est pas un type de données pris en charge :

SELECT SEARCH_IP((id, ipv4_source), '192.0.2.146') FROM ipt;
Copy
+---------------------------------------------+
| SEARCH_IP((ID, IPV4_SOURCE), '192.0.2.146') |
|---------------------------------------------|
| True                                        |
| False                                       |
+---------------------------------------------+
Langage: Français