La funzione SQL Substring in 5 esempi

Lavorate con dati di testo in SQL? Vi spieghiamo come ottenere valori da qualsiasi punto di una stringa.

Quando si pensa di lavorare con i dati in SQL, il primo pensiero è probabilmente quello di un database pieno di numeri e del codice SQL che esegue calcoli molto sofisticati. Ma anche il testo è un dato! È molto comune trovare dati di testo nei database. Non solo bisogna estrarli, ma spesso bisogna anche manipolarli. Le funzioni che permettono di farlo si chiamano funzioni di testo.

Per tutti coloro che vogliono esercitarsi con le funzioni SQL, consiglio il nostro corso interattivo. Standard SQL Functions interattivo. Contiene 211 esercizi e insegna come utilizzare le più comuni funzioni di testo, numeriche e di data e ora in SQL.

Una delle funzioni di testo più comuni trattate nel corso è SUBSTRING(). In questo articolo sono riportati cinque esempi di lavoro reali che coprono gli usi principali di questa funzione. Alcuni esempi potrebbero risultare complicati se non si ha familiarità con le funzioni di testo, quindi è bene tenere a portata di mano il foglio informativo Standard SQL Functions o una panoramica delle funzioni di testo SQL.

Che cos'è la funzione SUBSTRING()?

SUBSTRING() è una funzione di testo che consente di estrarre caratteri da una stringa. La sua sintassi è

SUBSTRING(expression, start, length)

Per l'argomento expression si scrive un letterale di stringa o si specifica una colonna da cui si vuole estrarre la sottostringa. L'argomento start è un numero intero che indica la posizione numerica del carattere nella stringa in cui inizia la sottostringa. L'argomento length, come dice il nome, definisce la lunghezza, un valore intero, della sottostringa da restituire.

Come funziona SUBSTRING()?

L'indizio è nel nome stesso della funzione. Una sottostringa è una stringa all'interno della stringa principale. Pertanto, SUBSTRING() estrae una sottostringa come specificato nel suo argomento.

Funziona così:

Nella stringa precedente, la sottostringa che inizia alla posizione 1 e ha una lunghezza di tre caratteri è ‘STR’.

Ora che i principi sono stati chiariti, vi mostrerò alcuni esempi. Iniziando, ovviamente, da quello più semplice!

Esempio 1: sottostringa di una stringa letterale

La funzione SUBSTRING() restituisce una sottostringa da una stringa qualsiasi. La stringa può essere scritta esplicitamente come argomento, in questo modo:

SELECT SUBSTRING('This is the first substring example', 9, 10) AS substring_extraction;

Questo significa: Voglio trovare una sottostringa dal testo ‘This is the first substring example’. Gli argomenti dicono che la sottostringa inizia dal 9° carattere della stringa e che la sua lunghezza è di 10 caratteri.

Vediamo cosa restituisce questo codice:

C'è una colonna e una riga. La sottostringa estratta è ‘the first’. Questo è l'uso più elementare di SUBSTRING(); il codice non usa nemmeno le tabelle!

La tabella dei dipendenti

Per mostrarvi esempi più interessanti, ho bisogno di alcuni dati. Vi presento una tabella chiamata employees.

La tabella memorizza le informazioni sui dipendenti di un'azienda immaginaria, Kooler, nelle seguenti colonne:

• id - L'ID del dipendente.
• first_name - Il nome del dipendente.
• last_name - Il cognome del dipendente.
• email - L'e-mail del dipendente.
• job_title - Il titolo di lavoro del dipendente.
• department - Il reparto del dipendente.
• start_date - La data di inizio del rapporto di lavoro con Kooler.

Ecco le prime righe per avere un'idea dei dati:

Esempio 2: Substringa di una colonna

Come si può immaginare, scrivere esplicitamente l'espressione stringa non è l'unico modo per utilizzare SUBSTRING(). È anche possibile utilizzarla su una colonna di una tabella.

Ecco un esempio. Voglio trovare le iniziali di tutti i dipendenti. Uso la colonna email perché so che le prime due lettere dell'indirizzo e-mail sono le iniziali:

SELECT first_name,
	 last_name,
	 email,
	 SUBSTRING(email, 1, 2) AS employee_initials
FROM employees;

Specifico la colonna email nella funzione. Ottenere le prime due lettere dell'indirizzo e-mail significa che la sottostringa inizia dal primo carattere per una lunghezza di due caratteri. In questo modo si ottiene il risultato desiderato:

Esempio 3: Substringa senza l'argomento lunghezza

È possibile omettere l'argomento lunghezza in SUBSTRING(), e la funzione funziona comunque. Un buon esempio è quello in cui si vuole mostrare solo l'anno della data di inizio dell'impiego. La colonna start_date non è molto adatta a questo scopo. Questa data è scritta come dato di testo nel formato MM/AAAA.

Fortunatamente, SUBSTRING() risolve questo problema:

