Funzione REGEXP_REPLACE

Cerca una stringa per un modello di espressione regolare e sostituisce ogni occorrenza del modello con la stringa specificata. REGEXP_REPLACE è simile a Funzione REPLACE, ma consente di cercare una stringa per un modello di espressione regolare. Per ulteriori informazioni sulle espressioni regolari, vedere Operatori POSIX Espressione regolare in Wikipedia.

REGEXP_REPLACE è simile a Funzione TRANSLATE e a Funzione REPLACE, ad eccezione del fatto che TRANSLATE esegue più sostituzioni a carattere singolo e REPLACE sostituisce un'intera stringa con un'altra stringa, mentre REGEXP_REPLACE consente di cercare una stringa per un modello di espressione regolare.

Sintassi

REGEXP_REPLACE( source_string, pattern [, replace_string [ , position [, parameters ] ] ] )

Argomenti

source_string

Un'espressione di stringa CHAR o VARCHAR, come ad esempio un nome di colonna, da cercare.

pattern

Una stringa letterale UTF-8 che rappresenta un modello di espressione regolare. Per ulteriori informazioni, consulta Operatori POSIX.

replace_string

(Facoltativo) Un'espressione di stringa CHAR or VARCHAR, ad esempio un nome di colonna, che sostituirà ogni occorrenza del modello. L'impostazione predefinita è una stringa vuota ( "" ).

posizione

(Facoltativo) Un numero intero positivo che indica la posizione all'interno di source_string per iniziare la ricerca. La posizione si basa sul numero di caratteri, non di byte, pertanto i caratteri multibyte vengono contati come caratteri singoli. Il valore predefinito è 1. Se position è inferiore a 1, la ricerca inizia con il primo carattere di source_string. Se posizione è maggiore rispetto al numero di caratteri in source_string, il risultato è source_string.

parameters

(Facoltativo) Uno o più letterali di stringa che indicano come la funzione corrisponde al modello. Di seguito sono riportati i valori possibili:

• c: eseguire una corrispondenza in base a maiuscole e minuscole. L'impostazione predefinita è utilizzare la corrispondenza con distinzione tra maiuscole e minuscole.

• i: eseguire una corrispondenza senza distinzione tra maiuscole e minuscole.

• p: interpreta il modello con il dialetto Perl Compatible Regular Expression (PCRE). Per ulteriori informazioni su PCRE, vedere Espressioni regolari compatibili con Perl in Wikipedia.

Tipo restituito

VARCHAR

Se pattern o replace_string è NULL, la funzione restituisce NULL.

Esempi

Per sostituire tutte le ricorrenze della stringa FOX nel valore quick brown fox usando una corrispondenza senza distinzione tra maiuscole e minuscole, utilizza l'esempio seguente.

SELECT REGEXP_REPLACE('the fox', 'FOX', 'quick brown fox', 1, 'i');

+---------------------+
|   regexp_replace    |
+---------------------+
| the quick brown fox |
+---------------------+

Nell'esempio seguente viene utilizzato un modello scritto in dialetto PCRE per individuare le parole contenenti almeno un numero e una lettera minuscola. Utilizza l'operatore ?=, che ha una connotazione look-ahead specifica in PCRE. Per sostituire ogni occorrenza di tale parola con il valore [hidden], utilizza l'esempio seguente.

SELECT REGEXP_REPLACE('passwd7 plain A1234 a1234', '(?=[^ ]*[a-z])(?=[^ ]*[0-9])[^ ]+', '[hidden]', 1, 'p');

+-------------------------------+
|        regexp_replace         |
+-------------------------------+
| [hidden] plain A1234 [hidden] |
+-------------------------------+

Nell'esempio seguente viene utilizzato un modello scritto in dialetto PCRE per individuare le parole contenenti almeno un numero e una lettera minuscola. Utilizza l'operatore ?=, che ha una connotazione look-ahead specifica in PCRE. Per sostituire ogni ricorrenza di tale parola con il valore [hidden], ma diversamente dall'esempio precedente in quanto utilizza la corrispondenza senza distinzione tra maiuscole e minuscole, utilizza l'esempio seguente.

SELECT REGEXP_REPLACE('passwd7 plain A1234 a1234', '(?=[^ ]*[a-z])(?=[^ ]*[0-9])[^ ]+', '[hidden]', 1, 'ip');

+----------------------------------+
|          regexp_replace          |
+----------------------------------+
| [hidden] plain [hidden] [hidden] |
+----------------------------------+

Negli esempi seguenti vengono utilizzati i dati della tabella USERS database di esempio TICKIT. Per ulteriori informazioni, consulta Database di esempio.

Per eliminare @ e il nome di dominio dagli indirizzi e-mail, utilizza l'esempio seguente.

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

Per sostituire i nomi di dominio degli indirizzi e-mail con internal.company.com, utilizza l'esempio seguente.

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