Catégories :: Fonctions de chaîne (expressions régulières)

REGEXP_INSTR¶

Renvoie la position de l’occurrence spécifiée du motif d’expression régulière dans le sujet de la chaîne.

Voir aussi Fonctions de chaîne (expressions régulières).

Syntaxe¶

REGEXP_INSTR( <subject> , <pattern> [ , <position> [ , <occurrence> [ , <option> [ , <regexp_parameters> [ , <group_num> ] ] ] ] ] )

Copy

Arguments¶

Obligatoire :

subject

La chaîne à rechercher pour les correspondances.

pattern

Modèle devant correspondre.

Pour des directives sur la spécification des modèles, voir Fonctions de chaîne (expressions régulières).

Facultatif :

position

Nombre de caractères depuis le début de la chaîne pour lesquels la fonction commence à rechercher des correspondances.

Par défaut : 1 (la recherche d’une correspondance commence au premier caractère à gauche)

occurrence

Spécifie la première occurrence du modèle à partir de laquelle il faut commencer à renvoyer les correspondances.

La fonction ignore les occurrence - 1 premières correspondances. Par exemple, s’il existe 5 correspondances et que vous spécifiez 3 pour l’argument occurrence, la fonction ignore les deux premières correspondances et renvoie les troisième, quatrième et cinquième correspondances.

Par défaut : 1

option

Indique s’il faut renvoyer le décalage du premier caractère du résultat correspondant (0) ou le décalage du premier caractère après la fin du résultat correspondant (1).

Par défaut : 0

regexp_parameters

Chaîne d’un ou plusieurs caractères spécifiant les paramètres utilisés pour la recherche de correspondances. Valeurs prises en charge :

Paramètre	Description
`c`	Correspondance sensible à la casse
`i`	Correspondance non sensible à la casse
`m`	Mode multiligne
`e`	Extraire les sous-correspondances.
`s`	POSIX wildcard character `.` matches `\n`

Par défaut : c

Pour plus de détails, voir Spécification des paramètres de l’expression régulière.

Note

Par défaut, REGEXP_INSTR renvoie le décalage du caractère de début ou de fin pour toute la partie correspondante du sujet. Cependant, si le paramètre e (pour « extraire ») est spécifié, REGEXP_INSTR renvoie le décalage du caractère de début ou de fin pour la partie du sujet qui correspond à la première sous-expression du motif. Si e est spécifié mais qu’un group_num n’est pas également spécifié, le group_num est défini par défaut sur 1 (le premier groupe). S’il n’y a pas de sous-expression dans le motif, REGEXP_INSTR se comporte comme si e n’était pas défini. Pour des exemples utilisant e, voir Exemples dans cette rubrique.

group_num

Le paramètre group_num spécifie le groupe à extraire. Les groupes sont spécifiés à l’aide de parenthèses dans l’expression régulière.

Si la valeur group_num est spécifiée, Snowflake autorise l’extraction même si l’option e n’a pas été spécifiée. L’option e est impliquée.

Snowflake prend en charge jusqu’à 1 024 groupes.

Pour des exemples utilisant group_num, voir Exemples de groupes de capture dans cette rubrique.

Renvoie¶

Renvoie une valeur de type NUMBER.

Si aucune correspondance n’est trouvée, renvoie 0.

Notes sur l’utilisation¶

Les positions sont basées sur 1 et non sur 0. Par exemple, la position de la lettre M dans MAN est 1 et non 0.
Pour des notes d’utilisation supplémentaires, voir Notes générales sur l’utilisation pour les fonctions d’expression régulière.

Détails du classement¶

Arguments with collation specifications are currently not supported.

Exemples¶

Les exemples suivants utilisent la fonction REGEXP_INSTR.

Exemples de base¶

Créer une table et insérer des données :

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

Copy

Recherchez une chaîne correspondante. Dans ce cas, la chaîne est nevermore suivie d’un seul chiffre décimal (par exemple nevermore1) : L’exemple utilise la fonction REGEXP_SUBSTR pour afficher la sous-chaîne correspondante :

SELECT id,
       string1,
       REGEXP_SUBSTR(string1, 'nevermore\\d') AS substring,
       REGEXP_INSTR( string1, 'nevermore\\d') AS position
  FROM demo1
  ORDER BY id;

Copy

+----+-------------------------------------+------------+----------+
| ID | STRING1                             | SUBSTRING  | POSITION |
|----+-------------------------------------+------------+----------|
|  1 | nevermore1, nevermore2, nevermore3. | nevermore1 |        1 |
+----+-------------------------------------+------------+----------+

