Guida completa al lavoro con le sottostringhe in SQL

In questo articolo discuteremo di come lavorare con le sottostringhe in SQL. Si tratta di un'operazione che implica l'apprendimento di diversi approcci, di cui vi mostreremo i vantaggi e gli svantaggi.

Per lavorare con le sottostringhe in SQL, dobbiamo innanzitutto sapere cos'è una stringa. In programmazione, una stringa è un qualsiasi tipo di dato testuale (un titolo, una descrizione, un nome). Una stringa è composta da qualsiasi numero e tipo di caratteri. In un database SQL, le stringhe sono generalmente memorizzate come tipi di dati CHAR o VARCHAR.

Una sottostringa è una parte di una stringa. In altre parole, una sottostringa è una parte più piccola della sequenza di caratteri. Se si avesse la stringa 'Corso SQL', 'SQL' sarebbe una sottostringa di quella stringa. Questo è illustrato nell'immagine seguente:

Si noti che l'immagine (e una stringa) non ignora gli spazi vuoti; anche gli spazi vuoti sono caratteri di una stringa.

Preparatevi a lavorare con le sottostringhe SQL

Conoscere la famiglia delle funzioni di testo SQL è obbligatorio quando si lavora con le stringhe. Anche se manterremo gli esempi di sottostringa il più semplici possibile, avremo bisogno di funzioni di testo. L'uso di queste funzioni spesso richiede la conoscenza di altri concetti SQL, come JOINs, il raggruppamento dei dati, il filtraggio dei dati e l'uso di subquery. Potete esercitarvi su tutti questi concetti nel nostro SQL Practice percorso. Ci sono otto corsi di esercitazione per un totale di oltre 950 sfide di codifica, per cui alla fine del corso vi faranno male le dita:) Ma è proprio questo il punto, perché la pratica è l'unico modo per diventare fluenti in SQL.

In questa sede utilizzeremo lo stesso approccio e vi mostreremo il maggior numero di esempi possibile in questo articolo. Quando si estrae una sottostringa da una stringa in SQL, la funzione di testo SUBSTRING(), dal nome evidente, sembra una scelta ovvia. Tuttavia, non è l'unica scelta possibile! Ce ne sono altre, come l'operatore LIKE e le funzioni di testo LEFT() e RIGHT().

Anche se questo può sembrare facile, lavorare con le sottostringhe SQL non è sempre semplice. Bisogna conoscere le sfumature di ogni funzione che si vuole applicare, come funziona l'indicizzazione in SQL e come SQL gestisce i dati testuali. Molti professionisti dei dati confermeranno che lavorare con le sottostringhe in SQL può diventare un'esperienza dolorosa e frustrante!

Noi faremo in modo che non lo sia per voi. Iniziamo.

Esempio di set di dati

Lavoreremo con la tabella clients. Come suggerisce il nome, si tratta di un elenco di clienti dell'azienda.

È possibile creare la tabella utilizzando questa query. Si noti che la data di nascita è memorizzata in un formato di dati di testo.

Cominciamo con l'estrazione delle sottostringhe!

Controllare se una stringa contiene una sottostringa in SQL

Un modo comune per verificare se un testo contiene una determinata sottostringa in SQL è quello di usare l'operatore LIKE o ILIKE. Vengono utilizzati con la clausola WHERE per filtrare i dati. Entrambi gli operatori trovano le stringhe con una sottostringa simile a quella definita nel loro schema. L'unica differenza è che LIKE è sensibile alle maiuscole e minuscole, mentre ILIKE non lo è.

Utilizzare LIKE e ILIKE per verificare se il testo contiene una sottostringa

Un vostro collega vi dice che un cliente ha chiamato con un messaggio per voi. Poiché il collega aveva fretta, ha dimenticato di annotare il nome del cliente. Il nome del cliente era Isaac, o Dave, o qualcosa del genere.

Si può cercare di rintracciare il cliente di nome Isaac o Dave scrivendo questa query:

SELECT *
FROM clients
WHERE first_name LIKE 'Dave' OR first_name LIKE 'Isaac';

