Kategorien:

Zeichenfolgenfunktionen (reguläre Ausdrücke)

REGEXP_INSTR

Gibt die Position des angegebenen Vorkommens des Musters des regulären Ausdrucks im Zeichenfolgensubjekt zurück. Wenn keine Übereinstimmung gefunden wird, wird 0 zurückgegeben.

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

Syntax

REGEXP_INSTR( <subject> , <pattern> [ , <position> [ , <occurrence> [ , <option> [ , <regexp_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

Option

Gibt an, ob der Offset des ersten Zeichens der Übereinstimmung (0) oder der Offset des ersten Zeichens nach dem Ende der Übereinstimmung (1) zurückgegeben werden soll.

Standard: 0

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_INSTR für den gesamten übereinstimmenden Teil des Subjekts den Offset des Anfangs- oder Endzeichens zurück. Wenn jedoch der Parameter e (für „extrahieren“) angegeben ist, gibt REGEXP_INSTR den Offset des Anfangs- oder Endzeichens für den Teil des Subjekts zurück, der mit dem ersten Teilausdruck 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_INSTR 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. Die Option 'e' ist impliziert.

Snowflake unterstützt bis zu 1.024 Gruppen.

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

Nutzungshinweise

  • Positionen sind 1-basiert, nicht 0-basiert. Beispielsweise ist die Position des Buchstabens „M“ in „MAN“ 1 und nicht 0.

  • 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

Grundlegende Beispiele

In den nächsten Beispielen werden folgende Daten verwendet:

CREATE TABLE demo1 (id INT, string1 VARCHAR);
INSERT INTO demo1 (id, string1) VALUES 
    (1, 'nevermore1, nevermore2, nevermore3.')
    ;

Suchen Sie nach einer übereinstimmenden Zeichenfolge. In diesem Fall ist die Zeichenfolge „nevermore“, gefolgt von einer einzelnen Dezimalstelle, z. B. „nevermore1“:

select id, string1,
      regexp_substr(string1, 'nevermore\\d') AS "SUBSTRING", 
      regexp_instr( string1, 'nevermore\\d') AS "POSITION"
    from demo1
    order by id;
+----+-------------------------------------+------------+----------+
| ID | STRING1                             | SUBSTRING  | POSITION |
|----+-------------------------------------+------------+----------|
|  1 | nevermore1, nevermore2, nevermore3. | nevermore1 |        1 |
+----+-------------------------------------+------------+----------+

Suchen Sie nach einer übereinstimmenden Zeichenfolge, beginnen Sie jedoch mit dem 5. Zeichen der Zeichenfolge und nicht mit dem 1. Zeichen der Zeichenfolge:

select id, string1,
      regexp_substr(string1, 'nevermore\\d', 5) AS "SUBSTRING", 
      regexp_instr( string1, 'nevermore\\d', 5) AS "POSITION"
    from demo1
    order by id;
+----+-------------------------------------+------------+----------+
| ID | STRING1                             | SUBSTRING  | POSITION |
|----+-------------------------------------+------------+----------|
|  1 | nevermore1, nevermore2, nevermore3. | nevermore2 |       13 |
+----+-------------------------------------+------------+----------+

Suchen Sie nach einer übereinstimmenden Zeichenfolge, suchen Sie jedoch nach der 3. Übereinstimmung und nicht nach der 1. Übereinstimmung:

select id, string1,
      regexp_substr(string1, 'nevermore\\d', 1, 3) AS "SUBSTRING", 
      regexp_instr( string1, 'nevermore\\d', 1, 3) AS "POSITION"
    from demo1
    order by id;
+----+-------------------------------------+------------+----------+
| ID | STRING1                             | SUBSTRING  | POSITION |
|----+-------------------------------------+------------+----------|
|  1 | nevermore1, nevermore2, nevermore3. | nevermore3 |       25 |
+----+-------------------------------------+------------+----------+

Diese Abfrage ist nahezu identisch mit der vorherigen Abfrage, zeigt jedoch, wie Sie mit dem Parameter Option angeben, ob Sie die Position des übereinstimmenden Ausdrucks oder die Position des ersten Zeichens nach dem übereinstimmenden Ausdruck erhalten möchten:

select id, string1,
       regexp_substr(string1, 'nevermore\\d', 1, 3) AS "SUBSTRING", 
       regexp_instr( string1, 'nevermore\\d', 1, 3, 0) AS "START_POSITION",
       regexp_instr( string1, 'nevermore\\d', 1, 3, 1) AS "AFTER_POSITION"
    from demo1
    order by id;
+----+-------------------------------------+------------+----------------+----------------+
| ID | STRING1                             | SUBSTRING  | START_POSITION | AFTER_POSITION |
|----+-------------------------------------+------------+----------------+----------------|
|  1 | nevermore1, nevermore2, nevermore3. | nevermore3 |             25 |             35 |
+----+-------------------------------------+------------+----------------+----------------+

Diese Abfrage zeigt, dass bei der Suche nach einem Vorkommen, das über das letzte tatsächliche Vorkommen hinausgeht, die zurückgegebene Position 0 ist:

select id, string1, 
       regexp_substr(string1, 'nevermore', 1, 4) AS "SUBSTRING",
       regexp_instr( string1, 'nevermore', 1, 4) AS "POSITION"
    from demo1
    order by id;
+----+-------------------------------------+-----------+----------+
| ID | STRING1                             | SUBSTRING | POSITION |
|----+-------------------------------------+-----------+----------|
|  1 | nevermore1, nevermore2, nevermore3. | NULL      |        0 |
+----+-------------------------------------+-----------+----------+

Beispiele für Erfassungsgruppen

In diesem Abschnitt erfahren Sie, wie Sie die „Gruppen“-Funktion von regulären Ausdrücken verwenden.

In den ersten Beispielen in diesem Abschnitt werden keine Erfassungsgruppen verwendet. Der Abschnitt beginnt mit einigen einfachen Beispielen und setzt sich dann mit Beispielen fort, die Erfassungsgruppen verwenden.

In den Beispielen werden die unten erstellten 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 (zum Beispiel das Leerzeichen, das Wörter trennt)

  • 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, string1,
       regexp_substr(string1, 'the\\W+\\w+') as "SUBSTRING",
       regexp_instr(string1, 'the\\W+\\w+') as "POSITION"
    from demo2
    order by id;
+----+-------------------------------------------------------------+--------------+----------+
| ID | STRING1                                                     | SUBSTRING    | POSITION |
|----+-------------------------------------------------------------+--------------+----------|
|  2 | It was the best of times, it was the worst of times.        | the best     |        8 |
|  3 | In    the   string   the   extra   spaces  are   redundant. | the   string |        7 |
|  4 | A thespian theater is nearby.                               | NULL         |        0 |
+----+-------------------------------------------------------------+--------------+----------+

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, string1, 
       regexp_substr(string1, 'the\\W+\\w+', 1, 2) as "SUBSTRING",
       regexp_instr(string1, 'the\\W+\\w+', 1, 2) as "POSITION"
    from demo2
    order by id;
+----+-------------------------------------------------------------+-------------+----------+
| ID | STRING1                                                     | SUBSTRING   | POSITION |
|----+-------------------------------------------------------------+-------------+----------|
|  2 | It was the best of times, it was the worst of times.        | the worst   |       34 |
|  3 | In    the   string   the   extra   spaces  are   redundant. | the   extra |       22 |
|  4 | A thespian theater is nearby.                               | NULL        |        0 |
+----+-------------------------------------------------------------+-------------+----------+

Dieses Beispiel ähnelt dem vorherigen Beispiel, fügt jedoch Erfassungsgruppen hinzu. Anstatt die Position der gesamten Übereinstimmung zurückzugeben, gibt diese Abfrage nur die Position der „Gruppe“ zurück, d. h. den Teil der Teilzeichenfolge, der mit dem Teil des regulären Ausdrucks in Klammern übereinstimmt. In diesem Fall sollte der zurückgegebene Wert die Position des Wortes nach „the“ sein.

select id, string1,
       regexp_substr(string1, 'the\\W+(\\w+)', 1, 2,    'e', 1) as "SUBSTRING",
       regexp_instr( string1, 'the\\W+(\\w+)', 1, 2, 0, 'e', 1) as "POSITION"
    from demo2
    order by id;
+----+-------------------------------------------------------------+-----------+----------+
| ID | STRING1                                                     | SUBSTRING | POSITION |
|----+-------------------------------------------------------------+-----------+----------|
|  2 | It was the best of times, it was the worst of times.        | worst     |       38 |
|  3 | In    the   string   the   extra   spaces  are   redundant. | extra     |       28 |
|  4 | A thespian theater is nearby.                               | NULL      |        0 |
+----+-------------------------------------------------------------+-----------+----------+

Wenn Sie den Parameter 'e' („extrahieren“) angeben, aber nicht die Guppennummer, ist die Standardeinstellung für die Gruppennummer 1:

select id, string1,
       regexp_substr(string1, 'the\\W+(\\w+)', 1, 2,    'e') as "SUBSTRING",
       regexp_instr( string1, 'the\\W+(\\w+)', 1, 2, 0, 'e') as "POSITION"
    from demo2
    order by id;
+----+-------------------------------------------------------------+-----------+----------+
| ID | STRING1                                                     | SUBSTRING | POSITION |
|----+-------------------------------------------------------------+-----------+----------|
|  2 | It was the best of times, it was the worst of times.        | worst     |       38 |
|  3 | In    the   string   the   extra   spaces  are   redundant. | extra     |       28 |
|  4 | A thespian theater is nearby.                               | NULL      |        0 |
+----+-------------------------------------------------------------+-----------+----------+

Wenn Sie eine Gruppennummer angeben, geht Snowflake davon aus, dass Sie extrahieren möchten, auch wenn Sie 'e' („extrahieren“) nicht als einen der Parameter angegeben haben:

select id, string1,
       regexp_substr(string1, 'the\\W+(\\w+)', 1, 2,    '', 1) as "SUBSTRING",
       regexp_instr( string1, 'the\\W+(\\w+)', 1, 2, 0, '', 1) as "POSITION"
    from demo2
    order by id;
+----+-------------------------------------------------------------+-----------+----------+
| ID | STRING1                                                     | SUBSTRING | POSITION |
|----+-------------------------------------------------------------+-----------+----------|
|  2 | It was the best of times, it was the worst of times.        | worst     |       38 |
|  3 | In    the   string   the   extra   spaces  are   redundant. | extra     |       28 |
|  4 | A thespian theater is nearby.                               | NULL      |        0 |
+----+-------------------------------------------------------------+-----------+----------+

In diesem Beispiel wird gezeigt, wie die Position des zweiten Wortes 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, 0 zurückgibt.

CREATE TABLE demo3 (id INT, string1 VARCHAR);
INSERT INTO demo3 (id, string1) VALUES
    (5, 'A MAN A PLAN A CANAL')
    ;
select id, string1,
    regexp_substr(string1, 'A\\W+(\\w+)', 1, 1,    'e', 1) as "SUBSTRING1",
    regexp_instr( string1, 'A\\W+(\\w+)', 1, 1, 0, 'e', 1) as "POSITION1",
    regexp_substr(string1, 'A\\W+(\\w+)', 1, 2,    'e', 1) as "SUBSTRING2",
    regexp_instr( string1, 'A\\W+(\\w+)', 1, 2, 0, 'e', 1) as "POSITION2",
    regexp_substr(string1, 'A\\W+(\\w+)', 1, 3,    'e', 1) as "SUBSTRING3",
    regexp_instr( string1, 'A\\W+(\\w+)', 1, 3, 0, 'e', 1) as "POSITION3",
    regexp_substr(string1, 'A\\W+(\\w+)', 1, 4,    'e', 1) as "SUBSTRING4",
    regexp_instr( string1, 'A\\W+(\\w+)', 1, 4, 0, 'e', 1) as "POSITION4"
    from demo3;
+----+----------------------+------------+-----------+------------+-----------+------------+-----------+------------+-----------+
| ID | STRING1              | SUBSTRING1 | POSITION1 | SUBSTRING2 | POSITION2 | SUBSTRING3 | POSITION3 | SUBSTRING4 | POSITION4 |
|----+----------------------+------------+-----------+------------+-----------+------------+-----------+------------+-----------|
|  5 | A MAN A PLAN A CANAL | MAN        |         3 | PLAN       |         9 | CANAL      |        16 | NULL       |         0 |
+----+----------------------+------------+-----------+------------+-----------+------------+-----------+------------+-----------+

Dieses Beispiel zeigt, wie die Position der ersten, zweiten und dritten Gruppe innerhalb des ersten Auftretens des Musters abgerufen wird. In diesem Fall sind die zurückgegebenen Werte die Positionen der einzelnen Buchstaben des Wortes MAN.

select id, string1, 
    regexp_substr(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1,    'e', 1) as "SUBSTR1",
    regexp_instr( string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 0, 'e', 1) as "POS1",
    regexp_substr(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1,    'e', 2) as "SUBSTR2",
    regexp_instr( string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 0, 'e', 2) as "POS2",
    regexp_substr(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1,    'e', 3) as "SUBSTR3",
    regexp_instr( string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 0, 'e', 3) as "POS3"
    from demo3;
+----+----------------------+---------+------+---------+------+---------+------+
| ID | STRING1              | SUBSTR1 | POS1 | SUBSTR2 | POS2 | SUBSTR3 | POS3 |
|----+----------------------+---------+------+---------+------+---------+------|
|  5 | A MAN A PLAN A CANAL | M       |    3 | A       |    4 | N       |    5 |
+----+----------------------+---------+------+---------+------+---------+------+

Zusätzliche Beispiele

Im folgenden Beispiel werden Vorkommen des Worts was abgeglichen. Der Abgleich beginnt beim 1. Zeichen in der Zeichenfolge und gibt die Position des Zeichens in der Zeichenfolge nach dem ersten Auftreten zurück:

select regexp_instr('It was the best of times, it was the worst of times', '\\bwas\\b', 1, 1) as "result" from dual;

+--------+
| result |
|--------|
|      4 |
+--------+

Im folgenden Beispiel wird der Offset des ersten Zeichens des Teils der Zeichenfolge zurückgegeben, der mit dem Muster übereinstimmt. Der Abgleich beginnt beim 1. Zeichen in der Zeichenfolge und gibt das erste Vorkommen des Musters zurück:

select regexp_instr('It was the best of times, it was the worst of times', 'the\\W+(\\w+)',1,1,0) as "result" from dual;

+--------+
| result |
|--------|
|      8 |
+--------+

Das folgende Beispiel ist dasselbe wie das vorherige Beispiel, verwendet jedoch den Parameter e, um den Zeichenoffset für den Teil des Subjekts zurückzugeben, der mit dem ersten Unterausdruck im Muster übereinstimmt (d. h. die erste Menge von Wortzeichen nach the):

select regexp_instr('It was the best of times, it was the worst of times', 'the\\W+(\\w+)',1,1,0,'e') as "result" from dual;

+--------+
| result |
|--------|
|     12 |
+--------+

Im folgenden Beispiel werden Vorkommen von Wörtern abgeglichen, die auf st enden, mit mindestens zwei alphabetischen Zeichen davor (ohne Berücksichtigung der Groß- und Kleinschreibung). Der Abgleich beginnt beim 15. Zeichen in der Zeichenfolge und gibt die Position des Zeichens in der Zeichenfolge nach dem ersten Vorkommen zurück:

select regexp_instr('It was the best of times, it was the worst of times', '[[:alpha:]]{2,}st', 15, 1) as "result" from dual;

+--------+
| result |
|--------|
|     38 |
+--------+

Bereiten Sie die Beispiele vor:

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

Rückgabe des Offsets des ersten Zeichens in der ersten Übereinstimmung:

select body, regexp_instr(body, '\\b\\S*o\\S*\\b') as result from message;

---------------------------------------------+-----------------------------------+
                    body                     | result                            |
---------------------------------------------+-----------------------------------+
 Hellooo World                               | 1                                 |
 How are you doing today?                    | 1                                 |
 the quick brown fox jumps over the lazy dog | 11                                |
 PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | 0                                 |
---------------------------------------------+-----------------------------------+

Erstes Zeichen in der ersten Übereinstimmung, beginnend mit dem dritten Zeichen im Subjekt:

select body, regexp_instr(body, '\\b\\S*o\\S*\\b', 3) as result from message;

---------------------------------------------+--------------------------------------+
                    body                     | result                               |
---------------------------------------------+--------------------------------------+
 Hellooo World                               | 3                                    |
 How are you doing today?                    | 9                                    |
 the quick brown fox jumps over the lazy dog | 11                                   |
 PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | 0                                    |
---------------------------------------------+--------------------------------------+

Erstes Zeichen im dritten Spiel, beginnend mit dem dritten Zeichen im Subjekt:

select body, regexp_instr(body, '\\b\\S*o\\S*\\b', 3, 3) as result from message;

---------------------------------------------+-----------------------------------------+
                    body                     | result                                  |
---------------------------------------------+-----------------------------------------+
 Hellooo World                               | 0                                       |
 How are you doing today?                    | 19                                      |
 the quick brown fox jumps over the lazy dog | 27                                      |
 PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | 0                                       |
---------------------------------------------+-----------------------------------------+

Letztes Zeichen im dritten Spiel, beginnend mit dem dritten Zeichen im Subjekt:

select body, regexp_instr(body, '\\b\\S*o\\S*\\b', 3, 3, 1) as result from message;

---------------------------------------------+--------------------------------------------+
                    body                     | result                                     |
---------------------------------------------+--------------------------------------------+
 Hellooo World                               | 0                                          |
 How are you doing today?                    | 24                                         |
 the quick brown fox jumps over the lazy dog | 31                                         |
 PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | 0                                          |
---------------------------------------------+--------------------------------------------+

Letztes Zeichen in der dritten Übereinstimmung, beginnend mit dem dritten Zeichen im Subjekt, Übereinstimmung ohne Berücksichtigung der Groß-/Kleinschreibung:

select body, regexp_instr(body, '\\b\\S*o\\S*\\b', 3, 3, 1, 'i') as result from message;

---------------------------------------------+-------------------------------------------------+
                    body                     | result                                          |
---------------------------------------------+-------------------------------------------------+
 Hellooo World                               | 0                                               |
 How are you doing today?                    | 24                                              |
 the quick brown fox jumps over the lazy dog | 31                                              |
 PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | 35                                              |
---------------------------------------------+-------------------------------------------------+
select body, regexp_instr(body, '\\S*(o)\\S*\\b', 1, 1, 0, 'i') as result from message;

---------------------------------------------+-------------------------------------------------+
                    body                     | result                                          |
---------------------------------------------+-------------------------------------------------+
 Hellooo World                               | 1                                               |
 How are you doing today?                    | 1                                               |
 the quick brown fox jumps over the lazy dog | 11                                              |
 PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | 9                                               |
---------------------------------------------+-------------------------------------------------+
select body, regexp_instr(body, '\\S*(o)\\S*\\b', 1, 1, 0, 'ie') as result from message;

---------------------------------------------+--------------------------------------------------+
                    body                     | result                                           |
---------------------------------------------+--------------------------------------------------+
 Hellooo World                               | 7                                                |
 How are you doing today?                    | 2                                                |
 the quick brown fox jumps over the lazy dog | 13                                               |
 PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | 10                                               |
---------------------------------------------+--------------------------------------------------+