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.

Sintaxe¶

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

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 a partir do início da cadeia de caracteres onde a função inicia a busca por correspondências. O valor deve ser um número inteiro positivo.

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

occurrence

Especifica a primeira ocorrência do padrão a partir do qual se começa a retornar as correspondências.

A função salta as primeiras occurrence - 1 correspondências. Por exemplo, se houver 5 correspondências e você especificar 3 para o argumento occurrence, a função ignorará as duas primeiras correspondências e retornará a terceira, quarta e quinta correspondências.

Padrão: 1

regex_parameters

Cadeia de caracteres de um ou mais caracteres que especifica os parâmetros usados para a busca de correspondências. Valores com suporte:


Parâmetro	Descrição
`c`	Correspondência com distinção entre maiúsculas e minúsculas
`i`	Correspondência sem distinção entre maiúsculas e minúsculas
`m`	Modo multilinha
`e`	Extração de subcorrespondências
`s`	O caractere curinga POSIX do modo de linha única `.` corresponde a `\n`

Padrão: c

Para obter mais informações, consulte Especificação dos parâmetros para a expressão regular.

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

Especifica qual grupo extrair. 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.

A função retorna NULL nos seguintes casos:

Nenhuma correspondência foi encontrada.
Qualquer argumento é NULL.

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 currently aren’t 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.

Estes exemplos utilizam as cadeias de caracteres criadas abaixo:

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

As cordas têm as seguintes características:

A cadeia de caracteres com um id de 2 tem várias ocorrências da palavra “the”.
A cadeia de caracteres com um id de 3 tem várias ocorrências da palavra “the” com espaços em branco extras entre as palavras.
A cadeia de caracteres com um id de 4 tem a sequência de caractere “the” dentro de várias palavras (“thespian” e “theater”), mas sem a palavra “the” sozinha.

Os exemplos a seguir chamam a função REGEXP_SUBSTR:

Chamada da função REGEXP_SUBSTR em uma lista SELECT
Chamada da função REGEXP_SUBSTR em uma cláusula WHERE

Chamada da função REGEXP_SUBSTR em uma lista SELECT¶

Chame a função REGEXP_SUBSTR em uma lista SELECT para extrair ou exibir valores que correspondem a um padrão.

Este exemplo procura a primeira ocorrência da palavra the, seguida por um ou mais caracteres que não sejam de palavras (por exemplo, o espaço em branco que separa as palavras) seguido por um ou mais caracteres de palavras.

“Caracteres de palavras” 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 espaços em branco, pontuação e assim por diante.

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

Começando na posição 1 da cadeia de caracteres, procure a segunda ocorrência da palavra the, seguida por um ou mais caracteres não pertencentes à palavra, seguidos por um ou mais caracteres pertencentes à palavra.

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

Em vez de retornar a correspondência inteira, retorna apenas o “grupo” (por exemplo, a parte da substring 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   |
+----+--------+

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. Este exemplo também mostra que tentar ir além do último padrão faz com que o Snowflake retorne NULL.

Primeiro, crie uma tabela e insira os dados:

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

Executar a consulta:

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

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

Aqui estão alguns exemplos adicionais.

Criar uma tabela e inserir dados:

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

Retorne a primeira correspondência que contém um o minúsculo, correspondendo a um limite de palavra (\b), seguido por zero ou mais caracteres de palavras (\S), a letra o e, em seguida, zero ou mais caracteres de palavras até o próximo limite de palavra:

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

Retorna a primeira correspondência que contém uma letra o minúscula, começando no terceiro caractere no assunto:

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

Retorna a terceira correspondência que contém uma letra o minúscula, começando no terceiro caractere no assunto:

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

Retorna a terceira correspondência que contém uma letra o minúscula, começando no terceiro caractere no assunto, com correspondência que não diferencia maiúsculas de minúsculas:

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

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

O exemplo seguinte ilustra ocorrências que se sobrepõem: Primeiro, crie uma tabela e insira os dados:

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

Execute uma consulta que encontre a segunda ocorrência do seguinte padrão em cada linha: um sinal de pontuação seguido por dígitos e letras, seguido por um sinal de pontuação.

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

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

O exemplo a seguir cria um objeto JSON a partir de um log de acesso ao Apache HTTP Server usando uma combinação de padrões e concatenações. Primeiro, crie uma tabela e insira os dados:

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

Execute uma consulta:

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

Chamada da função REGEXP_SUBSTR em uma cláusula WHERE¶

Chame a função REGEXP_SUBSTR em uma cláusula WHERE para filtrar linhas que contêm valores que correspondem a um padrão. Ao usar a função, você pode evitar múltiplas condições OR.

O exemplo a seguir consulta a tabela demo2 criada anteriormente para retornar linhas que incluem a cadeia de caracteres best ou thespian. Adicione IS NOT NULL à condição para retornar linhas que correspondem ao padrão. Ou seja, as linhas em que a função REGEXP_SUBSTR não retornou 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.                        |
+----+------------------------------------------------------+

Você pode usar as condições AND para encontrar linhas que correspondem a vários padrões. Por exemplo, a consulta a seguir retorna linhas que incluem a cadeia de caracteres best ou thespian e começam com a cadeia de caracteres 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. |
+----+------------------------------------------------------+