Função REGEXP_REPLACE

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 na Wikipédia.

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 CHAR ou VARCHAR, 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 ter mais informações, consulte Operadores POSIX.

replace_string

(Opcional) Uma expressão de string CHAR ou VARCHAR, 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 que 1, 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 ter 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     |
+-----------------------------------------------+-----------------------+
| [e-mail] | Etiam.laoreet.libero  |
| [e-mail]       | Suspendisse.tristique |
| [e-mail]   | amet.faucibus.ut      |
| [e-mail]                             | 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               |
+-----------------------------------------------+--------------------------------------------+
| [e-mail] | [e-mail]  |
| [e-mail]       | [e-mail] |
| [e-mail]   | [e-mail]      |
| [e-mail]                             | [e-mail]                   |
+-----------------------------------------------+--------------------------------------------+