- Catégories :
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 :
sujet
La chaîne à rechercher pour les correspondances.
motif
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
paramètres_regex
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. Sie
est spécifié mais qu’unnum_groupe
n’est pas également spécifié, lenum_groupe
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 sie
n’était pas défini. Pour des exemples utilisante
, voir Exemples dans cette rubrique.num_groupe
Le paramètre
num_groupe
spécifie le groupe à extraire. Les groupes sont spécifiés à l’aide de parenthèses dans l’expression régulière.Si un
num_groupe
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
num_groupe
, voir les exemples de cette rubrique.
Notes sur l’utilisation¶
Les parenthèses (
( )
) et les crochets ([ ]
) doivent actuellement être doublés pour être analysés sous forme de chaînes de littéraux.Par exemple :
SELECT REGEXP_SUBSTR('Customers - (NY)','\\([[:alnum:]\-]+\\)') as customers; +-----------+ | CUSTOMERS | |-----------| | (NY) | +-----------+
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¶
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"} | +-----------------------------------------------------------------------------------------------------------------------------------------+