Kategorien:

Zeichenfolgenfunktionen (reguläre Ausdrücke)

REGEXP_SUBSTR

Gibt die Teilzeichenfolge zurück, die mit einem regulären Ausdruck innerhalb einer Zeichenfolge übereinstimmt.

Syntax

REGEXP_SUBSTR( <subject> ,
               <pattern>
                 [ , <position>
                   [ , <occurrence>
                     [ , <regex_parameters>
                       [ , <group_num> ]
                     ]
                   ]
                 ]
)

Argumente

Benötigt:

subject

Die Zeichenfolge, nach der gesucht werden soll.

pattern

Abzugleichendes Muster.

Richtlinien zur Angabe von Mustern finden Sie unter Zeichenfolgenfunktionen (reguläre Ausdrücke).

Optional:

position

Anzahl der Zeichen ab dem Anfang der Zeichenfolge, an dem die Funktion mit der Suche nach Übereinstimmungen beginnt. Der Wert muss eine positive Ganzzahl sein.

Standard: 1 (die Suche nach einer Übereinstimmung beginnt beim ersten Zeichen links)

occurrence

Gibt das erste Auftreten des Musters an, ab dem Übereinstimmungen zurückgegeben werden sollen.

Die Funktion überspringt die ersten occurrence - 1 Übereinstimmungen. Wenn es beispielsweise 5 Übereinstimmungen gibt und Sie 3 für das occurrence-Argument angeben, ignoriert die Funktion die ersten beiden Übereinstimmungen und gibt die dritte, vierte und fünfte Übereinstimmung zurück.

Standard: 1

regex_parameters

Zeichenfolge aus einem oder mehreren Zeichen, in der die Parameter angegeben sind, die zur Suche nach Übereinstimmungen verwendet werden. Unterstützte Werte:

Parameter

Beschreibung

c

Abgleich, bei dem zwischen Groß- und Kleinschreibung unterschieden wird

i

Abgleich ohne Berücksichtigung der Groß-/Kleinschreibung

m

Mehrzeiliger Modus.

e

Untergeordnete Übereinstimmungen extrahieren

s

POSIX-Platzhalterzeichen . im einzeiligen Modus stimmt mit \n überein

Standard: c

Weitere Informationen dazu finden Sie unter Angeben der Parameter für reguläre Ausdrücke.

Bemerkung

Standardmäßig gibt REGEXP_SUBSTR den gesamten übereinstimmenden Teil des Subjekts zurück. Wenn jedoch der Parameter e (für „extrahieren“) angegeben ist, gibt REGEXP_SUBSTR nur den Teil des Subjekts zurück, der mit der ersten Gruppe im Muster übereinstimmt. Wenn zwar e angegeben ist, aber kein Wert für group_num, dann ist der Wert von group_num standardmäßig 1 (die erste Gruppe). Wenn das Muster keinen Teilausdruck enthält, verhält sich REGEXP_SUBSTR so, als wäre e nicht gesetzt. Beispiele, die e verwenden, finden Sie im Abschnitt Beispiele (unter diesem Thema).

group_num

Gibt an, welche Gruppe extrahiert werden soll. Gruppen werden im regulären Ausdruck mithilfe von Klammern angegeben.

Wenn eine group_num angegeben ist, ermöglicht Snowflake das Extrahieren, auch wenn die Option 'e' nicht angegeben wurde. Das 'e' ist impliziert.

Snowflake unterstützt bis zu 1.024 Gruppen.

Beispiele, die group_num verwenden, finden Sie im Abschnitt Beispiele (unter diesem Thema).

Rückgabewerte

Gibt einen Wert vom Typ VARCHAR zurück, der mit der Teilzeichenfolge übereinstimmt.

Die Funktion gibt in den folgenden Fällen NULL zurück:

  • Es wurde keine Übereinstimmung gefunden.

  • Alle Argumente sind NULL.

Nutzungshinweise

Weitere Informationen zur Verwendung regulärer Ausdrücke finden Sie unter Zeichenfolgenfunktionen (reguläre Ausdrücke).

