Categories:

Zeichenfolgen- und Binärfunktionen (Full-Text Search)

SEARCH_IP

Sucht nach gültigen IPv4- und IPv6-Adressen in angegebenen Zeichenfolgen aus einer oder mehreren Tabellen, einschließlich Feldern in den Spalten VARIANT, OBJECT und ARRAY. Die Suche basiert auf einer einzelnen IP-Adresse oder einem Bereich von IP-Adressen, die Sie angeben. Wenn eine IP-Adresse in der Spalte oder im Feld stimmt mit einer angegebenen IP-Adresse übereinstimmt oder in einem bestimmten Bereich liegt, dann gibt die Funktion TRUE zurück.

Weitere Informationen zur Verwendung dieser Funktion finden Sie unter Verwenden der Volltextsuche.

Syntax

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

Argumente

search_data

Die Daten, die Sie suchen möchten, ausgedrückt als durch Kommas getrennte Listen von Zeichenfolgen, Spaltennamen oder Pfaden zu Feldern in VARIANT-Spalten. Die Suchdaten können auch aus einer einzelnen literalen Zeichenfolge bestehen, was nützlich für das Testen der Funktion sein kann.

Sie können das Platzhalterzeichen (*) angeben, wobei sich * auf alle qualifizierten Spalten in allen Tabellen ausdehnt, die sich im Bereich der Funktion befinden. Qualifizierte Spalten sind diejenigen, die die Datentypen VARCHAR (Text), VARIANT, ARRAY und OBJECT haben. VARIANT-, ARRAY- und OBJECT-Daten werden für die Suche in Text umgewandelt. Sie können auch die Schlüsselwörter ILIKE und EXCLUDE zum Filtern verwenden.

Weitere Informationen zu diesem Argument finden Sie in der Beschreibung search_data der SEARCH Funktion.

'search_string'

Eine VARCHAR-Zeichenfolge, die eine der folgenden Adressen enthält:

  • Eine vollständige und gültige IP-Adresse im IPv4- oder IPv6-Standardformat , z. B. 192.0.2.1 oder 2001:0db8:85a3:0000:0000:8a2e:0370:7334.

  • Eine gültige IP-Adresse im IPv4- oder IPv6-Standardformat mit einem Classless Inter-Domain Routing (CIDR)-Bereich, z. B. 192.0.2.1/24 oder 2001:db8:85a3::/64.

  • Eine gültige IP-Adresse im IPv4- oder IPv6-Standardformat mit führenden Nullen, z. B. 192.000.002.001 anstelle von 192.0.2.1 oder 2001:0db8:85a3:0333:4444:8a2e:0370:7334 anstelle von 2001:db8:85a3:333:4444:8a2e:370:7334. Die Funktion akzeptiert bis zu drei Ziffern für jeden Teil eines IPv4-Adresse und bis zu vier Ziffern für jeden Teil einer IPv6-Adresse.

  • Eine gültige komprimierte IPv6-Adresse, z. B. 2001:db8:85a3:0:0:0:0:0 oder 2001:db8:85a3:: anstelle von 2001:db8:85a3:0000:0000:0000:0000:0000.

  • Eine doppelte IPv6-Adresse, die eine IPv6-Adresse und eine IPv4-Adresse miteinander kombiniert, z. B. 2001:db8:85a3::192.0.2.1.

Dieses Argument muss eine literale Zeichenfolge sein. Geben Sie ein Paar einfache Anführungszeichen um die Zeichenfolge an.

Die folgenden Arten von Argumenten werden nicht unterstützt:

  • Spaltennamen

  • Leere Zeichenfolgen

  • Mehr als eine IP-Adresse

  • Teilweise IPv4- und IPv6-Adressen

Rückgabewerte

