Função REGEXP_INSTR
Pesquisa um padrão de expressão regular em uma sequência e retorna um inteiro que indica a posição inicial ou final da subsequência correspondente. Se nenhuma correspondência for encontrada, a função retornará 0
. REGEXP_INSTR é semelhante à função POSITION, mas permite que você pesquise um padrão de expressão regular em uma sequência. Para ter mais informações sobre expressões regulares, consulte Operadores POSIX e Expressão regular
Sintaxe
REGEXP_INSTR( source_string, pattern [, position [, occurrence] [, option [, parameters ] ] ] ] )
Argumentos
- source_string
-
Uma expressão de string, tal como um nome de coluna, a ser procurada.
- pattern
-
Um literal de string UTF-8 que representa um padrão de expressão regular. Para obter mais informações, consulte Operadores POSIX.
- position
-
(Opcional) Um
INTEGER
positivo que indica a posição em source_string para começar a pesquisar. A posição é baseada no número de caracteres, e não bytes, de forma que caracteres multibyte são contados como caracteres simples. O padrão é1
. Se a posição for menor que1
, a pesquisa começará no primeiro caractere da source_string. Se a posição for maior que o número de caracteres na source_string, o resultado será0
. - occurrence
-
(Opcional) Um
INTEGER
positivo que indica qual ocorrência do padrão usar. REGEXP_INSTR ignora as primeiras correspondências deoccurrence-1
. O padrão é1
. Se a ocorrência for menor que1
ou maior que o número de caracteres em source_string, a pesquisa será ignorada e o resultado será0
. - option
-
(Opcional) Um valor que indica se retornar a posição do primeiro caractere da correspondência (
0
) ou a posição do primeiro caractere seguinte ao final da correspondência (1
). Um valor diferente de zero é o mesmo que1
. O valor padrão é0
. - parameters
-
(Opcional) Uma ou mais literais de string que indicam como a função corresponde ao padrão. Os valores possíveis são os seguintes:
-
c – Executa a correspondência diferenciando maiúsculas e minúsculas. O padrão é usar a correspondência diferenciando maiúsculas e minúsculas.
-
i – Executa a correspondência sem diferenciar maiúsculas de minúsculas.
-
e – Extrai uma subsequência usando uma subexpressão.
Se o padrão incluir uma subexpressão, REGEXP_INSTR corresponderá uma subsequência usando a primeira subexpressão em padrão. REGEXP_INSTR considera apenas a primeira subexpressão. As subexpressões adicionais são ignoradas. Se o padrão não tiver uma subexpressão, REGEXP_INSTR ignorará o parâmetro 'e'.
-
p — Interpreta o padrão com o dialeto de expressão regular compatível com Perl (PCRE - Perl Compatible Regular Expression). Para ter mais informações sobre PCRE, consulte Perl Compatible Regular Expressions
na Wikipédia.
-
Tipo de retorno
Inteiro
Exemplos
Os exemplos a seguir usam dados da tabela USERS do banco de dados de amostra TICKIT. Para obter mais informações, consulte Banco de dados de exemplo.
Para pesquisar o caractere @
que inicia o nome de um domínio e retorna a posição de início da primeira correspondência, use o exemplo a seguir.
SELECT email, REGEXP_INSTR(email, '@[^.]*') FROM users ORDER BY userid LIMIT 4;
+-----------------------------------------------+--------------+ | email | regexp_instr | +-----------------------------------------------+--------------+ | Etiam.laoreet.libero@sodalesMaurisblandit.edu | 21 | | Suspendisse.tristique@nonnisiAenean.edu | 22 | | amet.faucibus.ut@condimentumegetvolutpat.ca | 17 | | sed@lacusUtnec.ca | 4 | +-----------------------------------------------+--------------+
Para pesquisar as variantes da palavra Center
e retornar a posição inicial da primeira correspondência, use o exemplo a seguir.
SELECT venuename, REGEXP_INSTR(venuename,'[cC]ent(er|re)$') FROM venue WHERE REGEXP_INSTR(venuename,'[cC]ent(er|re)$') > 0 ORDER BY venueid LIMIT 4;
+-----------------------+--------------+ | venuename | regexp_instr | +-----------------------+--------------+ | The Home Depot Center | 16 | | Izod Center | 6 | | Wachovia Center | 10 | | Air Canada Centre | 12 | +-----------------------+--------------+
Para encontrar a posição inicial da primeira ocorrência da string FOX
, utilizando a lógica de correspondência que não diferencia letras maiúsculas de minúsculas, use o exemplo a seguir.
SELECT REGEXP_INSTR('the fox', 'FOX', 1, 1, 0, 'i');
+--------------+ | regexp_instr | +--------------+ | 5 | +--------------+
Para usar um padrão escrito no dialeto PCRE para localizar palavras contendo pelo menos um número e uma letra minúscula, use o exemplo a seguir. Ele usa o operador ?=
, que tem uma conotação específica look-ahead em PCRE. Este exemplo encontra a posição inicial da segunda palavra.
SELECT REGEXP_INSTR('passwd7 plain A1234 a1234', '(?=[^ ]*[a-z])(?=[^ ]*[0-9])[^ ]+', 1, 2, 0, 'p');
+--------------+ | regexp_instr | +--------------+ | 21 | +--------------+
Para usar um padrão escrito no dialeto PCRE para localizar palavras contendo pelo menos um número e uma letra minúscula, use o exemplo a seguir. Ele usa o operador ?=
, que tem uma conotação específica look-ahead em PCRE. Este exemplo localiza a posição inicial da segunda palavra, mas difere do exemplo anterior na medida em que usa correspondência sem diferenciar maiúsculas de minúsculas.
SELECT REGEXP_INSTR('passwd7 plain A1234 a1234', '(?=[^ ]*[a-z])(?=[^ ]*[0-9])[^ ]+', 1, 2, 0, 'ip');
+--------------+ | regexp_instr | +--------------+ | 15 | +--------------+