Sortierungsdetails

Arguments with collation specifications currently aren’t supported.

Beispiele

Die Dokumentation zur Funktion REGEXP_INSTR enthält viele Beispiele, die sowohl REGEXP_SUBSTR als auch REGEXP_INSTR verwenden. Vielleicht möchten Sie sich auch diese Beispiele ansehen.

In den Beispielen werden die unten erstellten Zeichenfolgen verwendet:

CREATE OR REPLACE TABLE demo2 (id INT, string1 VARCHAR);

INSERT INTO demo2 (id, string1) VALUES
    (2, 'It was the best of times, it was the worst of times.'),
    (3, 'In    the   string   the   extra   spaces  are   redundant.'),
    (4, 'A thespian theater is nearby.');

SELECT * FROM demo2;

+----+-------------------------------------------------------------+
| ID | STRING1                                                     |
|----+-------------------------------------------------------------|
|  2 | It was the best of times, it was the worst of times.        |
|  3 | In    the   string   the   extra   spaces  are   redundant. |
|  4 | A thespian theater is nearby.                               |
+----+-------------------------------------------------------------+

Die Zeichenfolgen haben die folgenden Eigenschaften:

  • Die Zeichenfolge mit einer id von 2 hat mehrere Vorkommen des Wortes „the“.

  • Die Zeichenfolge mit einer id von 3 hat mehrere Vorkommen des Wortes „the“ mit zusätzlichen Leerzeichen zwischen den Wörtern.

  • Die Zeichenfolge mit einer id von 4 enthält die Zeichenfolge „the“ innerhalb mehrerer Wörter („thespian“ und „theater“), aber ohne das Wort „the“ selbst.

Die folgenden Beispiele rufen die Funktion REGEXP_SUBSTR auf.

Aufrufen der Funktion REGEXP_SUBSTR in einer SELECT-Liste

Rufen Sie die Funktion REGEXP_SUBSTR in einer SELECT-Liste auf, um Werte zu extrahieren oder anzuzeigen, die mit einem Muster übereinstimmen.

In Beispiel wird nach dem ersten Vorkommen des Wortes the gesucht, gefolgt von einem oder mehreren Nicht-Wort-Zeichen (z. B. dem Leerzeichen, das Wörter trennt), gefolgt von einem oder mehreren Wort-Zeichen.

„Wortzeichen“ umfassen nicht nur die Buchstaben a–z und A–Z, sondern auch den Unterstrich („_“) und die Dezimalziffern 0–9, jedoch keine Leerzeichen, Satzzeichen und so weiter.

SELECT id,
       REGEXP_SUBSTR(string1, 'the\\W+\\w+') AS result
  FROM demo2
  ORDER BY id;

+----+--------------+
| ID | RESULT       |
|----+--------------|
|  2 | the best     |
|  3 | the   string |
|  4 | NULL         |
+----+--------------+

Suchen Sie ab Position 1 der Zeichenfolge nach dem zweiten Vorkommen des Wortes the, gefolgt von einem oder mehreren Nicht-Wort-Zeichen, gefolgt von einem oder mehreren Wort-Zeichen.

SELECT id,
       REGEXP_SUBSTR(string1, 'the\\W+\\w+', 1, 2) AS result
  FROM demo2
  ORDER BY id;

+----+-------------+
| ID | RESULT      |
|----+-------------|
|  2 | the worst   |
|  3 | the   extra |
|  4 | NULL        |
+----+-------------+

Suchen Sie ab Position 1 der Zeichenfolge nach dem zweiten Vorkommen des Wortes the, gefolgt von einem oder mehreren Nicht-Wort-Zeichen, gefolgt von einem oder mehreren Wort-Zeichen.

Anstatt den gesamten Treffer zurückzugeben, geben Sie nur die „Gruppe“ zurück (z. B. den Teil der Teilzeichenkette, der mit dem Teil des regulären Ausdrucks in Klammern übereinstimmt). In diesem Fall sollte der zurückgegebene Wert das Wort nach „the“ sein.

