Categorias:

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

REGEXP_SUBSTR_ALL

Retorna um ARRAY que contém todas as subcadeias de caracteres que correspondem a uma expressão regular dentro de uma cadeia de caracteres.

Aliases:

REGEXP_EXTRACT_ALL

Sintaxe

REGEXP_SUBSTR_ALL( <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 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_ALL retorna toda a parte correspondente do assunto. Entretanto, se o parâmetro e for especificado, REGEXP_SUBSTR_ALL retorna a parte do assunto que corresponde ao primeiro grupo no 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_ALL 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 ARRAY. A matriz contém um elemento para cada substring correspondente.

A função retorna uma matriz vazia se nenhuma correspondência for encontrada.

A função retorna NULL nos seguintes casos:

  • Qualquer argumento é NULL.

  • Você especifica group_num e o padrão não especifica um agrupamento com esse número. Por exemplo, se o padrão especificar apenas um grupo (por exemplo, a(b)c) e você usar 2 como group_num, a função retornará 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

O padrão no exemplo a seguir corresponde a um “a” minúsculo seguido por um dígito. O exemplo retorna um ARRAY que contém todas as correspondências:

SELECT REGEXP_SUBSTR_ALL('a1_a2a3_a4A5a6', 'a[[:digit:]]') AS matches;
Copy
+---------+
| MATCHES |
|---------|
| [       |
|   "a1", |
|   "a2", |
|   "a3", |
|   "a4", |
|   "a6"  |
| ]       |
+---------+

O exemplo seguinte começa a encontrar correspondências a partir do segundo caractere da cadeia de caracteres (2):

SELECT REGEXP_SUBSTR_ALL('a1_a2a3_a4A5a6', 'a[[:digit:]]', 2) AS matches;
Copy
+---------+
| MATCHES |
|---------|
| [       |
|   "a2", |
|   "a3", |
|   "a4", |
|   "a6"  |
| ]       |
+---------+

O exemplo a seguir começa a retornar correspondências a partir da terceira ocorrência do padrão na cadeia de caracteres (3):

SELECT REGEXP_SUBSTR_ALL('a1_a2a3_a4A5a6', 'a[[:digit:]]', 1, 3) AS matches;
Copy
+---------+
| MATCHES |
|---------|
| [       |
|   "a3", |
|   "a4", |
|   "a6"  |
| ]       |
+---------+

O exemplo a seguir realiza uma correspondência que não diferencia maiúsculas e minúsculas (i):

SELECT REGEXP_SUBSTR_ALL('a1_a2a3_a4A5a6', 'a[[:digit:]]', 1, 1, 'i') AS matches;
Copy
+---------+
| MATCHES |
|---------|
| [       |
|   "a1", |
|   "a2", |
|   "a3", |
|   "a4", |
|   "A5", |
|   "a6"  |
| ]       |
+---------+

O exemplo a seguir realiza uma correspondência que não diferencia maiúsculas e minúsculas e retorna a parte da cadeia de caracteres que corresponde ao primeiro grupo (ie):

SELECT REGEXP_SUBSTR_ALL('a1_a2a3_a4A5a6', '(a)([[:digit:]])', 1, 1, 'ie') AS matches;
Copy
+---------+
| MATCHES |
|---------|
| [       |
|   "a",  |
|   "a",  |
|   "a",  |
|   "a",  |
|   "A",  |
|   "a"   |
| ]       |
+---------+

O exemplo a seguir demonstra que a função retorna uma matriz vazia quando nenhuma correspondência é encontrada:

SELECT REGEXP_SUBSTR_ALL('a1_a2a3_a4A5a6', 'b') AS matches;
Copy
+---------+
| MATCHES |
|---------|
| []      |
+---------+

Este exemplo mostra como recuperar cada segunda palavra em uma cadeia de caracteres da primeira, segunda e terceira correspondências de um padrão de duas palavras em que a primeira palavra é A.

Primeiro, crie uma tabela e insira os dados:

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

Executar a consulta:

SELECT REGEXP_SUBSTR_ALL(string1, 'A\\W+(\\w+)', 1, 1, 'e', 1) AS result1,
       REGEXP_SUBSTR_ALL(string1, 'A\\W+(\\w+)', 1, 2, 'e', 1) AS result2,
       REGEXP_SUBSTR_ALL(string1, 'A\\W+(\\w+)', 1, 3, 'e', 1) AS result3
  FROM test_regexp_substr_all;
Copy
+-----------+-----------+-----------+
| RESULT1   | RESULT2   | RESULT3   |
|-----------+-----------+-----------|
| [         | [         | [         |
|   "MAN",  |   "PLAN", |   "CANAL" |
|   "PLAN", |   "CANAL" | ]         |
|   "CANAL" | ]         |           |
| ]         |           |           |
+-----------+-----------+-----------+

Este exemplo mostra como recuperar o primeiro, segundo e terceiro grupos dentro de cada ocorrência do padrão em uma cadeia de caracteres. Nesse caso, os valores retornados são cada letra individual de cada palavra correspondente em cada grupo.

SELECT REGEXP_SUBSTR_ALL(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 'e', 1) AS result1,
       REGEXP_SUBSTR_ALL(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 'e', 2) AS result2,
       REGEXP_SUBSTR_ALL(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 'e', 3) AS result3
  FROM test_regexp_substr_all;
Copy
+---------+---------+---------+
| RESULT1 | RESULT2 | RESULT3 |
|---------+---------+---------|
| [       | [       | [       |
|   "M",  |   "A",  |   "N",  |
|   "P",  |   "L",  |   "A",  |
|   "C"   |   "A"   |   "N"   |
| ]       | ]       | ]       |
+---------+---------+---------+
Linguagem: Português