In questa query si selezionano tutte le colonne della tabella. Poi si utilizza la clausola WHERE e l'operatore LIKE. Nell'operatore LIKE si inseriscono i nomi 'Dave' e 'Isaac' tra virgolette singole. La condizione nella clausola WHERE può essere letta come: "Restituisci tutti i clienti con Dave o Isaac come nome".

Eseguiamo il codice e vediamo... che non c'è assolutamente nulla! Il codice non restituisce alcuna riga. Stupido codice! Se guardiamo la tabella, vediamo che ci sono clienti di nome Isaac Guardiola e Dave Trotter:

Allora perché non funziona? La query precedente presuppone che la sottostringa inizi con 'Dave' o 'Isaac'. Osservando la tabella, si può notare che la stringa inizia in realtà con il loro saluto.

Per far funzionare la query, si deve usare il carattere jolly '%' con LIKE. Questo carattere jolly viene utilizzato per cercare una sottostringa all'inizio, alla fine e in qualsiasi punto intermedio. Tutto dipende dalla posizione del carattere jolly. Ecco una breve panoramica su come usarlo.

Riscriviamo la query:

SELECT *
FROM clients
WHERE first_name LIKE '%Dave%' OR first_name LIKE '%Isaac%';

Abbiamo imparato che i caratteri jolly prima e dopo la sottostringa che vogliamo trovare significano che la query cercherà questa sottostringa in qualsiasi punto della stringa. Questo ci permette di evitare il saluto davanti a ogni nome.

Ecco il risultato:

Per dare un altro esempio di utilizzo di '%' con LIKE, si possono anche cercare tutti i signori nel nostro elenco di clienti.

Per farlo, si inserisce il carattere jolly dopo la sottostringa da ricercare, dato che la sottostringa inizia con "Mr.":

SELECT *
FROM clients
WHERE first_name LIKE 'Mr.%';

Ecco l'output.

Probabilmente avrete notato che scriviamo le sottostringhe in LIKE esattamente come sono scritte nella tabella per quanto riguarda la capitalizzazione. Questo perché l'operatore LIKE è sensibile alle maiuscole e alle minuscole.

Se si vuole evitare questo problema o non si è sicuri di come sono formattati i dati, si può usare l'operatore ILIKE. Poiché è insensibile alle maiuscole e alle minuscole, è possibile scrivere la sottostringa in qualsiasi modo:

SELECT *
FROM clients
WHERE first_name ILIKE '%dave%' 
OR first_name ILIKE '%iSAaC%';

L'output è lo stesso di LIKE:

Per saperne di più su LIKE (e NOT LIKE), consultare questo articolo.

La funzione SUBSTRING()

SUBSTRING() è una funzione SQL utilizzata per estrarre le sottostringhe da una stringa. Consente di specificare la stringa da cui si desidera estrarre la sottostringa. Si definisce la sottostringa specificando la sua posizione iniziale all'interno della stringa e la sua lunghezza.

Ecco la sintassi della funzione:

SUBSTRING(string, start_position, length)

Come ogni funzione SQL, SUBSTRING() accetta argomenti tra le parentesi. Due argomenti sono obbligatori:

• string - La stringa che si vuole cercare; può essere una costante o una colonna/espressione.
• start_position - Un valore intero che definisce la posizione (il numero ordinale del carattere) in cui inizia la sottostringa, cioè la posizione del primo carattere della sottostringa.

Il terzo argomento è length, facoltativo. Definisce la lunghezza di una sottostringa come il numero di caratteri che conterrà. Se questo argomento viene omesso, la sottostringa sarà il resto della stringa, contando dalla posizione iniziale.

Vediamo alcuni esempi per chiarire tutto questo.

Utilizzare SUBSTRING() per estrarre una sottostringa da una stringa costante

Una stringa costante è qualsiasi stringa scritta tra apici singoli come primo argomento in SUBSTRING().

Ad esempio, la stringa costante nel codice seguente è 'SQL Practice Course' e si vuole estrarre 'Practice Course'.