SELECT id,
       REGEXP_SUBSTR(string1, 'the\\W+(\\w+)', 1, 2, 'e', 1) AS result
  FROM demo2
  ORDER BY id;

+----+--------+
| ID | RESULT |
|----+--------|
|  2 | worst  |
|  3 | extra  |
|  4 | NULL   |
+----+--------+

In diesem Beispiel wird gezeigt, wie das zweite Wort aus den ersten, zweiten und dritten Übereinstimmungen eines Zwei-Wort-Musters abgerufen wird, in dem das erste Wort A ist. Dieses Beispiel zeigt auch, dass der Versuch, über das letzte Muster hinauszugehen, Snowflake dazu veranlasst, NULL zurückzugeben.

Erstellen Sie zunächst eine Tabelle und fügen Sie Daten ein:

CREATE OR REPLACE TABLE test_regexp_substr (string1 VARCHAR);;
INSERT INTO test_regexp_substr (string1) VALUES ('A MAN A PLAN A CANAL');

Führen Sie die Abfrage aus:

SELECT REGEXP_SUBSTR(string1, 'A\\W+(\\w+)', 1, 1, 'e', 1) AS result1,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w+)', 1, 2, 'e', 1) AS result2,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w+)', 1, 3, 'e', 1) AS result3,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w+)', 1, 4, 'e', 1) AS result4
  FROM test_regexp_substr;

+---------+---------+---------+---------+
| RESULT1 | RESULT2 | RESULT3 | RESULT4 |
|---------+---------+---------+---------|
| MAN     | PLAN    | CANAL   | NULL    |
+---------+---------+---------+---------+

In diesem Beispiel wird gezeigt, wie die erste, zweite und dritte Gruppe innerhalb des ersten Auftretens des Musters abgerufen werden. In diesem Fall sind die zurückgegebenen Werte die einzelnen Buchstaben des Wortes MAN.

SELECT REGEXP_SUBSTR(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 'e', 1) AS result1,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 'e', 2) AS result2,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 'e', 3) AS result3
  FROM test_regexp_substr;

+---------+---------+---------+
| RESULT1 | RESULT2 | RESULT3 |
|---------+---------+---------|
| M       | A       | N       |
+---------+---------+---------+

Hier sind einige zusätzliche Beispiele.

Erstellen Sie eine Tabelle, und fügen Sie Daten ein:

CREATE OR REPLACE TABLE message(body VARCHAR(255));

INSERT INTO message VALUES
  ('Hellooo World'),
  ('How are you doing today?'),
  ('the quick brown fox jumps over the lazy dog'),
  ('PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS');

Gibt die erste Übereinstimmung zurück, die ein klein geschriebenes o enthält, durch Abgleich einer Wortgrenze (\b), gefolgt von null oder mehr Wortzeichen (\S), dem Buchstaben o und dann null oder mehr Wortzeichen bis zur nächsten Wortgrenze:

SELECT body,
       REGEXP_SUBSTR(body, '\\b\\S*o\\S*\\b') AS result
  FROM message;

+---------------------------------------------+---------+
| BODY                                        | RESULT  |
|---------------------------------------------+---------|
| Hellooo World                               | Hellooo |
| How are you doing today?                    | How     |
| the quick brown fox jumps over the lazy dog | brown   |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | NULL    |
+---------------------------------------------+---------+

Gibt die erste Übereinstimmung zurück, die ein klein geschriebenes o enthält, beginnend mit dem dritten Zeichen im Subjekt:

SELECT body,
       REGEXP_SUBSTR(body, '\\b\\S*o\\S*\\b', 3) AS result
  FROM message;

+---------------------------------------------+--------+
| BODY                                        | RESULT |
|---------------------------------------------+--------|
| Hellooo World                               | llooo  |
| How are you doing today?                    | you    |
| the quick brown fox jumps over the lazy dog | brown  |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | NULL   |
+---------------------------------------------+--------+

