La funzione SQL Replace: come e quando usarla

Il linguaggio SQL dispone di numerose funzioni per manipolare e gestire le stringhe di testo. In questo articolo vedremo in particolare come usare la funzione SQL Replace. La funzione Replace ci permette di sostituire uno o più caratteri presenti in una colonna di tipo stringa (ad esempio varchar o nvarchar).

 

Funzione Replace su Sql Server: sintassi

La funzione T-SQL Replace richiede tre argomenti, tutti obbligatori:

  • la colonna o il valore in cui effettuare la sostituzione;
  • il testo da sostituire;
  • il testo con cui effettuare la sostituzione.

Vediamo un esempio:

SELECT REPLACE('testo',
               't',
               'b') AS TestoModificato;

L’output di questa query sarà la stringa “besbo”. Osserviamo che entrambi i caratteri “t” presenti nel testo sono stati sostituiti da una “b”. Se le impostazioni di default non vengono modificate, i database creati su Sql Server non sono case sensitive. Di conseguenza effettuando la Replace alla parola Testo con la prima t maiuscolo, otterremmo lo stesso risultato: una doppia sostituzione di entrambe le t (sia la maiuscola che la minuscola) con b.

Nel caso in cui volessimo effettuare una sostituzione Case Sensitive, senza dover ovviamente modificare l’impostazione generale di tutto il database, possiamo usare la sintassi COLLATE all’interno del primo argomento della funzione. Ad esempio la query

SELECT REPLACE('Testo' COLLATE Latin1_General_CS_AS,
               't',
               'b') AS TestoModificato;

restituirà come output Tesbo, con soltanto la t minuscola sostituita.

Come usare la funzione Replace per eliminare i caratteri speciali in SQL

La funzione Replace può essere utilizzata in modo astuto anche per eliminare un particolare carattere. Basterà infatti specificare come terzo argomento la stringa vuota.  Ad esempio

SELECT REPLACE('testo',
                't',
                '') AS TestoModificato;

restituirà “eso”. Partendo da questa idea, la funzione Replace viene spesso utilizzata per “pulire” le stringhe dai caratteri speciali. Su SQL Server possiamo eliminare più caratteri annidando più funzioni REPLACE l’una dentro l’altra, come in questo esempio.

DECLARE @stringa VARCHAR(50) = '!Testo.';
SELECT 
 REPLACE(REPLACE(REPLACE(REPLACE(@stringa,
                                 '.',
                                 ''),
                          ',',
                          ''),
                 '!',
                 ''),
         '?',
         '');

Il codice però risulta di difficile lettura e manutenzione. Su Oracle abbiamo invece a disposizione la potentissima funzione REGEXP_REPLACE. Possiamo raggiungere lo stesso risultato precedente con uno sforzo molto minore utilizzando le espressioni regolari, ad esempio in questo modo

SELECT REGEXP_REPLACE('!Testo.', 
                      '[.,!?]',
                      '')
FROM   DUAL;

A questo link https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions130.htm trovi la documentazione completa di Oracle su regexp_replace.

Il tuo percorso completo di Analisi dei Dati

Funzione Replace su Oracle: sintassi

Torniamo su Oracle per sottolineare una piccola differenza a livello di sintassi. Il terzo parametro della funzione infatti è opzionale, se non specificato l’effetto sarà quello di eliminare tutte le occorrenze del carattere specificato nel secondo argomento. Inoltre ricordiamo che di default i database Oracle sono case sensitive, di conseguenza occorre fare particolare attenzione all’utilizzo di maiuscole e minuscole che risulta determinante nel risultato della query.

Funzione Replace su MySQL: fate attenzione!

Anche se di default i database MySQL sono case insensitive, la funzione replace farà comunque differenze tra maiuscolo e minuscolo. Ad esempio su MySQL il codice

SELECT REPLACE( 'Testo',
                't', 
                'c') AS TestoModificato;

restituirà Tesco, viene sostituita cioè soltanto la “t” minuscola.

Impatto della funzione Replace nella creazione di una nuova tabella

Studiamo infine un ultimo caso rilevante in cui la funzione Replace è utilizzata su SQL Server all’interno di un costrutto SELECT – INTO per creare e popolare contemporaneamente una tabella. Lanciamo il seguente codice

DECLARE @stringa VARCHAR(50) = 'Testo';
SELECT REPLACE(@stringa,
               't',
               'b') AS Valore
INTO #test;
GO;
USE tempdb;
GO;
sp_help #test;

Un po’ a sorpresa noteremo che la colonna Valore sarà creata con il tipo varchar(8000). Possiamo evitare questo comportamento inserendo prima della Replace la funzione Convert.

Conclusioni sulla funzione Replace

La funzione Replace è tra le funzioni SQL più utilizzate in assoluto. Grazie alla sua flessibilità spesso è utilizzata durante le attività di Data Quality e pulizia dei dati. Occorre comunque fare attenzione alle varie differenze che ci sono tra l’implementazione su SQL Server, Oracle e MySQL. Se sei interessato a una formazione di livello sul linguaggio SQL puoi visualizzare le pagine del corso base-intermedio https://www.yimp.it/corso-sql-academy/ o del corso avanzato su SQL Server https://www.yimp.it/video-corso-t-sql-avanzato-tuning-query/.

Ecco un’altra lista di articoli utili del mio blog:

Scroll to Top
Torna su