Corrispondenza di pattern con SQL LIKE: guida pratica con esempi

Supponiamo di avere una tabella employees e di voler trovare tutti i nomi che iniziano con “A”:

Potresti passare del tempo a scorrere la tabella manualmente. Ma perché farlo quando hai l’operatore LIKE?

SELECT DISTINCT
	first_name
FROM employees
WHERE first_name LIKE 'A%'

La magia qui sta nella clausola `WHERE first_name LIKE ‘A%’`, che significa “trova tutti i first_name che iniziano con "A" e proseguono con un qualsiasi numero di caratteri”. Qui A% è noto come pattern per il matching.

Il % non è l’unico wildcard che puoi usare insieme all’operatore LIKE. Puoi usare anche il carattere di underscore _:

• `%` corrisponde a qualsiasi numero di caratteri.
• _ corrisponde a un singolo carattere.

La sintassi è facile da ricordare. È semplicemente:

column_name LIKE pattern

Puoi usare LIKE per ottenere vari tipi di pattern matching. Ecco come:

Esempi base di SQL LIKE

Di seguito abbiamo riassunto alcuni esempi pratici di come puoi usare l’istruzione LIKE e i risultati dal nostro set di dati di esempio.

1. Usa LIKE per la corrispondenza esatta di una stringa

Se vuoi effettuare una corrispondenza esatta, usa LIKE senza “%” o “_”

SELECT
    first_name, last_name
FROM employees
WHERE first_name LIKE 'Barry' -- the same as WHERE first_name = ‘Barry’

2. Usa “%” per far combaciare qualsiasi numero di caratteri

“%” può essere usato per far combaciare qualsiasi (anche zero) numero di caratteri: cifre, lettere o simboli.

Supponi di voler trovare tutti i dipendenti il cui nome inizia con “Adam”; puoi usare il pattern “Adam%”

SELECT DISTINCT
    first_name
FROM employees
WHERE first_name LIKE 'Adam%'

Per trovare i nomi che finiscono con "z" prova il pattern “%z”. Puoi anche usare più “%” in un unico pattern. Ad esempio, se vuoi trovare i nomi che contengono "z", usa “%z%”.

3. Usa “_” per far combaciare un (e un solo) carattere

Come nel gioco dell’impiccato, il carattere underscore _ può rappresentare un solo carattere.

In quanti modi si può scrivere Le_n? Il pattern corrisponderebbe a qualsiasi cosa da "Lexn", "LeAn", "Le3n" o "Le-n".

SELECT DISTINCT
    first_name
FROM employees
WHERE first_name LIKE 'Le_n'

Quali sono i nomi diversi con solo tre caratteri? Possiamo scoprirlo usando tre underscore consecutivi ___ come pattern.

SELECT DISTINCT
    first_name
FROM employees
WHERE first_name LIKE '___'

4. Usa sia “%” sia “_” per far combaciare qualsiasi pattern

Ovviamente, puoi usare sia “%” sia “_” per creare pattern interessanti.

SELECT DISTINCT
    first_name
FROM employees
WHERE first_name LIKE '%ann_'

Il pattern “%ann_” corrisponde a una stringa che inizia con un qualsiasi numero di caratteri e termina con "ann" e un altro carattere.

5. Usa NOT per trovare stringhe che non corrispondono a un pattern

E se volessi trovare tutte le righe che non corrispondono a un criterio specifico? Puoi usare l’operatore NOT LIKE. Ad esempio, per trovare tutti i titoli tranne "Staff", possiamo usare la sintassi

`WHERE title NOT LIKE ‘Staff’`

Questa è esattamente equivalente alla sintassi

WHERE title != 'Staff'

SELECT DISTINCT
    title
FROM titles
WHERE title NOT LIKE 'Staff'

Ovviamente, puoi usare NOT LIKE con qualsiasi pattern descritto.

SELECT DISTINCT
    title
FROM titles 
WHERE title NOT LIKE '%engineer'

6. Usa LOWER (o UPPER) con LIKE per il pattern matching case-insensitive

Puoi usare la seguente sintassi se devi eseguire il pattern matching ma non sei sicuro che la stringa sia memorizzata in minuscolo, maiuscolo o in mixed case.

LOWER(column_name) LIKE pattern

La funzione LOWER() restituisce tutte le stringhe in minuscolo, indipendentemente dal fatto che siano memorizzate in maiuscolo, minuscolo o mixed case.

Quando usi la sintassi, assicurati di scrivere il pattern tutto in minuscolo! Altrimenti, potresti non ottenere alcuna corrispondenza.

