Guida a SQL SUBSTRING

SUBSTRING restituisce una parte di una stringa, partendo dalla posizione che indichi e prendendo tanti caratteri quanti ne chiedi. È così che SQL estrae un prefisso, un codice o il dominio di un'email da una colonna di testo. Gli esempi funzionano in PostgreSQL, MySQL, SQLite e SQL Server.

Come usare SUBSTRING

Passa a SUBSTRING una stringa, la posizione da cui partire (il primo carattere è 1) e quanti caratteri prendere. Ometti la lunghezza per prendere tutto fino alla fine:

SUBSTRING(string, start, length)

SELECT SUBSTRING('Joe Bloggs', 1, 3);
-- Output: Joe

-- No length: the rest of the string (SQL Server needs all three arguments)
SELECT SUBSTRING('Joe Bloggs', 5);
-- Output: Bloggs

In una query reale la stringa di solito è una colonna. I numeri d'ordine come ORD-2024-001 hanno l'anno nei caratteri da 5 a 8:

-- Input: orders
-- order_number
-- ------------
-- ORD-2024-001
-- ORD-2025-014
-- ORD-2026-102

SELECT order_number, SUBSTRING(order_number, 5, 4) AS year
FROM orders;

-- Output:
-- order_number | year
-- -------------+-----
-- ORD-2024-001 | 2024
-- ORD-2025-014 | 2025
-- ORD-2026-102 | 2026

Regole da conoscere

• Le posizioni partono da 1, non da 0. Un inizio negativo conta a ritroso dalla fine della stringa, ma solo in MySQL e SQLite:

SELECT SUBSTRING('Joe Bloggs', -6, 6);
-- Output: Bloggs
-- PostgreSQL and SQL Server return '' (an empty string) instead

• Andare oltre la fine non dà errore. Ottieni i caratteri esistenti, o una stringa vuota se l'inizio è oltre la fine:

SELECT SUBSTRING('Bob', 1, 10);
-- Output: Bob

SELECT SUBSTRING('Hello', 10, 5);
-- Output: '' (an empty string)

• NULL in ingresso, NULL in uscita. Avvolgi la chiamata in COALESCE quando ti serve un valore:

SELECT SUBSTRING(NULL, 1, 5);
-- Output: NULL
-- SQL Server rejects a bare NULL here, but a NULL column gives NULL

• Filtra su un prefisso con LIKE, non con SUBSTRING. WHERE SUBSTRING(product_code, 1, 3) = 'ELE' restituisce le stesse righe di WHERE product_code LIKE 'ELE%', ma solo la versione con LIKE può usare un indice su product_code.

Sintassi alternative

Troverai anche SUBSTR, un altro nome di SUBSTRING, e LEFT e RIGHT, che prendono caratteri da una delle due estremità:

-- Not in SQL Server
SELECT SUBSTR('Joe Bloggs', 1, 3);
-- Output: Joe

-- Not in SQLite: use SUBSTRING('Joe Bloggs', 1, 3) and SUBSTRING('Joe Bloggs', -6)
SELECT LEFT('Joe Bloggs', 3);
-- Output: Joe

SELECT RIGHT('Joe Bloggs', 6);
-- Output: Bloggs

-- PostgreSQL and MySQL: the standard SQL form
SELECT SUBSTRING('Joe Bloggs' FROM 1 FOR 3);
-- Output: Joe

Per partire dopo un certo carattere, trova prima la sua posizione:

SELECT INSTR('joe@example.com', '@');
-- Output: 4
-- PostgreSQL: POSITION('@' IN 'joe@example.com'); SQL Server: CHARINDEX('@', 'joe@example.com')

Quando usare SUBSTRING?

Usa SUBSTRING quando la parte di stringa che ti serve si trova in una posizione nota, o subito dopo un carattere che puoi trovare. La maggior parte dei casi rientra in uno di questi quattro.

Prendere i primi caratteri

Codici e nomi spesso hanno un significato all'inizio: la categoria in un codice prodotto, un'iniziale, un'anteprima di una descrizione lunga. SUBSTRING(column, 1, n) prende i primi n caratteri.

Estrarre campi da valori a formato fisso

Quando ogni valore ha la stessa struttura, ogni parte si trova nella stessa posizione in ogni riga: il prefisso in un numero di telefono come (303) 555-0147, l'anno in ORD-2024-001, o i campi di un record a larghezza fissa da un sistema più vecchio.

Estrarre il testo dopo un delimitatore

Domini email, estensioni di file e percorsi URL iniziano dopo un carattere la cui posizione cambia da riga a riga. Trova la sua posizione con INSTR, parti dal carattere successivo e prendi il resto della stringa.

Tenere gli ultimi caratteri

