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. Se não for encontrada nenhuma correspondência, retorna 0.

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.

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

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 de expressão regular usados para a busca de 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_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. 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.

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¶

Exemplos básicos¶

Os próximos exemplos utilizam estes dados:

CREATE 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”):

select id, string1,
      regexp_substr(string1, 'nevermore\\d') AS "SUBSTRING", 
      regexp_instr( string1, 'nevermore\\d') AS "POSITION"
    from demo1
    order by id;
+----+-------------------------------------+------------+----------+
| ID | STRING1                             | SUBSTRING  | POSITION |
|----+-------------------------------------+------------+----------|
|  1 | nevermore1, nevermore2, nevermore3. | nevermore1 |        1 |
+----+-------------------------------------+------------+----------+

Copy

Procurar por 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;
+----+-------------------------------------+------------+----------+
| ID | STRING1                             | SUBSTRING  | POSITION |
|----+-------------------------------------+------------+----------|
|  1 | nevermore1, nevermore2, nevermore3. | nevermore2 |       13 |
+----+-------------------------------------+------------+----------+

Copy

Procurar por uma cadeia de caracteres correspondente, mas pela terceira correspondência em vez da primeira correspondência:

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;
+----+-------------------------------------+------------+----------+
| ID | STRING1                             | SUBSTRING  | POSITION |
|----+-------------------------------------+------------+----------|
|  1 | nevermore1, nevermore2, nevermore3. | nevermore3 |       25 |
+----+-------------------------------------+------------+----------+

Copy

Esta consulta é quase idêntica à consulta anterior, mas isto mostra como usar o parâmetro option para indicar se você quer 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;
+----+-------------------------------------+------------+----------------+----------------+
| ID | STRING1                             | SUBSTRING  | START_POSITION | AFTER_POSITION |
|----+-------------------------------------+------------+----------------+----------------|
|  1 | nevermore1, nevermore2, nevermore3. | nevermore3 |             25 |             35 |
+----+-------------------------------------+------------+----------------+----------------+

Copy

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;
+----+-------------------------------------+-----------+----------+
| ID | STRING1                             | SUBSTRING | POSITION |
|----+-------------------------------------+-----------+----------|
|  1 | nevermore1, nevermore2, nevermore3. | NULL      |        0 |
+----+-------------------------------------+-----------+----------+

Copy

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 utilizam grupos de captura; a seção começa com alguns exemplos simples e depois continua com exemplos que utilizam grupos de captura.

Estes exemplos utilizam as cadeias de caracteres criadas 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”

seguida de um ou mais caracteres que não sejam palavras (por exemplo, o espaço em branco que separa as palavras)

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, string1,
       regexp_substr(string1, 'the\\W+\\w+') as "SUBSTRING",
       regexp_instr(string1, 'the\\W+\\w+') as "POSITION"
    from demo2
    order by id;
+----+-------------------------------------------------------------+--------------+----------+
| 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 |
+----+-------------------------------------------------------------+--------------+----------+

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

Copy

Este exemplo é semelhante ao exemplo anterior, mas acrescenta grupos de captura. Em vez de retornar a posição de toda a correspondência, esta consulta retorna a posição apenas do “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 posição da palavra após “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;
+----+-------------------------------------------------------------+-----------+----------+
| 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 |
+----+-------------------------------------------------------------+-----------+----------+

Copy

Se você especificar o parâmetro 'e' (“extrair”), mas não especificar o group_num, então o grupo_num padrão 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;
+----+-------------------------------------------------------------+-----------+----------+
| 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 |
+----+-------------------------------------------------------------+-----------+----------+

Copy

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

Copy

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.

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

Copy

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

Copy

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 "SUBSTR1",
    regexp_instr( string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 0, 'e', 1) as "POS1",
    regexp_substr(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1,    'e', 2) as "SUBSTR2",
    regexp_instr( string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 0, 'e', 2) as "POS2",
    regexp_substr(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1,    'e', 3) as "SUBSTR3",
    regexp_instr( string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 0, 'e', 3) as "POS3"
    from demo3;
+----+----------------------+---------+------+---------+------+---------+------+
| ID | STRING1              | SUBSTR1 | POS1 | SUBSTR2 | POS2 | SUBSTR3 | POS3 |
|----+----------------------+---------+------+---------+------+---------+------|
|  5 | A MAN A PLAN A CANAL | M       |    3 | A       |    4 | N       |    5 |
+----+----------------------+---------+------+---------+------+---------+------+

Copy

Exemplos adicionais¶

O seguinte exemplo corresponde a ocorrências da palavra was. A correspondência começa no primeiro caractere da 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" from dual;

+--------+
| result |
|--------|
|      4 |
+--------+

Copy

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 na 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" from dual;

+--------+
| result |
|--------|
|      8 |
+--------+

Copy

O exemplo seguinte é o mesmo que o exemplo anterior, mas usa o parâmetro e para retornar o offset de caracteres para a parte do sujeito que corresponde à primeira subexpressão no padrão (ou seja, o primeiro conjunto de caracteres de palavras 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" from dual;

+--------+
| result |
|--------|
|     12 |
+--------+

Copy

O exemplo a seguir combina ocorrências de palavras terminadas em st precedidas por 2 ou mais caracteres alfabéticos (sem diferenciação de maiúsculas e minúsculas). A correspondência começa no décimo quinto caractere da 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', '[[:alpha:]]{2,}st', 15, 1) as "result" from dual;

+--------+
| result |
|--------|
|     38 |
+--------+

Copy

Preparar os exemplos:

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

Retornar o offset do primeiro caractere na primeira correspondência:

select body, regexp_instr(body, '\\b\\S*o\\S*\\b') as result from message;

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

Copy

Primeiro caractere na primeira correspondência, começando no terceiro caractere no assunto:

select body, regexp_instr(body, '\\b\\S*o\\S*\\b', 3) as result from message;

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

Copy

Primeiro caractere na terceira correspondência, começando no terceiro caractere no assunto:

select body, regexp_instr(body, '\\b\\S*o\\S*\\b', 3, 3) as result from message;

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

Copy

Último caractere na terceira correspondência, começando no terceiro caractere no assunto:

select body, regexp_instr(body, '\\b\\S*o\\S*\\b', 3, 3, 1) as result from message;

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

Copy

Último caractere na terceira correspondência, começando no terceiro caractere no assunto, correspondência que não diferencia maiúsculas e minúsculas:

select body, regexp_instr(body, '\\b\\S*o\\S*\\b', 3, 3, 1, 'i') as result from message;

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

Copy

select body, regexp_instr(body, '\\S*(o)\\S*\\b', 1, 1, 0, 'i') as result from message;

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

Copy

select body, regexp_instr(body, '\\S*(o)\\S*\\b', 1, 1, 0, 'ie') as result from message;

---------------------------------------------+--------------------------------------------------+
                    body                     | result                                           |
---------------------------------------------+--------------------------------------------------+
 Hellooo World                               | 7                                                |
 How are you doing today?                    | 2                                                |
 the quick brown fox jumps over the lazy dog | 13                                               |
 PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | 10                                               |
---------------------------------------------+--------------------------------------------------+

Copy