Puoi sostituire LOWER con UPPER nella sintassi sopra. Assicurati di scrivere il pattern in maiuscolo.

`UPPER(column_name) LIKE PATTERN`

Ad esempio, per verificare se il nome di un dipendente è "Joanne", "JoAnne", "Joanna" o "JoAnna", prova una delle seguenti:

SELECT DISTINCT
    first_name
FROM employees
WHERE LOWER(first_name) LIKE 'joann_'

SELECT DISTINCT
    first_name
FROM employees
WHERE UPPER(first_name) LIKE 'JOANN_'

7. SQL LIKE con più valori usando OR/AND

Puoi anche combinare più condizioni usando la sintassi LIKE.

Ad esempio, usa la condizione OR per trovare risultati che soddisfano almeno uno tra più pattern LIKE.

SELECT DISTINCT
    first_name
FROM employees
WHERE
first_name LIKE 'Ad_l' OR
first_name LIKE 'Ad_m'

D’altra parte, per trovare una stringa che corrisponda a più condizioni LIKE, usa la parola chiave AND.

SELECT DISTINCT
    first_name
FROM employees
WHERE
first_name LIKE '%am%' AND
first_name LIKE '%me%'

La sintassi LIKE può essere applicata a più colonne purché il loro tipo sia un carattere a lunghezza variabile (varchar). Sapendo questo, possiamo trovare i nomi dei dipendenti le cui iniziali sono "Z. Z."

SELECT DISTINCT
    first_name, last_name
FROM employees
WHERE
first_name LIKE 'Z%' AND
last_name LIKE 'Z%'

8. Usa LIKE nella clausola SELECT CASE WHEN

Finora ci siamo concentrati sull’uso di LIKE come condizione per selezionare record nella clausola WHERE. Usiamo anche LIKE nella clausola SELECT. Ad esempio, possiamo scoprire quanti dipendenti di nome "Adam" ci sono nel nostro database?

SELECT
    COUNT(CASE WHEN first_name LIKE 'Adam' THEN 1 END) num_employees_adam
FROM employees

D’altra parte, quanti dipendenti hanno le iniziali "A.Z."?

SELECT
    COUNT(CASE WHEN first_name LIKE 'A%' AND last_name LIKE 'Z%' THEN 1 END) num_employees
FROM employees

Pattern comuni usati con SQL LIKE

Ecco un riepilogo dei pattern di cui abbiamo parlato, per riferimento rapido:

Esempi intermedi di SQL LIKE

La funzione LIKE è in gran parte simile tra le diverse varianti di SQL (ad es. PostgreSQL, MySQL, Redshift, ecc.). Tuttavia, alcune includono varianti aggiuntive della funzione LIKE che vale la pena menzionare.

1. L’operatore ILIKE

Disponibile in Redshift e PostgreSQL, ILIKE è la versione case-insensitive di LIKE. Di conseguenza, tutte le seguenti sono equivalenti.

SELECT
  datacamp ILIKE ‘datacamp’, -- returns TRUE
  DATACAMP ILIKE ‘datacamp’, -- returns TRUE
  Datacamp ILIKE ‘datacamp’, -- returns TRUE
  datacamp ILIKE ‘DataCamp’, -- returns TRUE

2. Uso delle parentesi quadre [] e [^] come caratteri wildcard

Gli utenti di T-SQL o SQL Server hanno a disposizione caratteri wildcard aggiuntivi per un pattern matching più complesso.

La sintassi con parentesi quadre [] corrisponde a qualsiasi carattere specifico all’interno dell’intervallo o dell’insieme. Ad esempio, i seguenti restituiranno tutti TRUE.

SELECT
  ‘Carson’ LIKE ‘[C-K]arson’, -- returns TRUE because C is in the range C-K
  ‘Karson’ LIKE ‘[C-K]arson’, -- returns TRUE because K is in range
  ‘Larson’ LIKE ‘[CKL]arson’, -- returns TRUE because L is in the set [CKL]
  ‘Parson’ LIKE ‘[C-K]arson’ -- returns FALSE because P is out of range

Il carattere circonflesso tra parentesi quadre [^] corrisponde a qualsiasi singolo carattere che non rientra nell’intervallo o nell’insieme specificato. Riesci a capire perché i seguenti risultati sono tali?

SELECT
  ‘Carson’ LIKE ‘[^C-K]arson’ -- returns FALSE 
  ‘Parson’ LIKE ‘[^C-K]arson’ -- returns TRUE

Qui, poiché "C" rientra nell’intervallo [C-K], il pattern “C” non corrisponderà a [^C-K]. Così, "Carson" non corrisponderà a [^C-K]arson, mentre "Parson" sì.

