Pesquisa uma string quanto a um padrão de expressão regular e substitui cada ocorrência do padrão pela string especificada. REGEXP_REPLACE é semelhante a Função REPLACE, mas permite que você pesquise uma string quanto a um padrão de expressão regular. Para ter mais informações sobre expressões regulares, consulte Operadores POSIX e Expressão regular
REGEXP_REPLACE é semelhante a Função TRANSLATE e Função REPLACE, exceto que TRANSLATE faz várias substituições de caractere único e REPLACE substitui uma string inteira por outra string, enquanto REGEXP_REPLACE permite que você pesquise uma string quanto a um padrão de expressão regular.
Sintaxe
REGEXP_REPLACE( source_string, pattern [, replace_string [ , position [, parameters ] ] ] )
Argumentos
- source_string
-
Uma expressão de string
CHARouVARCHAR, tal como um nome de coluna, a ser pesquisada. - pattern
-
Um literal de string UTF-8 que representa um padrão de expressão regular. Para obter mais informações, consulte Operadores POSIX.
- replace_string
-
(Opcional) Uma expressão de string
CHARouVARCHAR, tal como um nome de coluna, que substituirá cada ocorrência do padrão. O padrão é uma string vazia ( "" ). - position
-
(Opcional) Um inteiro 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 position for maior que o número de caracteres na source_string, o resultado será source_string. - 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.
-
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
VARCHAR
Se o padrão ou replace_string for NULL, a função retornará NULL.
Exemplos
Para substituir todas as ocorrências da string FOX no valor quick brown fox usando a correspondência que não diferencia letras maiúsculas de minúsculas, use o exemplo a seguir.
SELECT REGEXP_REPLACE('the fox', 'FOX', 'quick brown fox', 1, 'i');
+---------------------+
| regexp_replace |
+---------------------+
| the quick brown fox |
+---------------------+O exemplo a seguir usa um padrão escrito no dialeto PCRE para localizar palavras contendo pelo menos um número e uma letra minúscula. Ele usa o operador ?=, que tem uma conotação específica look-ahead em PCRE. Para substituir cada ocorrência de tal palavra pelo valor [hidden], use o exemplo a seguir.
SELECT REGEXP_REPLACE('passwd7 plain A1234 a1234', '(?=[^ ]*[a-z])(?=[^ ]*[0-9])[^ ]+', '[hidden]', 1, 'p');
+-------------------------------+
| regexp_replace |
+-------------------------------+
| [hidden] plain A1234 [hidden] |
+-------------------------------+O exemplo a seguir usa um padrão escrito no dialeto PCRE para localizar palavras contendo pelo menos um número e uma letra minúscula. Ele usa o operador ?=, que tem uma conotação específica look-ahead em PCRE. Para substituir cada ocorrência de tal palavra pelo valor [hidden], mas que difere do exemplo anterior porque que ele usa correspondência sem diferenciar letras maiúsculas de minúsculas, use o exemplo a seguir.
SELECT REGEXP_REPLACE('passwd7 plain A1234 a1234', '(?=[^ ]*[a-z])(?=[^ ]*[0-9])[^ ]+', '[hidden]', 1, 'ip');
+----------------------------------+
| regexp_replace |
+----------------------------------+
| [hidden] plain [hidden] [hidden] |
+----------------------------------+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 excluir o @ e o nome de domínio dos endereços de e-mail, use o exemplo a seguir.
SELECT email, REGEXP_REPLACE(email, '@.*\\.(org|gov|com|edu|ca)$')
FROM users
ORDER BY userid LIMIT 4;
+-----------------------------------------------+-----------------------+
| email | regexp_replace |
+-----------------------------------------------+-----------------------+
| Etiam.laoreet.libero@sodalesMaurisblandit.edu | Etiam.laoreet.libero |
| Suspendisse.tristique@nonnisiAenean.edu | Suspendisse.tristique |
| amet.faucibus.ut@condimentumegetvolutpat.ca | amet.faucibus.ut |
| sed@lacusUtnec.ca | sed |
+-----------------------------------------------+-----------------------+Para substituir os nomes de domínio de endereços de e-mail por internal.company.com, use o exemplo a seguir.
SELECT email, REGEXP_REPLACE(email, '@.*\\.[[:alpha:]]{2,3}','@internal.company.com')
FROM users
ORDER BY userid LIMIT 4;
+-----------------------------------------------+--------------------------------------------+
| email | regexp_replace |
+-----------------------------------------------+--------------------------------------------+
| Etiam.laoreet.libero@sodalesMaurisblandit.edu | Etiam.laoreet.libero@internal.company.com |
| Suspendisse.tristique@nonnisiAenean.edu | Suspendisse.tristique@internal.company.com |
| amet.faucibus.ut@condimentumegetvolutpat.ca | amet.faucibus.ut@internal.company.com |
| sed@lacusUtnec.ca | sed@internal.company.com |
+-----------------------------------------------+--------------------------------------------+