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

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

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.

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

regex_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_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

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.

Renvoie

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

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 are currently not 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.

Les exemples suivants utilisent la table des chaînes créée 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.')
    ;

L’exemple suivant recherche :

  • le mot « le »

  • suivi d’un ou plusieurs caractères autres que des 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, 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 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, 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 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.

Au lieu de renvoyer l’intégralité de la correspondance, ne retournez que 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. Cela montre également qu’en essayant d’aller au-delà du dernier motif, Snowflake renvoie NULL.

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

select id, 
    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 demo3;
+----+---------+---------+---------+---------+
| ID | RESULT1 | RESULT2 | RESULT3 | RESULT4 |
|----+---------+---------+---------+---------|
|  5 | 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 id, 
    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 demo3;
+----+---------+---------+---------+
| ID | RESULT1 | RESULT2 | RESULT3 |
|----+---------+---------+---------|
|  5 | M       | A       | N       |
+----+---------+---------+---------+

Voici quelques exemples supplémentaires.

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

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

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

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

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

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

+---------------------------------------------+------------------------------------------------------+
| BODY                                        | result                                               |
|---------------------------------------------+------------------------------------------------------|
| Hellooo World                               | Hellooo                                              |
| How are you doing today?                    | How                                                  |
| 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 :

-- Prepare example
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;

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 :

-- Prepare example
CREATE OR REPLACE TABLE log (logs varchar);

INSERT INTO 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
  '{ "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 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"} |
+-----------------------------------------------------------------------------------------------------------------------------------------+
⇧
Revenir au début