Recherchez une chaîne correspondante, mais en commençant par le cinquième caractère de la chaîne, plutôt que par le premier caractère de la chaîne :

SELECT id,
       string1,
       REGEXP_SUBSTR(string1, 'nevermore\\d', 5) AS substring,
       REGEXP_INSTR( string1, 'nevermore\\d', 5) AS position
  FROM demo1
  ORDER BY id;

Copy

+----+-------------------------------------+------------+----------+
| ID | STRING1                             | SUBSTRING  | POSITION |
|----+-------------------------------------+------------+----------|
|  1 | nevermore1, nevermore2, nevermore3. | nevermore2 |       13 |
+----+-------------------------------------+------------+----------+

Recherchez une chaîne correspondante, mais recherchez la troisième correspondance plutôt que la première correspondance :

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;

Copy

+----+-------------------------------------+------------+----------+
| ID | STRING1                             | SUBSTRING  | POSITION |
|----+-------------------------------------+------------+----------|
|  1 | nevermore1, nevermore2, nevermore3. | nevermore3 |       25 |
+----+-------------------------------------+------------+----------+

Cette requête est presque identique à la requête précédente, mais celle-ci montre comment utiliser l’argument option pour indiquer si vous voulez la position de l’expression correspondante ou la position du premier caractère après l’expression correspondante :

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;

Copy

+----+-------------------------------------+------------+----------------+----------------+
| ID | STRING1                             | SUBSTRING  | START_POSITION | AFTER_POSITION |
|----+-------------------------------------+------------+----------------+----------------|
|  1 | nevermore1, nevermore2, nevermore3. | nevermore3 |             25 |             35 |
+----+-------------------------------------+------------+----------------+----------------+

Cette requête indique que si vous recherchez une occurrence au-delà de la dernière occurrence réelle, la position renvoyée est 0 :

SELECT id,
       string1,
       REGEXP_SUBSTR(string1, 'nevermore', 1, 4) AS substring,
       REGEXP_INSTR( string1, 'nevermore', 1, 4) AS position
  FROM demo1
  ORDER BY id;

Copy

+----+-------------------------------------+-----------+----------+
| ID | STRING1                             | SUBSTRING | POSITION |
|----+-------------------------------------+-----------+----------|
|  1 | nevermore1, nevermore2, nevermore3. | NULL      |        0 |
+----+-------------------------------------+-----------+----------+

Exemples de groupes de capture¶

Cette section montre comment utiliser la fonctionnalité « groupe » des expressions régulières.

Les premiers exemples de cette section n’utilisent pas de groupes de capture. La section commence par quelques exemples simples, puis continue avec des exemples qui utilisent des groupes de capture.

Ces exemples utilisent les chaînes créées ci-dessous :

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;

Copy

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

Les chaînes ont les caractéristiques suivantes :

La chaîne avec un id de 2 contient plusieurs occurrences du mot « le ».
La chaîne avec un id de 3 contient plusieurs occurrences du mot « the » avec des espaces supplémentaires entre les mots.
La chaîne avec un id de 4 contient la séquence de caractères « the » à l’intérieur de plusieurs mots (« thespian » et « theater »), mais sans le mot « the » seul.

Cet exemple recherche la première occurrence du mot the, suivie d’un ou plusieurs caractères non-mots (par exemple, l’espace séparant les mots), suivis d’un ou plusieurs caractères mots.

Les « caractères Word » incluent non seulement les lettres a-z et A-Z, mais également le trait de soulignement (« _ ») et les chiffres décimaux 0-9, mais pas les espaces, la ponctuation, etc.

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

Copy

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

À partir de la position 1 de la chaîne, recherchez la deuxième occurrence du mot the, suivie d’un ou plusieurs caractères non-mots, suivis d’un ou plusieurs caractères mots.

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;

Copy

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

Cet exemple est similaire à l’exemple précédent, mais ajoute des groupes de capture. Plutôt que de renvoyer la position de l’intégralité de la correspondance, cette requête renvoie uniquement la position du groupe, c’est-à-dire la partie de la sous-chaîne qui correspond à la partie de l’expression régulière entre parenthèses. Dans ce cas, la valeur renvoyée est la position du mot après la deuxième occurrence du mot the.

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;

Copy

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

Si vous spécifiez le paramètre 'e' (extraction) mais ne spécifiez pas group_num, alors group_num prend par défaut la valeur 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;