Gibt einen BOOLEAN-Wert zurück:

  • Gibt TRUE zurück, wenn eine gültige IP-Adresse in search_string angegeben ist und eine passende IP-Adresse in search_data gefunden wird.

  • Gibt TRUE zurück, wenn in search_string eine gültige IP-Adresse mit einem CIDR-Bereich angegeben ist und in search_data eine IP-Adresse in dem angegebenen Bereich gefunden wird.

  • Gibt NULL zurück, wenn eines dieser Argumente NULL ist.

  • Andernfalls wird FALSE zurückgegeben.

Nutzungshinweise

  • Die SEARCH_IP-Funktion arbeitet nur mit VARCHAR (Text)-, VARIANT-, ARRAY- und OBJECT-Daten. Die Funktion gibt einen Fehler zurück, wenn das Argument search_data keine Daten dieser Datentypen enthält. Wenn das Argument search_data sowohl Daten von unterstützten als auch von nicht unterstützten Datentypen enthält, sucht die Funktion die Daten der unterstützten Datentypen und ignoriert die Daten der nicht unterstützten Datentypen. Weitere Beispiele finden Sie unter Beispiele für erwartete Fehlerfälle.

  • Die Funktion gibt einen Fehler zurück, wenn das Argument search_string keine gültige IP-Adresse ist. Weitere Beispiele finden Sie unter Beispiele für erwartete Fehlerfälle.

  • Sie können eine Optimierung der FULL_TEXT-Suche für Spalten hinzufügen, die das Ziel von SEARCH_IP-Funktionsaufrufen sind, indem Sie den Befehl ALTER TABLE verwenden, der die ENTITY_ANALYZER angibt. Beispiel:

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

    Der ENTITY_ANALYZER erkennt nur die Entitäten (z. B. IP-Adressen). Daher ist der Suchzugriffspfad in der Regel viel kleiner als die FULL_TEXT-Suchoptimierung mit einem anderen Analysator.

    Weitere Informationen finden Sie unter Aktivieren der FULL_TEXT-Suchoptimierung.

Beispiele

Die folgenden Beispiele verwenden die Funktion SEARCH_IP.

Suche nach passenden IP-Adressen in VARCHAR-Spalten

Die folgenden Beispiele zeigen, wie Sie die Funktion SEARCH_IP verwenden, um VARCHAR (Text)-Spalten abzufragen.

Erstellen Sie zunächst eine Tabelle namens ipt mit zwei Spalten, in denen IPv4 -Adressen gespeichert werden, und einer Spalte, in der IPv6-Adressen gespeichert werden:

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

Fügen Sie zwei Zeilen in die Tabelle ein:

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

Fragen Sie die Tabelle ab:

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

In den folgenden Abschnitten werden Abfragen ausgeführt, die die SEARCH_IP-Funktion auf diese Tabellendaten anwenden:

Suchen Sie nach passenden IP -Adressen durch Verwendung der Funktion in einer SELECT-Liste

Führen Sie eine Abfrage aus, die die SEARCH_IP-Funktion in der SELECT -Liste verwendet, und durchsuchen Sie die drei VARCHAR-Spalten in der Tabelle:

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

Beachten Sie, dass search_data 192.000.002.146 eine Übereinstimmung mit search_string 192.0.2.146 ist, obwohl 192.000.002.146 führende Nullen enthält.

Führen Sie eine Abfrage aus, die nach IPv6-Adressen sucht, die mit 2001:0db8:85a3:0000:0000:8a2e:0370:7334 übereinstimmen:

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

Die folgende Abfrage ist dieselbe wie die vorherige Abfrage, schließt aber die führenden Nullen und die Nullsegmente in search_string aus:

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

Die folgende Abfrage zeigt, dass ein search_string durch einen CIDR-Bereich für IPv4-Adressen ersetzt wurde:

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

Diese Abfrage zeigt, dass eine search_string mit führenden Nullen True für IPv4-Adressen zurückgibt, bei denen die führenden Nullen weggelassen wurden:

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

Suchen Sie nach passenden IP -Adressen durch Verwendung der Funktion in der WHERE-Klausel

Die folgende Abfrage verwendet die Funktion in der WHERE-Klausel und sucht nur in der Spalte 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 |
+-------------+-------------+-----------------------------------------+

Wenn die Funktion in der WHERE-Klausel verwendet wird und es keine Übereinstimmung gibt, werden keine Werte zurückgegeben:

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

Die folgende Abfrage verwendet die Funktion in der WHERE-Klausel und sucht nur in der Spalte 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 |
+-------------+-----------------+---------------------+

Sie können das Zeichen * (oder table.*) als erstes Argument für die SEARCH-Funktion verwenden, wie im folgenden Beispiel gezeigt. Die Operation bezieht sich auf alle qualifizierten Spalten in der Tabelle, aus der Sie auswählen.

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

Sie können auch die Schlüsselwörter ILIKE und EXCLUDE zum Filtern verwenden. Weitere Informationen zu diesen Schlüsselwörtern finden Sie unter SELECT.

Die folgende Suche verwendet das Schlüsselwort ILIKE, um nur in Spalten zu suchen, die mit der Zeichenfolge _target enden.

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

Aktivieren Sie die FULL_TEXT-Suchoptimierung in VARCHAR-Spalten

Um die Optimierung der FULL_TEXT-Suche für die Spalten der Tabelle ipt zu aktivieren, führen Sie den folgenden ALTER TABLE Befehl aus:

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

Bemerkung

Die von Ihnen angegebenen Spalten müssen VARCHAR oder VARIANT-Spalten sein. Spalten mit anderen Datentypen werden nicht unterstützt.

Suche nach passenden IP-Adressen in einer VARIANT-Spalte

Die folgenden Beispiele zeigen, wie Sie die Funktion SEARCH_IP verwenden, um VARIANT-Spalten abzufragen.

Das folgende Beispiel verwendet die Funktion SEARCH_IP, um einen Pfad zu einem Feld in einer VARIANT-Spalte zu suchen. Erstellen Sie eine Tabelle mit dem Namen iptv und fügen Sie zwei Zeilen ein:

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

Führen Sie die folgenden Suchabfragen aus. Die erste Abfrage durchsucht nur das Feld ipv1. Die zweite Abfrage durchsucht ipv1 und 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"  |
| }                        |
+--------------------------+

Um die Optimierung der FULL_TEXT-Suche für diese ip1 VARIANT Spalte und ihre Felder zu aktivieren, führen Sie den folgenden ALTER TABLE-Befehl aus:

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

Bemerkung

Die von Ihnen angegebenen Spalten müssen VARCHAR oder VARIANT-Spalten sein. Spalten mit anderen Datentypen werden nicht unterstützt.

Suche nach übereinstimmenden IP-Adressen in langen Textzeichenfolgen

Erstellen einer Tabelle namens ipt_log und Einfügen von Zeilen:

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

Suchen Sie nach Protokolleinträgen in der Spalte ip_request_log, die die IP-Adresse 192.0.2.146 enthalten:

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

Beispiele für erwartete Fehlerfälle

Die folgenden Beispiele zeigen Abfragen, die erwartete Syntaxfehler zurückgeben.

Das folgende Beispiel schlägt fehl, weil 5 kein unterstützter Datentyp für das Argument search_string ist:

SELECT SEARCH_IP(ipv4_source, 5) FROM ipt;

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

Das folgende Beispiel schlägt fehl, weil das Argument search_string keine gültige IP-Adresse ist.

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,

Das folgende Beispiel schlägt fehl, weil das Argument search_string eine leere Zeichenfolge ist.

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,

Das folgende Beispiel schlägt fehl, weil für das Argument search_data keine Spalten mit unterstützten Datentypen angegeben sind.

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.

Das folgende Beispiel ist erfolgreich, weil eine Spalte mit einem unterstützten Datentyp für das Argument search_data angegeben ist. Die Funktion ignoriert die id-Spalte, da es sich nicht um einen unterstützten Datentyp handelt:

SELECT SEARCH_IP((id, ipv4_source), '192.0.2.146') FROM ipt;

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