SQL SUBSTRING : come estrarre una specifica porzione di testo

SUBSTRING è una nota funzione del linguaggio SQL che può essere utilizzata per estrarre solo i caratteri che si trovano in una determinata posizione all’interno di una stringa di testo. In questo articolo ne andiamo ad analizzare la sintassi e riportiamo degli utili casi d’uso.

SUBSTRING su SQL Server

Partiamo analizzando la sintassi della SUBSTRING su SQL Server. Supponiamo ad esempio di avere una colonna contenente i codici fiscali dei clienti. Sappiamo che i caratteri dal settimo all’ottavo del codice fiscale ci forniscono informazioni sulla data di nascita del cliente. Ad esempio, analizzando il codice fiscale ABCABL90R17L113A deduciamo che le ultime due cifre dell’anno di nascita sono 90. Se volessimo scrivere una query SQL per estrarre questa informazioni dovremmo usare la funzione SUBSTRING!

SELECT 
 CodiceFiscale,
 SUBSTRING(
     CodiceFiscale,
     7,
     2) AS AnnoNascita          
FROM dbo.Clienti;

Analizzando la sintassi notiamo che dopo SUBSTRING dobbiamo inserire tre valori:

  • la colonna contenente il testo originale di riferimento (CodiceFiscale);
  • la posizione del carattere di partenza (7). Su quasi tutti i database inizieremo a contare da 1;
  • il numero di caratteri da visualizzare (2).

Ecco l’output finale

esempio risultato funzione Substring su SQL Server

Calcolare dinamicamente i parametri di SUBSTRING con la funzione CHARINDEX

In alcuni casi la posizione di partenza non è la stessa per tutte le righe, ma va calcolata dinamicamente in base alla posizione di un determinato carattere. Pensiamo ad esempio al caso in cui vogliamo estrarre il dominio da un indirizzo email. In tutti i casi dovremmo partire dal carattere successivo a “@”, ma questa posizione sarà differente da indirizzo a indirizzo. Ad esempio in nicola.iantomasi@yimp.it partiremo dal carattere 18, mentre in nicola@yimp.it partiremo dal carattere 8.

In questi casi possiamo utilizzare la SUBSTRING insieme alla funzione CHARINDEX che ci permette di individuare la prima occorrenza di un carattere all’interno di una determinata stringa. Vediamo il codice SQL relativo e poi lo commentiamo:

SELECT 
 Email,
 SUBSTRING(
     Email,
     1 + CHARINDEX('@', Email),
     500) AS Dominio
FROM dbo.Clienti;

In questo caso il secondo parametro di SUBSTRING è calcolato a partire dal risultato della funzione CHARINDEX che riporta la posizione della prima occorrenza del carattere “@” all’interno della colonna Email. Siccome dobbiamo partire dal carattere successivo, a questo valore aggiungiamo un “+1” all’interno della SUBSTRING.

Nel terzo parametro di SUBSTRING abbiamo inserito invece un numero abbastanza grande (500) per assicurarci di prendere tutti i caratteri fino alla fine della stringa. Ecco il risultato:

esempio codice SQL con substring e charindex

Nel caso in cui una riga della tabella non contenesse il carattere “@” all’interno di email, allora il risultato di charindex sarebbe zero. Di conseguenza la substring riporterebbe erroneamente l’indirizzo email completo. Per gestire anche questo caso possiamo usare la funzione CASE WHEN in questo modo:

SELECT 
 Email,
 CASE 
  WHEN 
   CHARINDEX('@', Email) > 0
  THEN SUBSTRING(Email,
                 1 + CHARINDEX('@', Email),
                 500)
  ELSE
   'Non trovato'
 END AS Dominio
FROM dbo.Clienti;

Puoi trovare altri casi d’uso di SUBSTRING e CHARINDEX in questo mio articolo di approfondimento sull’ETL.

SUBSTRING su MySQL

Su MySQL continua a valere quanto abbiamo già detto su SQL Server ad eccezione della funzione CHARINDEX che va sostituita con la funzione INSTR che richiede i parametri in ordine invertito. Su MySQL ad esempio scriveremo

INSTR(Email,'@')

invece della sintassi di SQL Server

CHARINDEX('@', Email)

Inoltre su MySQL la funzione SUBSTRING ci garantisce maggiore flessibilità in quanto possiamo:

  • non inserire il terzo parametro e con questo sottintendere che vogliamo estrarre tutto il resto della stringa a partire dalla posizione indicata;
  • inserire nel secondo parametro un numero negativo per indicare che il punto di partenza deve essere calcolato contando da destra verso sinistra.
Video Corso Data Analysis completo

Esiste una funzione simile alla SUBSTRING su Excel?

La risposta è sì! Nella versione in italiano è la funzione STRINGA.ESTRAI (in inglese MID) e funziona esattamente come la SUBSTRING di SQL Server. Per estrarre l’anno dal codice fiscale, scriveremo ad esempio

=STRINGA.ESTRAI(A2,7,2)

dove A2 è la cella contenente il codice fiscale.

esempio funzione stringa estrai su excel

Puoi approfondire l’uso della funzione STRINGA.ESTRAI sulla documentazione ufficiale di Microsoft.

E su pandas?

Anche sui DataFrame della libreria Pandas di Python esiste ovviamente un metodo che permette di estrarre una particolare porzione di testo da una colonna di tipo stringa. La sintassi è un po’ diversa, per estrarre l’anno dal codice fiscale scriveremo ad esempio

clienti["CodiceFiscale"].str.slice(start = 6, 
                                   stop = 8, 
                                   step = 1 )

È fondamentale osservare che su pandas il primo carattere corrisponde alla posizione zero (e non a 1 come su SQL Server, MySQL ed Excel).

Conclusioni

La funzione SUBSTRING può essere usata in modo molto versatile per estrarre una particolare porzione di testo. Essa è molto utilizzata anche nelle fasi di caricamento e pulizia dei dati, anche in combinazione con altre funzioni come CHARINDEX e CASE WHEN. Fai un upgrade delle tue skill di SQL con i due video corsi https://www.yimp.it/corso-sql-academy/ e https://www.yimp.it/video-corso-t-sql-avanzato-tuning-query/

Altri articoli utili sul mio blog:

Torna in alto
Torna su