Gibt die dritte Übereinstimmung zurück, die ein klein geschriebenes o enthält, beginnend mit dem dritten Zeichen im Subjekt:

SELECT body,
       REGEXP_SUBSTR(body, '\\b\\S*o\\S*\\b', 3, 3) AS result
  FROM message;

+---------------------------------------------+--------+
| BODY                                        | RESULT |
|---------------------------------------------+--------|
| Hellooo World                               | NULL   |
| How are you doing today?                    | today  |
| the quick brown fox jumps over the lazy dog | over   |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | NULL   |
+---------------------------------------------+--------+

Gibt die dritte Übereinstimmung zurück, die ein klein geschriebenes o enthält, beginnend mit dem dritten Zeichen im Subjekt, wobei Groß- und Kleinschreibung nicht berücksichtigt werden:

SELECT body,
       REGEXP_SUBSTR(body, '\\b\\S*o\\S*\\b', 3, 3, 'i') AS result
  FROM message;

+---------------------------------------------+--------+
| BODY                                        | RESULT |
|---------------------------------------------+--------|
| Hellooo World                               | NULL   |
| How are you doing today?                    | today  |
| the quick brown fox jumps over the lazy dog | over   |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | LIQUOR |
+---------------------------------------------+--------+

Dieses Beispiel zeigt, dass Sie alle Parameter für reguläre Ausdrücke explizit weglassen können, indem Sie eine leere Zeichenfolge angeben.

SELECT body,
       REGEXP_SUBSTR(body, '(H\\S*o\\S*\\b).*', 1, 1, '') AS result
  FROM message;

+---------------------------------------------+--------------------------+
| BODY                                        | RESULT                   |
|---------------------------------------------+--------------------------|
| Hellooo World                               | Hellooo World            |
| How are you doing today?                    | How are you doing today? |
| the quick brown fox jumps over the lazy dog | NULL                     |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | NULL                     |
+---------------------------------------------+--------------------------+

Im folgenden Beispiel werden sich überlappende Vorkommen veranschaulicht: Erstellen Sie zunächst eine Tabelle und fügen Sie Daten ein:

CREATE OR REPLACE TABLE overlap (
  id NUMBER,
  a STRING);

INSERT INTO overlap VALUES (1, ',abc,def,ghi,jkl,');
INSERT INTO overlap VALUES (2, ',abc,,def,,ghi,,jkl,');

SELECT * FROM overlap;

+----+----------------------+
| ID | A                    |
|----+----------------------|
|  1 | ,abc,def,ghi,jkl,    |
|  2 | ,abc,,def,,ghi,,jkl, |
+----+----------------------+

Führen Sie eine Abfrage aus, die das zweite Auftreten des folgenden Musters in jeder Zeile findet: ein Satzzeichen gefolgt von Ziffern und Buchstaben, gefolgt von einem Satzzeichen.

SELECT id,
       REGEXP_SUBSTR(a,'[[:punct:]][[:alnum:]]+[[:punct:]]', 1, 2) AS result
  FROM overlap;

+----+--------+
| ID | RESULT |
|----+--------|
|  1 | ,ghi,  |
|  2 | ,def,  |
+----+--------+

Im folgenden Beispiel wird aus dem Zugriffsprotokoll eines Apache HTTP-Servers ein JSON-Objekt erstellt, wobei Mustererkennung und Verkettung zum Einsatz kommen: Erstellen Sie zunächst eine Tabelle und fügen Sie Daten ein:

CREATE OR REPLACE TABLE test_regexp_log (logs VARCHAR);

INSERT INTO test_regexp_log (logs) VALUES
  ('127.0.0.1 - - [10/Jan/2018:16:55:36 -0800] "GET / HTTP/1.0" 200 2216'),
  ('192.168.2.20 - - [14/Feb/2018:10:27:10 -0800] "GET /cgi-bin/try/ HTTP/1.0" 200 3395');

SELECT * from test_regexp_log