SELECT SUBSTRING ('SQL Practice Course', 5) 
AS substring_extract

Il numero intero 5 è l'argomento di start_position. Significa che la sottostringa inizia dal quinto carattere della stringa.

Perché scegliere 5 se ci sono solo tre lettere ('SQL') davanti alla nostra sottostringa? La sottostringa non dovrebbe iniziare dalla quarta posizione? Attenzione: Anche lo spazio vuoto conta come un carattere in una stringa!

Poiché abbiamo omesso l'argomento length, il codice restituirà il resto della stringa come una sottostringa. In altre parole, il codice restituisce questo risultato:

Utilizzare SUBSTRING() per estrarre una sottostringa da una colonna

Invece di un valore costante, si può anche specificare una colonna da cui si vuole estrarre una sottostringa.

Mostriamo il nome e il cognome del cliente, ma senza il saluto (Mr./Ms./Mx.):

SELECT SUBSTRING (first_name, 5) AS first_name,
	 last_name
FROM clients;

Utilizziamo ancora una volta la funzione SUBSTRING(). Il primo argomento è il nome della colonna, che è first_name. Il secondo argomento è la posizione iniziale della sottostringa, che è cinque. Ricordate di contare gli spazi vuoti! Ce n'è uno dopo il saluto, quindi "Mr." è composto da quattro caratteri. Il quinto carattere è la prima lettera del nome del cliente.

Abbiamo omesso la lunghezza della sottostringa e la seconda colonna selezionata è il cognome del cliente, quindi il codice restituisce questo:

Utilizzare SUBSTRING() per estrarre una sottostringa di lunghezza definita

Ora smetteremo di evitare il terzo argomento SUBSTRING(). In questo esempio, mostreremo come estrarre il giorno e il mese di nascita dei clienti:

SELECT first_name,
	 last_name,
	 SUBSTRING (date_of_birth, 1, 6) AS birthday
FROM clients;

Dopo aver selezionato il nome e il cognome dei clienti, utilizziamo la funzione SUBSTRING(). Utilizzeremo la colonna date_of_birth per estrarre la data di nascita del cliente (mese e giorno).

Dopo aver passato la colonna come argomento, specifichiamo la posizione iniziale. Il numero 1 significa che la sottostringa verrà estratta dal primo carattere della stringa. Il numero 6 significa che la sottostringa sarà composta da sei caratteri: due caratteri per il giorno, un punto, due caratteri per il mese e un altro punto.

Guardate il risultato:

Tutti i clienti sono presenti, insieme ai loro compleanni, estratti nel modo desiderato. Questa volta non ci dispiace mostrare il saluto.

Uso di SUBSTRING() con altre funzioni per trovare un indice di sottostringa

Esistono funzioni che possono essere utilizzate per trovare un indice all'interno della stringa. In MySQL e PostgreSQL, questa funzione si chiama POSITION(); in SQL Server si chiama CHARINDEX(). È possibile specificare la sottostringa che si sta cercando e queste funzioni restituiranno la sua posizione all'interno della stringa.

Queste funzioni sono estremamente utili in combinazione con SUBSTRING(). Parliamo prima di POSITION() e poi vedremo un esempio con CHARINDEX().

La funzione POSITION() consente di specificare la sottostringa e ne trova la posizione iniziale. Usiamola per estrarre il nome utente di ogni cliente dal suo indirizzo e-mail:

SELECT first_name,
	 last_name,
	 email,
	 POSITION('@' IN email) AS at_position,
	 SUBSTRING(email, 1, POSITION('@' IN email)-1) AS username
FROM clients;

Sopra, usiamo POSITION() per trovare la '@' nell'indirizzo e-mail di ogni utente. Ne abbiamo bisogno perché tutto ciò che viene prima di questo carattere è il nome dell'utente. La sintassi di POSITION() è semplice: basta specificare il carattere di ricerca tra virgolette singole, quindi farlo seguire dalla parola chiave IN e dal nome delle colonne in cui si vuole cercare.

