Categorias:

Funções de cadeia de caracteres (expressões regulares)

REGEXP_SUBSTR

Retorna a subcadeia de caracteres que corresponde a uma expressão regular dentro de uma cadeia de caracteres. Se não for encontrada nenhuma correspondência, retorna NULL.

Consulte também: Funções de cadeia de caracteres (expressões regulares)

Sintaxe

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

Argumentos

Obrigatório:

subject

A cadeia de caracteres a ser procurada por correspondências.

pattern

Padrão a atender.

Para diretrizes sobre como especificar padrões, consulte Funções de cadeia de caracteres (expressões regulares).

Opcional:

position

Número de caracteres desde o início da cadeia de caracteres onde a função começa a procurar correspondências.

Padrão: 1 (a busca por uma correspondência começa no primeiro caractere à esquerda)

occurrence

Especifica qual a ocorrência do padrão a ser correspondido. A função salta as primeiras occurrence - 1 correspondências.

Padrão: 1

regex_parameters

Cadeia de caracteres de um ou mais caracteres que especifica os parâmetros de expressão regular usados para pesquisar correspondências. Os valores aceitos são:

  • c: diferencia maiúsculas e minúsculas.

  • i: não diferencia maiúsculas e minúsculas.

  • m: modo multilinhas.

  • e: extrair subcorrespondências.

  • s: o curinga ‘.’ também corresponde à nova linha.

Para obter mais detalhes, consulte a documentação de parâmetros de expressão regular.

Padrão: c

Nota

Por padrão, REGEXP_SUBSTR retorna toda a parte correspondente do assunto. Entretanto, se o parâmetro e (para “extrair”) for especificado, REGEXP_SUBSTR retornará a parte do sujeito que corresponde ao primeiro grupo do padrão. Se e for especificado, mas um group_num também não for especificado, então o group_num padrão é 1 (o primeiro grupo). Se não houver subexpressão no padrão, REGEXP_SUBSTR se comportará como se e não estivesse definido. Para exemplos que usam e, consulte Exemplos neste tópico.

group_num

O parâmetro group_num especifica o grupo a ser extraído. Os grupos são especificados usando parênteses na expressão regular.

Se um group_num for especificado, o Snowflake permite a extração mesmo que a opção 'e' também não tenha sido especificada. O 'e' está implícito.

O Snowflake oferece suporte a até 1.024 grupos.

Para exemplos que usam group_num, consulte Exemplos neste tópico.

Retornos

A função retorna um valor do tipo VARCHAR que é a subcadeia de caracteres correspondente.

Notas de uso

Para informações adicionais sobre o uso de expressões regulares, consulte Funções de cadeia de caracteres (expressões regulares).

Detalhes do agrupamento

Arguments with collation specifications are currently not supported.

Exemplos

A documentação da função REGEXP_INSTR contém muitos exemplos que utilizam tanto REGEXP_SUBSTR como REGEXP_INSTR. Talvez você também queira dar uma olhada nesses exemplos.

Os próximos exemplos utilizam a tabela de cadeias de caracteres criada abaixo:

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

O próximo exemplo pesquisa:

  • a palavra “the”

  • seguido de um ou mais caracteres não nominativos

  • seguido de um ou mais caracteres de palavras.

Os “caracteres de palavra” incluem não apenas as letras a-z e A-Z, mas também o sublinhado (“_”) e os dígitos decimais 0-9, mas não o espaço em branco, pontuação 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         |
+----+--------------+
Copy

A partir da posição 1 da cadeia de caracteres, procure a segunda ocorrência de

  • a palavra “the”

  • seguido de um ou mais caracteres não nominativos

  • seguido de um ou mais caracteres de palavras.

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

A partir da posição 1 da cadeia de caracteres, procure a segunda ocorrência de

  • a palavra “the”

  • seguido de um ou mais caracteres não nominativos

  • seguido de um ou mais caracteres de palavras.

Em vez de retornar a correspondência inteira, retorne somente o “grupo” (ou seja, a parte da subcadeia de caracteres que corresponde à parte da expressão regular entre parênteses). Neste caso, o valor retornado deve ser a palavra após “the”.

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

Este exemplo mostra como recuperar a segunda palavra da primeira, segunda e terceira correspondência de um padrão de duas palavras no qual a primeira palavra é A. Isto também mostra que tentar ir além do último padrão faz com que o Snowflake retorne NULL.

CREATE TABLE demo3 (id INT, string1 VARCHAR);;
INSERT INTO demo3 (id, string1) VALUES
    (5, 'A MAN A PLAN A CANAL')
    ;
Copy
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    |
+----+---------+---------+---------+---------+
Copy

Este exemplo mostra como recuperar o primeiro, segundo e terceiro grupos dentro da primeira ocorrência do padrão. Neste caso, os valores retornados são as letras individuais da palavra 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       |
+----+---------+---------+---------+
Copy

Aqui estão alguns exemplos adicionais.

-- 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');
Copy
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                                     |
+---------------------------------------------+------------------------------------------+
Copy
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                                      |
+---------------------------------------------+-------------------------------------------+
Copy
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                                         |
+---------------------------------------------+----------------------------------------------+
Copy
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                                            |
+---------------------------------------------+---------------------------------------------------+
Copy

Este exemplo mostra que você pode omitir explicitamente qualquer parâmetro de expressão regular especificando a cadeia de caracteres vazia.

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

O exemplo seguinte ilustra ocorrências que se sobrepõem:

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

O exemplo seguinte cria um objeto JSON a partir de um Apache HTTP. Log de acesso ao servidor usando combinação de padrões e concatenação:

-- 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');
Copy
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"} |
+-----------------------------------------------------------------------------------------------------------------------------------------+
Copy
Linguagem: Português