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. Si aucune correspondance n’est trouvée, renvoie 0.

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.

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 l’occurrence du motif devant correspondre. La fonction ignore les occurrence - 1 premières 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 d’expression régulière utilisés pour la recherche de correspondances. Les valeurs prises en charge sont les suivantes :

  • c : sensible à la casse.

  • i : insensible à la casse.

  • m : mode multiligne.

  • e : extrait les sous-correspondances.

  • s : le caractère générique “.” correspond également à une nouvelle ligne.

Pour plus de détails, voir la documentation sur les paramètres des expressions régulières.

Par défaut : c

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 un group_num est spécifié, Snowflake autorise l’extraction même si l’option 'e' n’a pas été spécifiée. Le 'e' est impliqué.

Snowflake prend en charge jusqu’à 1 024 groupes.

Pour des exemples utilisant group_num, voir les exemples de cette rubrique.

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

Exemples de base

Les exemples suivants utilisent ces données :

CREATE 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 « plus jamais » suivie d’un seul chiffre décimal (par exemple « plus jamais1 ») :

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

Recherchez une chaîne correspondante, mais en commençant par le 5e caractère de la chaîne, plutôt que par le 1er 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;
+----+-------------------------------------+------------+----------+
| ID | STRING1                             | SUBSTRING  | POSITION |
|----+-------------------------------------+------------+----------|
|  1 | nevermore1, nevermore2, nevermore3. | nevermore2 |       13 |
+----+-------------------------------------+------------+----------+
Copy

Recherchez une chaîne correspondante, mais recherchez la 3e correspondance plutôt que la 1re 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;
+----+-------------------------------------+------------+----------+
| ID | STRING1                             | SUBSTRING  | POSITION |
|----+-------------------------------------+------------+----------|
|  1 | nevermore1, nevermore2, nevermore3. | nevermore3 |       25 |
+----+-------------------------------------+------------+----------+
Copy

Cette requête est presque identique à la requête précédente, mais cela montre comment utiliser le paramètre 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;
+----+-------------------------------------+------------+----------------+----------------+
| ID | STRING1                             | SUBSTRING  | START_POSITION | AFTER_POSITION |
|----+-------------------------------------+------------+----------------+----------------|
|  1 | nevermore1, nevermore2, nevermore3. | nevermore3 |             25 |             35 |
+----+-------------------------------------+------------+----------------+----------------+
Copy

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;
+----+-------------------------------------+-----------+----------+
| ID | STRING1                             | SUBSTRING | POSITION |
|----+-------------------------------------+-----------+----------|
|  1 | nevermore1, nevermore2, nevermore3. | NULL      |        0 |
+----+-------------------------------------+-----------+----------+
Copy

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 utilisant des groupes de capture.

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

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

L’exemple suivant recherche :

  • le mot « le »

  • suivi d’un ou de plusieurs caractères autres que des mots (par exemple, les espaces séparant les mots)

  • suivis d’un ou de plusieurs caractères de 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;
+----+-------------------------------------------------------------+--------------+----------+
| 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

À partir de la position 1 de la chaîne, recherchez la 2e occurrence de

  • le mot « le »

  • suivi d’un ou plusieurs caractères autres que des mots

  • suivis d’un ou de plusieurs caractères de 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;
+----+-------------------------------------------------------------+-------------+----------+
| 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

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 doit être la position du mot après « le ».

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

Si vous spécifiez le paramètre 'e' (« extraction ») mais ne spécifiez pas le group_num, le paramètre num_groupe est par défaut 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

Si vous spécifiez un 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;
+----+-------------------------------------------------------------+-----------+----------+
| 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

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.

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

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

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" from dual;

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

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" from dual;

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

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" from dual;

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

L’exemple suivant correspond aux occurrences de mots se terminant par st précédés de 2 caractères alphabétiques ou plus (insensibles à la casse). La correspondance commence au 15e 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', '[[:alpha:]]{2,}st', 15, 1) as "result" from dual;

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

Préparer les exemples :

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

Renvoyer le décalage du premier caractère de la première correspondance :

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

Premier caractère de la première correspondance, en commençant par le troisième caractère du sujet :

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

Premier caractère de la troisième correspondance, en commençant par le troisième caractère du sujet :

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

Dernier caractère de la troisième correspondance, en commençant par le troisième caractère du sujet :

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

Dernier caractère de la troisième correspondance, en commençant par le troisième caractère du sujet, correspondance insensible à la casse :

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
Langage: Français