Mascherare il numero di una carta o di un conto, o leggere un suffisso, significa contare a ritroso dalla fine. Calcola l'inizio dalla lunghezza della stringa oppure, in MySQL e SQLite, usa un inizio negativo.

Esempi reali di SUBSTRING

Contare i prodotti per categoria dal codice

I codici prodotto iniziano con una categoria di tre lettere. Raggruppa per quel prefisso per contare i prodotti di ciascuna:

-- Input: products
-- product_code | name
-- -------------+-----------
-- ELE-1234     | USB-C Hub
-- FUR-0087     | Desk Chair
-- ELE-5521     | Webcam
-- OFF-2210     | Stapler

SELECT SUBSTRING(product_code, 1, 3) AS category, COUNT(*) AS products
FROM products
GROUP BY SUBSTRING(product_code, 1, 3)
ORDER BY category;

-- Output:
-- category | products
-- ---------+---------
-- ELE      | 2
-- FUR      | 1
-- OFF      | 1

Analizzare un record a larghezza fissa

Le esportazioni dai sistemi più vecchi spesso mettono i campi in posizioni fisse senza delimitatori. Qui il nome occupa 8 caratteri (riempiti con spazi, che TRIM rimuove), la data di nascita i successivi 8 e il codice della città gli ultimi 3:

-- Input: legacy_data
-- record
-- -------------------
-- JOHNDOE 19850315NYC
-- MARYLEE 19780912CHI
-- JANEROE 19900722LAX

SELECT
  TRIM(SUBSTRING(record, 1, 8)) AS name,
  SUBSTRING(record, 9, 8) AS birth_date,
  SUBSTRING(record, 17, 3) AS city_code
FROM legacy_data;

-- Output:
-- name    | birth_date | city_code
-- --------+------------+----------
-- JOHNDOE | 19850315   | NYC
-- MARYLEE | 19780912   | CHI
-- JANEROE | 19900722   | LAX

Ottenere l'estensione di ogni file

Trova il punto, poi prendi tutto ciò che segue. La clausola WHERE salta i file senza estensione:

-- Input: documents
-- filename
-- ---------------
-- q3-report.pdf
-- budget.xlsx
-- README
-- team-photo.jpeg

SELECT
  filename,
  SUBSTRING(filename, INSTR(filename, '.') + 1) AS extension
FROM documents
WHERE INSTR(filename, '.') > 0;

-- Output:
-- filename        | extension
-- ----------------+----------
-- q3-report.pdf   | pdf
-- budget.xlsx     | xlsx
-- team-photo.jpeg | jpeg

In PostgreSQL, usa POSITION('.' IN filename) al posto di INSTR. In SQL Server, usa CHARINDEX('.', filename) e aggiungi una lunghezza, come LEN(filename).

Mascherare il numero di una carta

Non tutti i numeri di carta hanno 16 cifre, quindi una posizione di inizio fissa taglia i numeri American Express. Conta invece a ritroso dalla fine:

-- Input: payments
-- customer_name | card_number
-- --------------+-----------------
-- Lena Fischer  | 4111111111111111
-- Marcus Reid   | 5500005555555559
-- Aiko Tanaka   | 378282246310005

-- Wrong: position 13 only works for 16-digit cards
SELECT customer_name, '**** ' || SUBSTRING(card_number, 13, 4) AS masked_card
FROM payments;

-- Output:
-- customer_name | masked_card
-- --------------+------------
-- Lena Fischer  | **** 1111
-- Marcus Reid   | **** 5559
-- Aiko Tanaka   | **** 005

-- Right: start 4 characters from the end
SELECT customer_name, '**** ' || SUBSTRING(card_number, LENGTH(card_number) - 3, 4) AS masked_card
FROM payments;

-- Output:
-- customer_name | masked_card
-- --------------+------------
-- Lena Fischer  | **** 1111
-- Marcus Reid   | **** 5559
-- Aiko Tanaka   | **** 0005

In MySQL e SQL Server, unisci le stringhe con CONCAT() invece di ||. SQL Server richiede anche LEN() al posto di LENGTH().

Approfondimenti su SUBSTRING

Su questo blog:

• Guida a SQL LIKE: trova testo che inizia, finisce o contiene un pattern
• Guida a SQL COALESCE: sostituisci i NULL che SUBSTRING lascia passare
• Guida a SQL GROUP BY: raggruppa e conta le righe in base a un valore estratto
• Guida al formato delle date in SQL: formatta le date ed estrai parti di data senza tagliare stringhe

Documentazione ufficiale:

• PostgreSQL: funzioni per stringhe
• MySQL: SUBSTRING()
• SQLite: substr()
• SQL Server: SUBSTRING