Questa colonna restituisce solo la posizione del carattere. Dobbiamo incorporarla in SUBSTRING() per ottenere il nome utente. I primi due argomenti di SUBSTRING() sono familiari: stiamo guardando la colonna email e vogliamo che la nostra sottostringa (un nome utente) inizi con il primo carattere della stringa.

Il terzo argomento di SUBSTRING() è la lunghezza della sottostringa. La lunghezza del nome utente è il numero di caratteri che precedono ed escludono "@". In altre parole, la lunghezza del nome utente è la posizione di '@' meno uno. Perché meno? Perché altrimenti '@' farebbe parte del nome utente.

Possiamo verificare il risultato. In 'iguardiola@gmail.com', il simbolo '@' è in posizione 11, come mostrato nella colonna at_position. La colonna nome utente mostra la sottostringa prima di '@', che è effettivamente 'iguardiola' per questo cliente. È possibile verificare il resto dei risultati nello stesso modo.

Lo stesso codice in SQL Server dovrebbe utilizzare la funzione CHARINDEX():

SELECT first_name,
	 last_name,
	 email,
	 CHARINDEX('@', email) AS at_position,
	 SUBSTRING(email, 1, CHARINDEX('@', email)-1) AS username
FROM clients;

La logica è simile a quella della funzione POSITION(): si specifica la sottostringa che si sta cercando e poi la colonna, con gli argomenti separati da una virgola.

L'output del codice è lo stesso.

Abbiamo finito con SUBSTRING(). Ma se avete bisogno di fare più pratica, ecco altri cinque esempi di SUBSTRING().

LEFT() e RIGHT() in SQL

Un altro modo per estrarre una sottostringa è quello di utilizzare le funzioni LEFT() o RIGHT().

LEFT() estrae la sottostringa da sinistra, consentendo di definire la lunghezza della sottostringa. La funzione RIGHT() fa la stessa cosa ma dal lato destro.

Usare LEFT() e RIGHT() per estrarre una sottostringa

Utilizziamo queste due funzioni per estrarre alcune sottostringhe: le prime tre lettere di un cognome e l'anno di nascita.

Ecco il codice.

SELECT first_name,
	 last_name,
	 LEFT(last_name, 3) AS last_name_substring,
	 RIGHT(date_of_birth, 4) AS year_of_birth
FROM clients;

Utilizziamo la funzione LEFT() per ottenere le prime tre lettere del cognome. Parte da sinistra e prende il numero di caratteri specificato per creare una sottostringa. La sintassi è semplice: si specifica il nome della colonna e poi la lunghezza della sottostringa.

RIGHT() viene utilizzato per trovare l'anno di nascita, partendo da destra e utilizzando il numero di caratteri specificato per creare una sottostringa. Qui il primo argomento è la colonna date_of_birth. Poi contiamo il numero di caratteri che vogliamo a partire da destra, cioè quattro: questo restituirà l'anno di nascita.

Poiché LEFT() e RIGHT() sono sostanzialmente immagini speculari l'una dell'altra, avremmo potuto facilmente utilizzare una di queste funzioni per trovare entrambe le sottostringhe richieste.

Ecco l'output della query precedente:

Mostra le prime tre lettere del cognome e l'anno di nascita.

Diventare colui che estrae tutte le (sotto)stringhe in SQL

In qualità di analista di dati, ci si aspetta che si tirino tutte le (sotto)stringhe da soli. Uno dei trucchi per riuscirci è conoscere vari modi per lavorare con le sottostringhe, come quelli che vi abbiamo mostrato qui.

Conoscere le diverse funzioni per gestire le sottostringhe consente di scegliere quella più adatta al proprio problema. Non solo si otterrà il risultato desiderato, ma si otterrà anche un codice più efficiente e con meno righe.

Per arrivare a questo livello, è necessario lavorare molto con le stringhe e utilizzare quotidianamente tutte le funzioni citate. In altre parole, è necessario scrivere codice. Se avete bisogno di un ambiente sicuro per farlo, il nostro SQL Practice corso offre esattamente questo.

Buona fortuna per diventare il maestro delle sottostringhe SQL!