3. L’operatore RLIKE

Disponibile in MySQL, l’operatore RLIKE riconosce le espressioni regolari (RegEx) nel pattern. Le RegEx sono uno strumento potente e versatile per il pattern matching avanzato.

Non fa male avere una conoscenza di base delle RegEx, specialmente se la tua variante SQL supporta RLIKE. Puoi saperne di più sulle RegEx con il nostro corso Regular Expressions in Python.

SELECT DISTINCT
    first_name
FROM employees
WHERE first_name RLIKE 'Susann[a-e]'

4. L’operatore “~~”

In PostgreSQL, “~~” è del tutto sinonimo di LIKE. Esistono anche gli equivalenti di ILIKE, NOT LIKE e NOT ILIKE.

Risoluzione dei problemi: errori e sviste comuni con LIKE

Ecco alcuni errori comuni che potresti incontrare usando LIKE e come correggerli:

1. Nessun risultato restituito: verifica la distinzione tra maiuscole e minuscole. In alcuni dialetti SQL, LIKE distingue le maiuscole di default. Usa LOWER() o ILIKE (se supportato) per matching case-insensitive.
2. Risultati inaspettati con wildcard: assicurati che % e _ siano usati correttamente. % corrisponde a qualsiasi numero di caratteri, mentre _ corrisponde esattamente a un carattere. Un loro posizionamento errato può portare a corrispondenze inattese.
3. Problemi di performance: se la query è lenta, controlla la presenza di % in testa nei pattern (ad es., %pattern), che disabilita l’uso degli indici. Riscrivi i pattern per iniziare con una stringa specifica, se possibile. Vedi la sezione sotto per maggiori dettagli sulle performance.
4. Errori di SQL injection: se stai usando input dell’utente nella query, assicurati che sia correttamente parametrizzato per evitare vulnerabilità di SQL injection.
5. Caratteri speciali nei pattern: i simboli wildcard come % e _ nella stringa di ricerca devono essere escape se intesi come caratteri letterali. Usa ESCAPE nella query per definire un carattere di escape.

WHERE column_name LIKE '50\% OFF' ESCAPE '\'

Considerazioni sulle prestazioni quando si usa l’operatore LIKE

L’operatore LIKE è ottimo, ma può influire sulle prestazioni delle query, soprattutto se usato su dataset di grandi dimensioni. Ecco alcune considerazioni per ottimizzarne l’uso:

1. Indici: l’operatore LIKE dà il meglio quando il pattern inizia con una stringa costante, come Adam%, perché il database può usare gli indici. Tuttavia, pattern come %Adam o %Adam% richiedono una scansione completa della tabella, che può essere lenta su tabelle grandi.
2. Evita wildcard in testa: iniziare un pattern con %, come %pattern, disabilita l’uso degli indici, poiché il database deve esaminare ogni record.
3. Collation e matching case-insensitive: usare funzioni come LOWER() o UPPER() sulle colonne per ricerche case-insensitive può anch’esso impedire l’uso degli indici. In alternativa, assicurati che la collation del database sia impostata in modo appropriato per confronti case-insensitive.
4. Approcci alternativi: per dataset grandi, valuta l’uso del full-text search o di funzionalità di ricerca specifiche del database, come gli indici GIN indexes in PostgreSQL o gli indici FULLTEXT in MySQL, quando esegui matching di stringhe complesso o frequente.
5. Query selettive: limita la portata delle query usando filtri aggiuntivi, come intervalli di date o colonne numeriche, per ridurre i dati elaborati dall’operatore LIKE.

LIKE vs Full-Text Search

Il full-text search è disponibile in database come MySQL, PostgreSQL e SQL Server. Mentre LIKE funziona bene per il pattern matching semplice, il full-text search è pensato per query testuali più avanzate. Ecco come si confrontano i due approcci:

Per operazioni su stringhe più avanzate oltre al pattern matching, vedi il tutorial SQL CONTAINS.

Usa SQL LIKE con sicurezza

Padroneggiare le funzioni SQL è fondamentale per avere successo nella data science, e il comando LIKE di SQL non fa eccezione. Una buona padronanza di SQL accelererà i tuoi progressi nell’analisi: assicurati di impararlo bene!

Per ulteriori risorse su SQL, dai un’occhiata a quanto segue:

• Corso Introduzione a SQL
• Corso SQL intermedio
• Introduzione alla clausola WHERE in SQL
• Subquery in SQL: guida completa
• Introduzione alle JOIN in SQL
• Hub Impara SQL
• Cheat Sheet: basi di SQL