+-------------------------------------------------------------------------------------+
| LOGS                                                                                |
|-------------------------------------------------------------------------------------|
| 127.0.0.1 - - [10/Jan/2018:16:55:36 -0800] "GET / HTTP/1.0" 200 2216                |
| 192.168.2.20 - - [14/Feb/2018:10:27:10 -0800] "GET /cgi-bin/try/ HTTP/1.0" 200 3395 |
+-------------------------------------------------------------------------------------+

Führen Sie eine Abfrage durch:

SELECT '{ "ip_addr":"'
       || REGEXP_SUBSTR (logs,'\\b\\d{1,3}\.\\d{1,3}\.\\d{1,3}\.\\d{1,3}\\b')
       || '", "date":"'
       || REGEXP_SUBSTR (logs,'([\\w:\/]+\\s[+\-]\\d{4})')
       || '", "request":"'
       || REGEXP_SUBSTR (logs,'\"((\\S+) (\\S+) (\\S+))\"', 1, 1, 'e')
       || '", "status":"'
       || REGEXP_SUBSTR (logs,'(\\d{3}) \\d+', 1, 1, 'e')
       || '", "size":"'
       || REGEXP_SUBSTR (logs,'\\d{3} (\\d+)', 1, 1, 'e')
       || '"}' as Apache_HTTP_Server_Access
  FROM test_regexp_log;

+-----------------------------------------------------------------------------------------------------------------------------------------+
| APACHE_HTTP_SERVER_ACCESS                                                                                                               |
|-----------------------------------------------------------------------------------------------------------------------------------------|
| { "ip_addr":"127.0.0.1", "date":"10/Jan/2018:16:55:36 -0800", "request":"GET / HTTP/1.0", "status":"200", "size":"2216"}                |
| { "ip_addr":"192.168.2.20", "date":"14/Feb/2018:10:27:10 -0800", "request":"GET /cgi-bin/try/ HTTP/1.0", "status":"200", "size":"3395"} |
+-----------------------------------------------------------------------------------------------------------------------------------------+

Aufrufen der Funktion REGEXP_SUBSTR in einer WHERE-Klausel

Rufen Sie die Funktion REGEXP_SUBSTR in einer WHERE-Klausel auf, um nach Zeilen zu filtern, die Werte enthalten, die mit einem Muster übereinstimmen. Durch die Verwendung der Funktion können Sie mehrere OR-Bedingungen vermeiden.

Im folgenden Beispiel wird die Tabelle demo2 abgefragt, die Sie zuvor erstellt haben, um Zeilen zurückzugeben, die entweder die Zeichenfolge best oder die Zeichenfolge thespian enthalten. Fügen Sie IS NOT NULL zur Bedingung hinzu, um Zeilen zurückzugeben, die dem Muster entsprechen, also die Zeilen, in denen die Funktion REGEXP_SUBSTR nicht NULL zurückgegeben hat:

SELECT id, string1
  FROM demo2
  WHERE REGEXP_SUBSTR(string1, '(best|thespian)') IS NOT NULL;

+----+------------------------------------------------------+
| ID | STRING1                                              |
|----+------------------------------------------------------|
|  2 | It was the best of times, it was the worst of times. |
|  4 | A thespian theater is nearby.                        |
+----+------------------------------------------------------+

Sie können AND-Bedingungen, um Zeilen zu finden, die mehrere Mustern entsprechen. Die folgende Abfrage gibt zum Beispiel Zeilen zurück, die entweder die Zeichenfolge best oder die Zeichenfolge thespian enthalten und mit der Zeichenfolge It beginnen:

SELECT id, string1
  FROM demo2
  WHERE REGEXP_SUBSTR(string1, '(best|thespian)') IS NOT NULL
    AND REGEXP_SUBSTR(string1, '^It') IS NOT NULL;

+----+------------------------------------------------------
| ID | STRING1                                              |
|----+------------------------------------------------------|
|  2 | It was the best of times, it was the worst of times. |
+----+------------------------------------------------------+