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> ] ] ] ] ] )

Copy

Argumente¶

Benötigt:

subject: Die Zeichenfolge, nach der gesucht werden soll.
pattern: 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)

occurrence

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

regexp_parameters

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 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_INSTR so, als wäre e nicht gesetzt. Beispiele, die e verwenden, finden Sie im Abschnitt Beispiele (unter diesem Thema).

group_num

Der Parameter 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).

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

Wenn Sie den Parameter 'e' („extrahieren“) angeben, aber nicht die group_num, 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 |
+----+-------------------------------------------------------------+-----------+----------+

Copy

Wenn Sie eine group_num 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 |
+----+-------------------------------------------------------------+-----------+----------+

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

Im folgenden Beispiel werden Vorkommen von Wörtern abgeglichen, die auf st enden, mit mindestens zwei alphabetischen Zeichen davor (ohne Berücksichtigung der Groß-/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 |
+--------+

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy