Funzioni stringa in SQLite: SUBSTR, REPLACE, INSTR e altre

Le stringhe sono il cuore delle query reali

I numeri sono facili. Con le stringhe le query si complicano: nomi con spazi di troppo, email con maiuscole e minuscole mescolate, ID incollati con trattini, campi di testo libero che corrispondono quasi, ma non del tutto. SQLite offre un insieme piccolo e mirato di funzioni stringa che gestisce quasi tutto questo senza bisogno di codice applicativo.

Questa pagina passa in rassegna quelle che userai per prime: concatenare, estrarre porzioni, cercare, sostituire, ripulire e formattare.

Per concatenare si usa ||, non CONCAT

SQLite non ha una funzione CONCAT. Le stringhe si uniscono con l'operatore ||:

SELECT 'Hello, ' || 'world!' AS greeting;

SELECT 'User #' || 42 || ' joined' AS message;

I numeri e gli altri tipi vengono convertiti in testo automaticamente. Il tranello: se uno qualsiasi degli operandi è NULL, l'intera espressione diventa NULL. È il comportamento standard di SQL, ma coglie di sorpresa parecchie persone:

SELECT 'Hello, ' || NULL || '!' AS greeting;

SELECT 'Hello, ' || COALESCE(NULL, 'friend') || '!' AS safe_greeting;

Avvolgi le colonne che possono essere nulle in COALESCE(col, '') o COALESCE(col, 'default') quando non vuoi che un valore mancante cancelli l'intera stringa.

Length, Upper, Lower

Le tre che userai continuamente:

SELECT
    LENGTH('résumé')        AS char_count,
    UPPER('hello world')    AS upper_case,
    LOWER('HELLO WORLD')    AS lower_case;

Per il testo LENGTH restituisce il numero di caratteri, non di byte. Se ti servono davvero i byte (raro, ma utile per analizzare lo spazio occupato), usa OCTET_LENGTH. Per impostazione predefinita UPPER e LOWER trasformano solo le lettere ASCII: i caratteri accentati restano invariati, a meno che tu non abbia caricato l'estensione ICU.

SUBSTR: estrarre porzioni di stringa

SUBSTR(text, start, length) estrae un pezzo di una stringa. Gli indici partono da 1: 1 è il primo carattere, non 0:

SELECT
    SUBSTR('codecademy', 1, 4)  AS first_four,
    SUBSTR('codecademy', 5)     AS rest,
    SUBSTR('codecademy', -3)    AS last_three;

Alcune cose da ricordare:

• Il terzo argomento è facoltativo. Senza, ottieni tutto da start fino alla fine.
• Uno start negativo conta dalla fine della stringa.
• Se start va oltre la fine, ottieni una stringa vuota, non un errore.

SUBSTRING è accettato come sinonimo, nel caso le tue abitudini vengano da un altro database.

INSTR: trovare una sottostringa

INSTR(haystack, needle) restituisce la posizione (a partire da 1) della prima occorrenza di needle in haystack, oppure 0 se non la trova:

SELECT
    INSTR('user@example.com', '@')        AS at_position,
    INSTR('user@example.com', 'xyz')      AS not_found,
    SUBSTR('user@example.com',
           1,
           INSTR('user@example.com', '@') - 1) AS local_part;

L'ultima espressione è l'idioma SQLite per "tutto quello che viene prima della @": trovi il delimitatore con INSTR, poi tagli con SUBSTR. Scriverai spesso questa combinazione. Nota che, siccome INSTR restituisce 0 quando non trova nulla, conviene controllare prima di tagliare: passare 0 a SUBSTR dà in silenzio risultati strani.

REPLACE: sostituire una sottostringa con un'altra

REPLACE(text, old, new) sostituisce ogni occorrenza di old con new:

SELECT
    REPLACE('555-123-4567', '-', '')                AS digits_only,
    REPLACE('Hello, world. Hello, again.',
            'Hello',
            'Hi')                                   AS shortened,
    REPLACE('  spaced  out  ', ' ', '')             AS no_spaces;

Distingue maiuscole e minuscole e non accetta espressioni regolari: solo una sottostringa letterale. Per trasformazioni più complesse puoi concatenare più chiamate a REPLACE, ma oltre due o tre annidate è ora di fare il lavoro nell'applicazione.

TRIM, LTRIM, RTRIM

I dati inseriti dagli utenti tendono ad arrivare con spazi alle estremità. TRIM li elimina:

SELECT
    TRIM('   hello   ')              AS both_sides,
    LTRIM('   hello   ')             AS left_only,
    RTRIM('   hello   ')             AS right_only,
    TRIM('---hello---', '-')         AS custom_chars;

Per impostazione predefinita rimuovono gli spazi. Passa un secondo argomento per indicare quali caratteri togliere: ogni carattere del secondo argomento viene trattato come membro di un "insieme da rimuovere", non come sottostringa letterale. Quindi TRIM('xxxhelloxx', 'x') dà 'hello'.

printf: formattare numeri e stringhe

Quando ti serve una stringa formattata (decimali fissi, numeri con riempimento, output esadecimale), ci pensa printf (che si scrive anche format):

SELECT
    printf('%.2f',         3.14159)            AS two_decimals,
    printf('%05d',         42)                 AS padded,
    printf('$%,.2f',       1234567.89)         AS money,
    printf('%s is %d',     'Ada',  30)         AS sentence;

Gli specificatori di formato seguono le convenzioni del C: %d, %s, %f, %x, riempimento con 0 o spazi e così via. È molto più pulito che costruire stringhe con || e una pila di CAST.

LIKE vs GLOB: confronto con pattern

Due operatori, due mondi diversi.

LIKE usa i classici caratteri jolly di SQL (% per qualsiasi sequenza di caratteri, _ per un singolo carattere) e non distingue maiuscole e minuscole per l'ASCII:

SELECT 'Alice'  LIKE 'a%'      AS alice_match;
SELECT 'README' LIKE '_____'   AS five_chars;
SELECT 'foo.txt' LIKE '%.txt'  AS is_text_file;

GLOB usa i caratteri jolly della shell Unix (* per qualsiasi sequenza, ? per un singolo carattere, [abc] per le classi di caratteri) e distingue maiuscole e minuscole:

SELECT 'Alice'  GLOB 'A*'         AS alice_match;
SELECT 'Alice'  GLOB 'a*'         AS lowercase_match;
SELECT 'log7'   GLOB 'log[0-9]'   AS digit_class;

La regola per scegliere: LIKE per i confronti "inizia con", "contiene", "finisce con" pensati per le persone. GLOB quando conta la distinzione tra maiuscole e minuscole o ti servono le classi di caratteri. Entrambi possono usare gli indici, ma solo quando il pattern è ancorato all'inizio ('foo%', non '%foo'): i caratteri jolly iniziali obbligano a una scansione completa.

Dividere le stringhe: non esiste SPLIT

SQLite non include una funzione SPLIT_STRING. I due rimedi pratici:

-- Estrarre una sola parte con INSTR + SUBSTR
WITH addresses AS (
    SELECT 'rosa@example.com'   AS email UNION ALL
    SELECT 'boris@coddy.tech'   UNION ALL
    SELECT 'ada@lovelace.org'
)
SELECT
    SUBSTR(email, 1, INSTR(email, '@') - 1)  AS local_part,
    SUBSTR(email, INSTR(email, '@') + 1)     AS domain
FROM addresses;

Per dividere su un delimitatore ottenendo più righe, la strada più pulita è json_each su un array JSON, oppure una CTE ricorsiva. Li vedremo entrambi nei capitoli successivi: per ora ti basta sapere che "dammi ogni parola" in SQLite non si fa in una riga.

Un esempio completo: ripulire i nomi

Mettiamo tutto insieme. Immagina una tabella users con nomi visualizzati disordinati: spazi in più, maiuscole e minuscole mescolate, titoli facoltativi come "Dr. " o "Mr. " da togliere:

WITH users(id, display_name) AS (
    VALUES
      (1, '  Dr. Ada Lovelace  '),
      (2, 'mr. Boris Karloff'),
      (3, 'Rosa  Parks'),
      (4, '   alan TURING')
)
SELECT
    id,
    display_name AS original,
    TRIM(
      REPLACE(
        REPLACE(LOWER(TRIM(display_name)), 'dr. ', ''),
        'mr. ', ''
      )
    ) AS cleaned
FROM users;

L'espressione si legge dall'interno verso l'esterno: togli gli spazi esterni, converti in minuscolo, elimina i titoli, poi rifai il trim nel caso la rimozione del titolo abbia lasciato uno spazio iniziale. Ogni passaggio è una singola funzione: la complessità nasce dall'impilarle. Quando la pila supera i tre o quattro livelli, è un segnale per usare una colonna generata (capitolo: Funzionalità avanzate) oppure fare la pulizia durante l'importazione dei dati.

Cosa portarti a casa

• || per concatenare; NULL contamina il risultato, quindi usa COALESCE.
• SUBSTR e INSTR insieme coprono quasi tutti i casi di "trova e taglia".
• REPLACE sostituisce ogni occorrenza di una sottostringa letterale.
• TRIM e le sue varianti accettano un insieme di caratteri personalizzato, non solo gli spazi.
• printf è lo strumento giusto per l'output formattato.
• LIKE per i caratteri jolly SQL senza distinzione tra maiuscole e minuscole, GLOB per i pattern in stile shell che la fanno.

Prossimo passo: funzioni numeriche

Sistemate le stringhe, la tappa successiva ovvia sono i numeri: arrotondamenti, valori assoluti, stranezze della divisione e le funzioni matematiche aggiunte da SQLite nelle versioni recenti. È la prossima pagina.