Copy

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

Si vous spécifiez une valeur group_num, Snowflake suppose que vous souhaitez extraire le fichier, même si vous n’avez pas spécifié 'e' (extraction) comme l’un des paramètres :

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;

Copy

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

Cet exemple montre comment extraire la position du deuxième mot des première, deuxième et troisième correspondances d’un motif de deux mots dans lequel le premier mot est A. Cela montre également qu’en essayant d’aller au-delà du dernier motif, Snowflake renvoie 0.

Créer une table et insérer des données :

CREATE TABLE demo3 (id INT, string1 VARCHAR);
INSERT INTO demo3 (id, string1) VALUES
  (5, 'A MAN A PLAN A CANAL');

Copy

Exécutez la requête :

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;

Copy

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

Cet exemple montre comment récupérer la position des premier, deuxième et troisième groupes au sein de la première occurrence du motif. Dans ce cas, les valeurs renvoyées sont les positions des lettres individuelles du mot MAN.

SELECT id,
       string1,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1,    'e', 1) AS substring1,
       REGEXP_INSTR( string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 0, 'e', 1) AS position1,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1,    'e', 2) AS substring2,
       REGEXP_INSTR( string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 0, 'e', 2) AS position2,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1,    'e', 3) AS substring3,
       REGEXP_INSTR( string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 0, 'e', 3) AS position3
  FROM demo3;

Copy

+----+----------------------+------------+-----------+------------+-----------+------------+-----------+
| ID | STRING1              | SUBSTRING1 | POSITION1 | SUBSTRING2 | POSITION2 | SUBSTRING3 | POSITION3 |
|----+----------------------+------------+-----------+------------+-----------+------------+-----------|
|  5 | A MAN A PLAN A CANAL | M          |         3 | A          |         4 | N          |         5 |
+----+----------------------+------------+-----------+------------+-----------+------------+-----------+

Exemples supplémentaires¶

L’exemple suivant correspond aux occurrences du mot was. La correspondance commence au premier caractère de la chaîne de caractères et renvoie la position du caractère suivant la première occurrence dans la chaîne :

SELECT REGEXP_INSTR('It was the best of times, it was the worst of times',
                    '\\bwas\\b',
                    1,
                    1) AS result;

Copy

+--------+
| RESULT |
|--------|
|      4 |
+--------+

L’exemple suivant renvoie le décalage du premier caractère de la partie de la chaîne qui correspond au modèle. La correspondance commence au premier caractère de la chaîne de caractères et renvoie la première occurrence du modèle :

SELECT REGEXP_INSTR('It was the best of times, it was the worst of times',
                    'the\\W+(\\w+)',
                    1,
                    1,
                    0) AS result;

Copy

+--------+
| RESULT |
|--------|
|      8 |
+--------+

L’exemple suivant est le même que l’exemple précédent, mais utilise le paramètre e pour renvoyer le décalage des caractères de la partie du sujet qui correspond à la première sous-expression du modèle (c’est-à-dire le premier ensemble de caractères après 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;

Copy

+--------+
| RESULT |
|--------|
|     12 |
+--------+

L’exemple suivant correspond aux occurrences de mots se terminant par st précédés de deux caractères alphabétiques ou plus (insensibles à la casse). La correspondance commence au quinzième caractère de la chaîne de caractères et renvoie la position du caractère suivant la première occurrence (le début de worst) dans la chaîne :

SELECT REGEXP_INSTR('It was the best of times, it was the worst of times',
                    '[[:alpha:]]{2,}st',
                    15,
                    1) AS result;

Copy

+--------+
| RESULT |
|--------|
|     38 |
+--------+

Pour exécuter la prochaine série d’exemples, créez une table et insérez des données :

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

Renvoie le décalage du premier caractère de la première correspondance contenant un o minuscule :

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

Copy

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

Renvoie le décalage du premier caractère de la première correspondance contenant un o minuscule, à partir du troisième caractère du sujet :

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

Copy

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

Renvoie le décalage du premier caractère de la troisième correspondance contenant un o minuscule, à partir du troisième caractère du sujet :

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

Copy

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

Renvoie le décalage du dernier caractère de la troisième correspondance contenant un o minuscule, à partir du troisième caractère du sujet :

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

Copy

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

Renvoie le décalage du dernier caractère de la troisième correspondance contenant un o minuscule, à partir du troisième caractère du sujet, avec une correspondance insensible à la casse :

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

Copy

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