Catégories :

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

REGEXP_SUBSTR

Renvoie la sous-chaîne qui correspond à une expression régulière dans une chaîne.

Syntaxe

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

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. La valeur doit être un entier positif.

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

regex_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

Le caractère générique . du mode à une ligne POSIX correspond à \n

Par défaut : c

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

Note

Par défaut, REGEXP_SUBSTR renvoie la totalité de la partie correspondante du sujet. Toutefois, si le paramètre e (pour « extraction ») est spécifié, REGEXP_SUBSTR ne renvoie que la partie du sujet qui correspond au premier groupe 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_SUBSTR se comporte comme si e n’était pas défini. Pour des exemples utilisant e, voir Exemples dans cette rubrique.

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.

Renvoie

La fonction renvoie une valeur de type VARCHAR qui est la sous-chaine correspondante.

La fonction renvoie NULL dans les cas suivants :

  • Aucune correspondance n’est trouvée.

  • L’un des arguments est NULL.

Notes sur l’utilisation

Pour plus d’informations sur l’utilisation d’expressions régulières, voir Fonctions de chaîne (expressions régulières).

Détails du classement

Arguments with collation specifications currently aren’t supported.

Exemples

La documentation de la fonction REGEXP_INSTR contient de nombreux exemples utilisant à la fois REGEXP_SUBSTR et REGEXP_INSTR. Vous voudrez peut-être aussi regarder ces exemples.

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;

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

Les exemples suivants appellent la fonction REGEXP_SUBSTR :

Appeler la fonction REGEXP_SUBSTR dans une liste SELECT

Appelez la fonction REGEXP_SUBSTR dans une liste SELECT pour extraire ou afficher les valeurs qui correspondent à un modèle.

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,
       REGEXP_SUBSTR(string1, 'the\\W+\\w+') AS result
  FROM demo2
  ORDER BY id;

+----+--------------+
| ID | RESULT       |
|----+--------------|
|  2 | the best     |
|  3 | the   string |
|  4 | NULL         |
+----+--------------+

À 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,
       REGEXP_SUBSTR(string1, 'the\\W+\\w+', 1, 2) AS result
  FROM demo2
  ORDER BY id;

+----+-------------+
| ID | RESULT      |
|----+-------------|
|  2 | the worst   |
|  3 | the   extra |
|  4 | NULL        |
+----+-------------+

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

Au lieu de renvoyer l’intégralité de la correspondance, retourne uniquement le « 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 le mot après « le ».

SELECT id,
       REGEXP_SUBSTR(string1, 'the\\W+(\\w+)', 1, 2, 'e', 1) AS result
  FROM demo2
  ORDER BY id;

+----+--------+
| ID | RESULT |
|----+--------|
|  2 | worst  |
|  3 | extra  |
|  4 | NULL   |
+----+--------+

Cet exemple montre comment extraire le 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. Cet exemple montre également qu’en essayant d’aller au-delà du dernier motif, Snowflake renvoie NULL.

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

CREATE OR REPLACE TABLE test_regexp_substr (string1 VARCHAR);;
INSERT INTO test_regexp_substr (string1) VALUES ('A MAN A PLAN A CANAL');

Exécutez la requête :

SELECT REGEXP_SUBSTR(string1, 'A\\W+(\\w+)', 1, 1, 'e', 1) AS result1,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w+)', 1, 2, 'e', 1) AS result2,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w+)', 1, 3, 'e', 1) AS result3,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w+)', 1, 4, 'e', 1) AS result4
  FROM test_regexp_substr;

+---------+---------+---------+---------+
| RESULT1 | RESULT2 | RESULT3 | RESULT4 |
|---------+---------+---------+---------|
| MAN     | PLAN    | CANAL   | NULL    |
+---------+---------+---------+---------+

Cet exemple montre comment récupérer les 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 lettres individuelles du mot MAN.

SELECT REGEXP_SUBSTR(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 'e', 1) AS result1,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 'e', 2) AS result2,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 'e', 3) AS result3
  FROM test_regexp_substr;

