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

REGEXP_INSTR¶

Retorna a posição da ocorrência especificada do padrão de expressão regular na entidade da cadeia de caracteres.

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

Sintaxe¶

REGEXP_INSTR( <subject> , <pattern> [ , <position> [ , <occurrence> [ , <option> [ , <regexp_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 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

option

Especifica se deve retornar o offset do primeiro caractere da correspondência (0) ou o offset do primeiro caractere após o fim da correspondência (1).

Padrão: 0

regexp_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`	POSIX wildcard character `.` matches `\n`

Padrão: c

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

Nota

Por padrão, REGEXP_INSTR retorna o offset do caractere inicial ou final para toda a parte correspondente do sujeito. Entretanto, se o parâmetro e (para “extrair”) for especificado, REGEXP_INSTR retornará o offset de início ou fim do caractere para a parte do sujeito que corresponde à primeira subexpressão 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_INSTR 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. A opção e está implícita.

O Snowflake oferece suporte a até 1.024 grupos.

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

Retornos¶

Retorna um valor do tipo NUMBER.

Se não for encontrada nenhuma correspondência, retorna 0.

Notas de uso¶

As posições são baseadas em 1, não em 0. Por exemplo, a posição da letra “M” em “MAN” é 1, e não 0.
Para notas adicionais de uso, consulte Notas de uso geral para funções de expressão regular.

Detalhes do agrupamento¶

Arguments with collation specifications are currently not supported.

Exemplos¶

Os exemplos a seguir usam a função REGEXP_INSTR.

Exemplos básicos¶

Criar uma tabela e inserir dados:

CREATE OR REPLACE TABLE demo1 (id INT, string1 VARCHAR);
INSERT INTO demo1 (id, string1) VALUES
  (1, 'nevermore1, nevermore2, nevermore3.');

Copy

Procure por uma cadeia de caracteres correspondente. Neste caso, a cadeia de caracteres é nevermore seguida por um único dígito decimal (por exemplo, nevermore1). O exemplo usa a função REGEXP_SUBSTR para mostrar a substring correspondente:

SELECT id,
       string1,
       REGEXP_SUBSTR(string1, 'nevermore\\d') AS substring,
       REGEXP_INSTR( string1, 'nevermore\\d') AS position
  FROM demo1
  ORDER BY id;

Copy

+----+-------------------------------------+------------+----------+
| ID | STRING1                             | SUBSTRING  | POSITION |
|----+-------------------------------------+------------+----------|
|  1 | nevermore1, nevermore2, nevermore3. | nevermore1 |        1 |
+----+-------------------------------------+------------+----------+

Pesquise uma cadeia de caracteres correspondente, mas começando no quinto caractere da cadeia de caracteres, em vez do primeiro caractere da cadeia de caracteres:

SELECT id,
       string1,
       REGEXP_SUBSTR(string1, 'nevermore\\d', 5) AS substring,
       REGEXP_INSTR( string1, 'nevermore\\d', 5) AS position
  FROM demo1
  ORDER BY id;

Copy

+----+-------------------------------------+------------+----------+
| ID | STRING1                             | SUBSTRING  | POSITION |
|----+-------------------------------------+------------+----------|
|  1 | nevermore1, nevermore2, nevermore3. | nevermore2 |       13 |
+----+-------------------------------------+------------+----------+

Pesquise uma cadeia de caracteres correspondente, mas procure a terceira correspondência em vez da primeira:

SELECT id,
       string1,
       REGEXP_SUBSTR(string1, 'nevermore\\d', 1, 3) AS substring,
       REGEXP_INSTR( string1, 'nevermore\\d', 1, 3) AS position
  FROM demo1
  ORDER BY id;

Copy

+----+-------------------------------------+------------+----------+
| ID | STRING1                             | SUBSTRING  | POSITION |
|----+-------------------------------------+------------+----------|
|  1 | nevermore1, nevermore2, nevermore3. | nevermore3 |       25 |
+----+-------------------------------------+------------+----------+

Esta consulta é quase idêntica à consulta anterior, mas esta mostra como usar o argumento option para indicar se você deseja a posição da expressão correspondente ou a posição do primeiro caractere após a expressão correspondente:

SELECT id,
       string1,
       REGEXP_SUBSTR(string1, 'nevermore\\d', 1, 3) AS substring,
       REGEXP_INSTR( string1, 'nevermore\\d', 1, 3, 0) AS start_position,
       REGEXP_INSTR( string1, 'nevermore\\d', 1, 3, 1) AS after_position
  FROM demo1
  ORDER BY id;

Copy

+----+-------------------------------------+------------+----------------+----------------+
| ID | STRING1                             | SUBSTRING  | START_POSITION | AFTER_POSITION |
|----+-------------------------------------+------------+----------------+----------------|
|  1 | nevermore1, nevermore2, nevermore3. | nevermore3 |             25 |             35 |
+----+-------------------------------------+------------+----------------+----------------+

Esta consulta mostra que se você procurar por uma ocorrência além da última ocorrência real, a posição retornada é 0:

SELECT id,
       string1,
       REGEXP_SUBSTR(string1, 'nevermore', 1, 4) AS substring,
       REGEXP_INSTR( string1, 'nevermore', 1, 4) AS position
  FROM demo1
  ORDER BY id;

Copy

+----+-------------------------------------+-----------+----------+
| ID | STRING1                             | SUBSTRING | POSITION |
|----+-------------------------------------+-----------+----------|
|  1 | nevermore1, nevermore2, nevermore3. | NULL      |        0 |
+----+-------------------------------------+-----------+----------+

Exemplos de grupos de captura¶

Esta seção mostra como usar o recurso de “agrupar” das expressões regulares.

Os primeiros exemplos nesta seção não usam grupos de captura. A seção começa com alguns exemplos simples e depois continua com exemplos que usam grupos de captura.

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;

Copy

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

Este exemplo pesquisa 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,
       string1,
       REGEXP_SUBSTR(string1, 'the\\W+\\w+') AS substring,
       REGEXP_INSTR(string1, 'the\\W+\\w+') AS position
  FROM demo2
  ORDER BY id;

Copy

+----+-------------------------------------------------------------+--------------+----------+
| ID | STRING1                                                     | SUBSTRING    | POSITION |
|----+-------------------------------------------------------------+--------------+----------|
|  2 | It was the best of times, it was the worst of times.        | the best     |        8 |
|  3 | In    the   string   the   extra   spaces  are   redundant. | the   string |        7 |
|  4 | A thespian theater is nearby.                               | NULL         |        0 |
+----+-------------------------------------------------------------+--------------+----------+

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,
       string1,
       REGEXP_SUBSTR(string1, 'the\\W+\\w+', 1, 2) AS substring,
       REGEXP_INSTR(string1, 'the\\W+\\w+', 1, 2) AS position
  FROM demo2
  ORDER BY id;

Copy

+----+-------------------------------------------------------------+-------------+----------+
| ID | STRING1                                                     | SUBSTRING   | POSITION |
|----+-------------------------------------------------------------+-------------+----------|
|  2 | It was the best of times, it was the worst of times.        | the worst   |       34 |
|  3 | In    the   string   the   extra   spaces  are   redundant. | the   extra |       22 |
|  4 | A thespian theater is nearby.                               | NULL        |        0 |
+----+-------------------------------------------------------------+-------------+----------+

Este exemplo é semelhante ao exemplo anterior, mas acrescenta grupos de captura. Em vez de retornar a posição da correspondência inteira, esta consulta retorna apenas a posição do grupo, que é a parte da substring que corresponde à parte da expressão regular entre parênteses. Neste caso, o valor retornado é a posição da palavra após a segunda ocorrência da palavra the.

SELECT id,
       string1,
       REGEXP_SUBSTR(string1, 'the\\W+(\\w+)', 1, 2,    'e', 1) AS substring,
       REGEXP_INSTR( string1, 'the\\W+(\\w+)', 1, 2, 0, 'e', 1) AS position
  FROM demo2
  ORDER BY id;

Copy

+----+-------------------------------------------------------------+-----------+----------+
| ID | STRING1                                                     | SUBSTRING | POSITION |
|----+-------------------------------------------------------------+-----------+----------|
|  2 | It was the best of times, it was the worst of times.        | worst     |       38 |
|  3 | In    the   string   the   extra   spaces  are   redundant. | extra     |       28 |
|  4 | A thespian theater is nearby.                               | NULL      |        0 |
+----+-------------------------------------------------------------+-----------+----------+

Se você especificar o parâmetro (extrair) 'e', mas não especificar o group_num, o padrão de group_num será 1:

SELECT id,
       string1,
       REGEXP_SUBSTR(string1, 'the\\W+(\\w+)', 1, 2,    'e') AS substring,
       REGEXP_INSTR( string1, 'the\\W+(\\w+)', 1, 2, 0, 'e') AS position
  FROM demo2
  ORDER BY id;

Copy

+----+-------------------------------------------------------------+-----------+----------+
| ID | STRING1                                                     | SUBSTRING | POSITION |
|----+-------------------------------------------------------------+-----------+----------|
|  2 | It was the best of times, it was the worst of times.        | worst     |       38 |
|  3 | In    the   string   the   extra   spaces  are   redundant. | extra     |       28 |
|  4 | A thespian theater is nearby.                               | NULL      |        0 |
+----+-------------------------------------------------------------+-----------+----------+

Se você especificar um group_num, o Snowflake assume que você quer extrair, mesmo que você não tenha especificado 'e' (extrair) como um dos parâmetros:

SELECT id,
       string1,
       REGEXP_SUBSTR(string1, 'the\\W+(\\w+)', 1, 2,    '', 1) AS substring,
       REGEXP_INSTR( string1, 'the\\W+(\\w+)', 1, 2, 0, '', 1) AS position
  FROM demo2
  ORDER BY id;

Copy

+----+-------------------------------------------------------------+-----------+----------+
| ID | STRING1                                                     | SUBSTRING | POSITION |
|----+-------------------------------------------------------------+-----------+----------|
|  2 | It was the best of times, it was the worst of times.        | worst     |       38 |
|  3 | In    the   string   the   extra   spaces  are   redundant. | extra     |       28 |
|  4 | A thespian theater is nearby.                               | NULL      |        0 |
+----+-------------------------------------------------------------+-----------+----------+

Este exemplo mostra como recuperar a posição da 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 0.

Criar uma tabela e inserir dados:

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

Copy

Executar a consulta:

SELECT id,
       string1,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w+)', 1, 1,    'e', 1) AS substring1,
       REGEXP_INSTR( string1, 'A\\W+(\\w+)', 1, 1, 0, 'e', 1) AS position1,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w+)', 1, 2,    'e', 1) AS substring2,
       REGEXP_INSTR( string1, 'A\\W+(\\w+)', 1, 2, 0, 'e', 1) AS position2,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w+)', 1, 3,    'e', 1) AS substring3,
       REGEXP_INSTR( string1, 'A\\W+(\\w+)', 1, 3, 0, 'e', 1) AS position3,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w+)', 1, 4,    'e', 1) AS substring4,
       REGEXP_INSTR( string1, 'A\\W+(\\w+)', 1, 4, 0, 'e', 1) AS position4
  FROM demo3;

Copy

+----+----------------------+------------+-----------+------------+-----------+------------+-----------+------------+-----------+
| ID | STRING1              | SUBSTRING1 | POSITION1 | SUBSTRING2 | POSITION2 | SUBSTRING3 | POSITION3 | SUBSTRING4 | POSITION4 |
|----+----------------------+------------+-----------+------------+-----------+------------+-----------+------------+-----------|
|  5 | A MAN A PLAN A CANAL | MAN        |         3 | PLAN       |         9 | CANAL      |        16 | NULL       |         0 |
+----+----------------------+------------+-----------+------------+-----------+------------+-----------+------------+-----------+

Este exemplo mostra como recuperar a posição do primeiro, segundo e terceiro grupos dentro da primeira ocorrência do padrão. Neste caso, os valores retornados são as posições das letras individuais da palavra MAN.

SELECT id,
       string1,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1,    'e', 1) AS substring1,
       REGEXP_INSTR( string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 0, 'e', 1) AS position1,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1,    'e', 2) AS substring2,
       REGEXP_INSTR( string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 0, 'e', 2) AS position2,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1,    'e', 3) AS substring3,
       REGEXP_INSTR( string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 0, 'e', 3) AS position3
  FROM demo3;

Copy

+----+----------------------+------------+-----------+------------+-----------+------------+-----------+
| ID | STRING1              | SUBSTRING1 | POSITION1 | SUBSTRING2 | POSITION2 | SUBSTRING3 | POSITION3 |
|----+----------------------+------------+-----------+------------+-----------+------------+-----------|
|  5 | A MAN A PLAN A CANAL | M          |         3 | A          |         4 | N          |         5 |
+----+----------------------+------------+-----------+------------+-----------+------------+-----------+

Exemplos adicionais¶

O seguinte exemplo corresponde a ocorrências da palavra was. A correspondência começa no primeiro caractere na cadeia de caracteres e retorna a posição na cadeia de caracteres do caractere após a primeira ocorrência:

SELECT REGEXP_INSTR('It was the best of times, it was the worst of times',
                    '\\bwas\\b',
                    1,
                    1) AS result;

Copy

+--------+
| RESULT |
|--------|
|      4 |
+--------+

O exemplo seguinte retorna o offset do primeiro caractere da parte da cadeia de caracteres que corresponde ao padrão. A correspondência começa no primeiro caractere da cadeia de caracteres e retorna a primeira ocorrência do padrão:

SELECT REGEXP_INSTR('It was the best of times, it was the worst of times',
                    'the\\W+(\\w+)',
                    1,
                    1,
                    0) AS result;

Copy

+--------+
| RESULT |
|--------|
|      8 |
+--------+

O exemplo a seguir é o mesmo que o exemplo anterior, mas usa o parâmetro e para retornar o deslocamento de caractere para a parte do assunto que corresponde à primeira subexpressão no padrão (o primeiro conjunto de caracteres de palavra após the):

SELECT REGEXP_INSTR('It was the best of times, it was the worst of times',
                    'the\\W+(\\w+)',
                    1,
                    1,
                    0,
                    'e') AS result;

Copy

+--------+
| RESULT |
|--------|
|     12 |
+--------+

O exemplo a seguir corresponde às ocorrências de palavras terminadas em st precedidas por dois ou mais caracteres alfabéticos (sem distinção entre maiúsculas e minúsculas). A correspondência começa no décimo quinto caractere na cadeia de caracteres e retorna a posição na cadeia de caracteres do caractere após a primeira ocorrência (o início de worst):

SELECT REGEXP_INSTR('It was the best of times, it was the worst of times',
                    '[[:alpha:]]{2,}st',
                    15,
                    1) AS result;

Copy

+--------+
| RESULT |
|--------|
|     38 |
+--------+

Para executar o próximo conjunto de exemplos, crie uma tabela e insira 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');

Copy

Retorna o deslocamento do primeiro caractere na primeira correspondência que contém uma letra minúscula o:

SELECT body,
       REGEXP_INSTR(body, '\\b\\S*o\\S*\\b') AS result
  FROM message;

Copy

+---------------------------------------------+--------+
| BODY                                        | RESULT |
|---------------------------------------------+--------|
| Hellooo World                               |      1 |
| How are you doing today?                    |      1 |
| the quick brown fox jumps over the lazy dog |     11 |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     |      0 |
+---------------------------------------------+--------+

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

SELECT body,
       REGEXP_INSTR(body, '\\b\\S*o\\S*\\b', 3) AS result
  FROM message;

Copy

+---------------------------------------------+--------+
| BODY                                        | RESULT |
|---------------------------------------------+--------|
| Hellooo World                               |      3 |
| How are you doing today?                    |      9 |
| the quick brown fox jumps over the lazy dog |     11 |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     |      0 |
+---------------------------------------------+--------+

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

SELECT body, REGEXP_INSTR(body, '\\b\\S*o\\S*\\b', 3, 3) AS result
  FROM message;

Copy

+---------------------------------------------+--------+
| BODY                                        | RESULT |
|---------------------------------------------+--------|
| Hellooo World                               |      0 |
| How are you doing today?                    |     19 |
| the quick brown fox jumps over the lazy dog |     27 |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     |      0 |
+---------------------------------------------+--------+

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

SELECT body, REGEXP_INSTR(body, '\\b\\S*o\\S*\\b', 3, 3, 1) AS result
  FROM message;

Copy

+---------------------------------------------+--------+
| BODY                                        | RESULT |
|---------------------------------------------+--------|
| Hellooo World                               |      0 |
| How are you doing today?                    |     24 |
| the quick brown fox jumps over the lazy dog |     31 |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     |      0 |
+---------------------------------------------+--------+

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

SELECT body, REGEXP_INSTR(body, '\\b\\S*o\\S*\\b', 3, 3, 1, 'i') AS result
  FROM message;

Copy

+---------------------------------------------+--------+
| BODY                                        | RESULT |
|---------------------------------------------+--------|
| Hellooo World                               |      0 |
| How are you doing today?                    |     24 |
| the quick brown fox jumps over the lazy dog |     31 |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     |     35 |
+---------------------------------------------+--------+