SELECT first_name,
	 last_name,
	 start_date,
	 SUBSTRING(start_date, 4) AS start_year
FROM employees;

Per ottenere l'anno dalla colonna start_date è sufficiente definire l'inizio della sottostringa. In questo codice, la sottostringa inizia dal quarto carattere. Poiché ometto l'argomento della lunghezza, la lunghezza della sottostringa è quella che manca alla fine della stringa dal quarto carattere. In questo modo si ottiene facilmente l'anno, come si vede di seguito:

Esempio 4: POSITION() e CHARINDEX()

Torniamo a lavorare con le e-mail. Per politica aziendale, il punto locale di un indirizzo e-mail (cioè la parte prima di '@') è anche il nome utente del dipendente per accedere a tutte le applicazioni aziendali. È necessario estrarre questo nome utente. Ecco come fare:

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

I primi due argomenti sono quelli già visti. Voglio estrarre una sottostringa dalla colonna email e voglio che inizi dal primo carattere della stringa. Ma ora la lunghezza della sottostringa è diversa per ogni dipendente. Come faccio a dire alla funzione di restituire tutti i caratteri prima del segno '@'?

Uso POSITION(), che è equivalente a CHARINDEX() in SQL Server o MySQL. Individua il carattere specificato nella stringa e ne restituisce la posizione numerica. Quindi, la lunghezza della sottostringa che rappresenta il nome utente del dipendente è uguale a POSITION('@' IN email)-1. Perché meno uno? Perché non voglio che '@' sia incluso nel nome utente del dipendente.

Questo è il risultato:

Esempio 5: LENGTH() + POSITION()

L'ultimo esempio mostra come trovare la posizione lavorativa di un dipendente a partire dai dati. Lavorando in Kooler, so come vengono formati i titoli delle mansioni: prima viene l'anzianità del dipendente, poi il reparto, quindi la posizione. Ad esempio, "Assistente alle vendite junior" significa che il dipendente ha un'anzianità di servizio inferiore, lavora nelle vendite e come assistente.

Utilizzando SQL, è possibile estrarre questo dato come sottostringa:

SELECT first_name,
       last_name,
       job_title,
       SUBSTRING(job_title, LENGTH(job_title) - POSITION(' ' IN REVERSE(job_title))+2) AS position
FROM employees;

Questo è un altro esempio di omissione dell'argomento lunghezza, anche se un po' più complesso. Come sempre, per prima cosa devo specificare la colonna della stringa - in questo caso job_title. Dopodiché, devo in qualche modo trovare una sottostringa composta solo dall'ultima parola del titolo del lavoro.

Lo faccio utilizzando prima LENGTH(). Questo restituisce la lunghezza della stringa nella colonna job_title. È un inizio: è la lunghezza di tutte e tre le parole insieme, compresi gli spazi vuoti. Se potessi in qualche modo sottrarre il numero di caratteri dell'ultima parola, avrei la lunghezza delle prime due parole, che mi darebbe l'inizio della sottostringa desiderata.

È un po' complicata perché i nomi delle posizioni lavorative hanno lunghezze diverse. L'unica cosa che separa le parole è lo spazio bianco. Quindi, per ottenere la lunghezza della terza parola nella stringa, devo contare il numero di caratteri fino allo spazio vuoto, ma da destra.

La funzione POSITION() salva di nuovo la situazione, ma questa volta in combinazione con REVERSE(). La funzione REVERSE() inverte l'espressione della stringa in modo che "Junior Sales Assistant" diventi "tnatsissA selaS roinuJ". L'ultima parola diventa la prima; anche la parola stessa è invertita, ma questo non ha importanza.

POSITION() trova la posizione dello spazio vuoto dopo la prima parola della stringa invertita. Questa posizione è uguale a quella dello spazio vuoto prima dell'ultima parola della stringa originale (non invertita).

Uff! Ora, se sottraggo questo numero dalla lunghezza totale della stringa originale, ottengo l'inizio della sottostringa, giusto?

Beh, non proprio! Utilizzando questa differenza si ottiene una sottostringa che include l'ultima lettera della seconda parola e lo spazio vuoto prima dell'ultima parola. Perché?

Per due motivi. L'argomento iniziale della funzione SUBSTRING() è inclusivo. Inoltre, POSITION() calcola la posizione dello spazio vuoto, non il numero di caratteri fino allo spazio vuoto. Quindi, devo aggiungere 2 per ottenere questo risultato:

Ora che ho introdotto alcune altre funzioni, potreste voler dare un'occhiata ad altre funzioni di testo che potrebbero esservi utili.

Ulteriori informazioni su SUBSTRING () e sul lavoro con i dati di testo

Ora sapete quando e come usare SUBSTRING(). È ora di fare pratica!

Esistono altre funzioni di testo, non solo SUBSTRING(). Potete trovarle (e molto altro ancora!) nel corso Standard SQL Functions corso.