+---------+---------+---------+
| RESULT1 | RESULT2 | RESULT3 |
|---------+---------+---------|
| M       | A       | N       |
+---------+---------+---------+

Voici quelques exemples supplémentaires.

Créer une table et insérer 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');

Renvoie la première correspondance contenant un o minuscule en faisant correspondre une limite de mot (\b), suivie de zéro ou de plusieurs caractères de mots (\S), la lettre o, puis zéro ou plusieurs caractères de mot jusqu’à la prochaine limite de mot :

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

+---------------------------------------------+---------+
| BODY                                        | RESULT  |
|---------------------------------------------+---------|
| Hellooo World                               | Hellooo |
| How are you doing today?                    | How     |
| the quick brown fox jumps over the lazy dog | brown   |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | NULL    |
+---------------------------------------------+---------+

Renvoie la première correspondance contenant un o minuscule, à partir du troisième caractère du sujet :

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

+---------------------------------------------+--------+
| BODY                                        | RESULT |
|---------------------------------------------+--------|
| Hellooo World                               | llooo  |
| How are you doing today?                    | you    |
| the quick brown fox jumps over the lazy dog | brown  |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | NULL   |
+---------------------------------------------+--------+

Renvoie la troisième correspondance contenant un o minuscule, à partir du troisième caractère du sujet :

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

+---------------------------------------------+--------+
| BODY                                        | RESULT |
|---------------------------------------------+--------|
| Hellooo World                               | NULL   |
| How are you doing today?                    | today  |
| the quick brown fox jumps over the lazy dog | over   |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | NULL   |
+---------------------------------------------+--------+

Renvoie 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_SUBSTR(body, '\\b\\S*o\\S*\\b', 3, 3, 'i') AS result
  FROM message;

+---------------------------------------------+--------+
| BODY                                        | RESULT |
|---------------------------------------------+--------|
| Hellooo World                               | NULL   |
| How are you doing today?                    | today  |
| the quick brown fox jumps over the lazy dog | over   |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | LIQUOR |
+---------------------------------------------+--------+

Cet exemple montre que vous pouvez explicitement omettre tout paramètre d’expression régulière en spécifiant une chaîne vide.

SELECT body,
       REGEXP_SUBSTR(body, '(H\\S*o\\S*\\b).*', 1, 1, '') AS result
  FROM message;

+---------------------------------------------+--------------------------+
| BODY                                        | RESULT                   |
|---------------------------------------------+--------------------------|
| Hellooo World                               | Hellooo World            |
| How are you doing today?                    | How are you doing today? |
| the quick brown fox jumps over the lazy dog | NULL                     |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | NULL                     |
+---------------------------------------------+--------------------------+

L’exemple suivant illustre des occurrences qui se chevauchent : Créer une table en premier et insérer des données :

CREATE OR REPLACE TABLE overlap (
  id NUMBER,
  a STRING);

INSERT INTO overlap VALUES (1, ',abc,def,ghi,jkl,');
INSERT INTO overlap VALUES (2, ',abc,,def,,ghi,,jkl,');

SELECT * FROM overlap;

+----+----------------------+
| ID | A                    |
|----+----------------------|
|  1 | ,abc,def,ghi,jkl,    |
|  2 | ,abc,,def,,ghi,,jkl, |
+----+----------------------+

Exécutez une requête qui recherche la deuxième occurrence du modèle suivant dans chaque ligne : un signe de ponctuation suivi de chiffres et de lettres, suivi d’un signe de ponctuation.

SELECT id,
       REGEXP_SUBSTR(a,'[[:punct:]][[:alnum:]]+[[:punct:]]', 1, 2) AS result
  FROM overlap;

+----+--------+
| ID | RESULT |
|----+--------|
|  1 | ,ghi,  |
|  2 | ,def,  |
+----+--------+

L’exemple suivant crée un objet JSON à partir d’un journal d’accès du serveur HTTP Apache à l’aide de la correspondance de modèle et de la concaténation. Créer une table en premier et insérer des données :

CREATE OR REPLACE TABLE test_regexp_log (logs VARCHAR);

