Kategorien:

Zeichenfolgenfunktionen (reguläre Ausdrücke)

REGEXP_SUBSTR

Gibt die Teilzeichenfolge zurück, die mit einem regulären Ausdruck in einer Zeichenfolge übereinstimmt. Wenn keine Übereinstimmung gefunden wird, wird NULL zurückgegeben.

Siehe auch Zeichenfolgenfunktionen (reguläre Ausdrücke).

Syntax

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

Argumente

Benötigt:

Subjekt

Die Zeichenfolge, nach der gesucht werden soll.

Muster

Abzugleichendes Muster.

Optional:

Position

Anzahl der Zeichen ab dem Anfang der Zeichenfolge, an dem die Funktion mit der Suche nach Übereinstimmungen beginnt.

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

Vorkommen

Gibt an, welches Vorkommen des Musters abgeglichen werden soll. Die Funktion überspringt die ersten occurrence - 1 Übereinstimmungen.

Standard: 1

RegAusdr-Parameter

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

  • c: Groß-/Kleinschreibung wird unterschieden.

  • i: Groß-/Kleinschreibung wird nicht unterschieden.

  • m: Mehrzeiliger Modus.

  • e: Teilübereinstimmungen werden extrahiert.

  • s: Der Platzhalter „.“ stimmt auch mit Zeilenumbruchzeichen überein.

Weitere Informationen dazu finden Sie in der Dokumentation zu Parameter für reguläre Ausdrücke.

Standard: c

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, aber keine Gruppennummer angegeben ist, ist die Gruppennummer 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).

Gruppennummer

Der Parameter Gruppennummer gibt an, welche Gruppe extrahiert werden soll. Gruppen werden im regulären Ausdruck mithilfe von Klammern angegeben.

Wenn eine Gruppennummer 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 Gruppennummer verwenden, finden Sie im Abschnitt Beispiele (unter diesem Thema).

Nutzungshinweise

  • Klammern (( )) und eckige Klammern ([ ]) müssen derzeit mit doppelten Escape-Zeichen versehen werden, damit sie als Literalzeichenfolgen analysiert werden.

    Beispiel:

    SELECT REGEXP_SUBSTR('Customers - (NY)','\\([[:alnum:]\-]+\\)') as customers;
    
      +-----------+
      | CUSTOMERS |
      |-----------|
      | (NY)      |
      +-----------+
    
  • Weitere Hinweise zur Verwendung der Funktionen für reguläre Ausdrücke finden Sie unter Allgemeine Nutzungshinweise.

Sortierungsdetails

Arguments with collation specifications are currently not 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 folgenden Beispielen wird die folgende Tabelle mit Zeichenfolgen verwendet:

CREATE TABLE demo2 (id INT, string1 VARCHAR);;
INSERT INTO demo2 (id, string1) VALUES 
    -- A string with multiple occurrences of the word "the".
    (2, 'It was the best of times, it was the worst of times.'),
    -- A string with multiple occurrences of the word "the" and with extra
    -- blanks between words.
    (3, 'In    the   string   the   extra   spaces  are   redundant.'),
    -- A string with the character sequence "the" inside multiple words 
    -- ("thespian" and "theater"), but without the word "the" by itself.
    (4, 'A thespian theater is nearby.')
    ;

Das nächste Beispiel sucht nach:

  • dem Wort „the“

  • gefolgt von einem oder mehreren Nicht-Wort-Zeichen

  • gefolgt von einem oder mehreren Wortzeichen.

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

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 2. Vorkommen von

  • dem Wort „the“

  • gefolgt von einem oder mehreren Nicht-Wort-Zeichen

  • gefolgt von einem oder mehreren Wortzeichen.

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 2. Vorkommen von

  • dem Wort „the“

  • gefolgt von einem oder mehreren Nicht-Wort-Zeichen

  • gefolgt von einem oder mehreren Wortzeichen.

Anstatt die gesamte Übereinstimmung zurückzugeben, wird nur die „Gruppe“ zurückgegeben (d. h. der Teil der Teilzeichenfolge, 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. Dies zeigt auch, dass Snowflake beim Versuch, über das letzte Muster hinauszugehen, NULL zurückgibt.

CREATE TABLE demo3 (id INT, string1 VARCHAR);;
INSERT INTO demo3 (id, string1) VALUES
    (5, 'A MAN A PLAN A CANAL')
    ;
select id, 
    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 demo3;
+----+---------+---------+---------+---------+
| ID | RESULT1 | RESULT2 | RESULT3 | RESULT4 |
|----+---------+---------+---------+---------|
|  5 | 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 id, 
    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 demo3;
+----+---------+---------+---------+
| ID | RESULT1 | RESULT2 | RESULT3 |
|----+---------+---------+---------|
|  5 | M       | A       | N       |
+----+---------+---------+---------+

Hier sind einige zusätzliche Beispiele.

-- Prepare example
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');
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                                     |
+---------------------------------------------+------------------------------------------+
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                                      |
+---------------------------------------------+-------------------------------------------+
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                                         |
+---------------------------------------------+----------------------------------------------+
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                                               |
+---------------------------------------------+----------------------------------------------------+
SELECT body, REGEXP_SUBSTR(body, '(H\\S*o\\S*\\b) .*', 1, 1, 'e') AS result FROM message;

+---------------------------------------------+------------------------------------------------------+
| BODY                                        | result                                               |
|---------------------------------------------+------------------------------------------------------|
| Hellooo World                               | Hellooo                                              |
| How are you doing today?                    | How                                                  |
| 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:

-- Prepare example
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;

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:

-- Prepare example
CREATE OR REPLACE TABLE log (logs varchar);

INSERT INTO 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
  '{ "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 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"} |
+-----------------------------------------------------------------------------------------------------------------------------------------+