INSERT INTO test_regexp_log (logs) VALUES
  ('127.0.0.1 - - [10/Jan/2018:16:55:36 -0800] "GET / HTTP/1.0" 200 2216'),
  ('192.168.2.20 - - [14/Feb/2018:10:27:10 -0800] "GET /cgi-bin/try/ HTTP/1.0" 200 3395');

SELECT * from test_regexp_log

+-------------------------------------------------------------------------------------+
| LOGS                                                                                |
|-------------------------------------------------------------------------------------|
| 127.0.0.1 - - [10/Jan/2018:16:55:36 -0800] "GET / HTTP/1.0" 200 2216                |
| 192.168.2.20 - - [14/Feb/2018:10:27:10 -0800] "GET /cgi-bin/try/ HTTP/1.0" 200 3395 |
+-------------------------------------------------------------------------------------+

Exécuter une requête :

SELECT '{ "ip_addr":"'
       || REGEXP_SUBSTR (logs,'\\b\\d{1,3}\.\\d{1,3}\.\\d{1,3}\.\\d{1,3}\\b')
       || '", "date":"'
       || REGEXP_SUBSTR (logs,'([\\w:\/]+\\s[+\-]\\d{4})')
       || '", "request":"'
       || REGEXP_SUBSTR (logs,'\"((\\S+) (\\S+) (\\S+))\"', 1, 1, 'e')
       || '", "status":"'
       || REGEXP_SUBSTR (logs,'(\\d{3}) \\d+', 1, 1, 'e')
       || '", "size":"'
       || REGEXP_SUBSTR (logs,'\\d{3} (\\d+)', 1, 1, 'e')
       || '"}' as Apache_HTTP_Server_Access
  FROM test_regexp_log;

+-----------------------------------------------------------------------------------------------------------------------------------------+
| APACHE_HTTP_SERVER_ACCESS                                                                                                               |
|-----------------------------------------------------------------------------------------------------------------------------------------|
| { "ip_addr":"127.0.0.1", "date":"10/Jan/2018:16:55:36 -0800", "request":"GET / HTTP/1.0", "status":"200", "size":"2216"}                |
| { "ip_addr":"192.168.2.20", "date":"14/Feb/2018:10:27:10 -0800", "request":"GET /cgi-bin/try/ HTTP/1.0", "status":"200", "size":"3395"} |
+-----------------------------------------------------------------------------------------------------------------------------------------+

Appeler la fonction REGEXP_SUBSTR dans une clause WHERE

Appelez la fonction REGEXP_SUBSTR dans une clause WHERE pour filtrer les lignes contenant des valeurs qui correspondent à un modèle. En utilisant la fonction, vous pouvez éviter plusieurs conditions OR.

L’exemple suivant interroge la table demo2 que vous avez créée précédemment pour renvoyer des lignes comprenant soit la chaîne best ou la chaîne thespian. Ajoutez IS NOT NULL à la condition pour renvoyer les lignes qui correspondent au modèle. C’est-à-dire les lignes où la fonction REGEXP_SUBSTR n’a pas renvoyé NULL :

SELECT id, string1
  FROM demo2
  WHERE REGEXP_SUBSTR(string1, '(best|thespian)') IS NOT NULL;

+----+------------------------------------------------------+
| ID | STRING1                                              |
|----+------------------------------------------------------|
|  2 | It was the best of times, it was the worst of times. |
|  4 | A thespian theater is nearby.                        |
+----+------------------------------------------------------+

Vous pouvez utiliser les conditions AND pour trouver des lignes qui correspondent à plusieurs modèles. Par exemple, la requête suivante renvoie des lignes comprenant soit la chaîne best ou la chaîne thespian et commencent par la chaîne It:

SELECT id, string1
  FROM demo2
  WHERE REGEXP_SUBSTR(string1, '(best|thespian)') IS NOT NULL
    AND REGEXP_SUBSTR(string1, '^It') IS NOT NULL;

+----+------------------------------------------------------
| ID | STRING1                                              |
|----+------------------------------------------------------|
|  2 | It was the best of times, it was the worst of times. |
+----+------------------------------------------------------+