Fonctions de chaînes SQLite : SUBSTR, REPLACE, INSTR

Les chaînes, c'est là que se joue la vraie vie des requêtes

Les nombres, c'est facile. Les chaînes, c'est là que ça se complique : des noms truffés d'espaces parasites, des e-mails en casse mélangée, des identifiants collés avec des tirets, du texte libre qui presque correspond, mais pas tout à fait. SQLite propose un petit jeu de fonctions chaînes ciblées qui suffisent pour la plupart de ces cas, sans avoir à passer par du code applicatif.

Cette page parcourt celles que vous dégainerez en premier : concaténation, découpe, recherche, remplacement, nettoyage et formatage.

La concaténation se fait avec ||, pas avec CONCAT

SQLite n'a pas de fonction CONCAT. Pour coller deux chaînes, on utilise l'opérateur || :

SELECT 'Bonjour, ' || 'le monde !' AS greeting;

SELECT 'Utilisateur n°' || 42 || ' a rejoint' AS message;

Les nombres et autres types sont convertis en texte automatiquement. Le piège : si un seul opérande vaut NULL, toute l'expression devient NULL. C'est le comportement SQL standard, mais ça en surprend plus d'un :

SELECT 'Bonjour, ' || NULL || ' !' AS salutation;

SELECT 'Bonjour, ' || COALESCE(NULL, 'ami') || ' !' AS salutation_sure;

Encapsulez les colonnes nullables avec COALESCE(col, '') ou COALESCE(col, 'default') quand vous ne voulez pas qu'une valeur manquante fasse exploser toute votre chaîne.

Longueur, majuscules, minuscules

Le trio que vous allez utiliser en permanence :

SELECT
    LENGTH('résumé')        AS char_count,
    UPPER('bonjour le monde')    AS upper_case,
    LOWER('BONJOUR LE MONDE')    AS lower_case;

LENGTH renvoie le nombre de caractères pour du texte, pas d'octets. Si vous voulez vraiment compter les octets (cas rare, mais pratique pour analyser le stockage), utilise plutôt OCTET_LENGTH. Quant à UPPER et LOWER, ils ne transforment que les lettres ASCII par défaut : les caractères accentués passent à travers sans broncher, sauf si vous avez chargé l'extension ICU.

SUBSTR : extraire une sous-chaîne en sqlite

SUBSTR(texte, début, longueur) permet d'extraire un morceau d'une chaîne. Attention, les index commencent à 1 — 1 correspond au premier caractère, pas 0 :

SELECT
    SUBSTR('codecademy', 1, 4)  AS quatre_premiers,
    SUBSTR('codecademy', 5)     AS reste,
    SUBSTR('codecademy', -3)    AS trois_derniers;

Quelques points à garder en tête :

• Le troisième argument est facultatif. Sans lui, vous récupérez tout depuis start jusqu'à la fin de la chaîne.
• Un start négatif compte à partir de la fin de la chaîne.
• Si start dépasse la fin, vous obtenez une chaîne vide, pas une erreur.

SUBSTRING est aussi accepté comme synonyme, pratique si vos doigts ont l'habitude d'une autre base de données.

INSTR : trouver une sous-chaîne

INSTR(haystack, needle) renvoie la position (indexée à partir de 1) de la première occurrence de needle dans haystack, ou 0 si la sous-chaîne est absente :

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;

Cette dernière expression, c'est l'idiome SQLite pour récupérer « tout ce qui se trouve avant le @ » : on localise le délimiteur avec INSTR, puis on découpe avec SUBSTR. Vous allez écrire cette combinaison très souvent. Attention quand même : INSTR renvoie 0 s'il ne trouve rien, donc pensez à vérifier avant de découper — passer 0 à SUBSTR donne des résultats bizarres sans broncher.

REPLACE : remplacer une sous-chaîne par une autre

REPLACE(text, old, new) remplace toutes les occurrences de old par new :

SELECT
    REPLACE('555-123-4567', '-', '')                AS chiffres_uniquement,
    REPLACE('Bonjour, monde. Bonjour, encore.',
            'Bonjour',
            'Salut')                                AS raccourci,
    REPLACE('  espace  ment  ', ' ', '')            AS sans_espaces;

C'est sensible à la casse et ça n'accepte pas de regex — uniquement une sous-chaîne littérale. Pour des transformations plus complexes, on peut enchaîner les appels à REPLACE, mais au-delà de deux ou trois imbrications, mieux vaut traiter ça côté application.

TRIM, LTRIM et RTRIM

Les données saisies par les utilisateurs arrivent souvent avec des espaces parasites en début ou en fin. TRIM se charge de les nettoyer :

SELECT
    TRIM('   bonjour   ')              AS les_deux_cotes,
    LTRIM('   bonjour   ')             AS gauche_seulement,
    RTRIM('   bonjour   ')             AS droite_seulement,
    TRIM('---bonjour---', '-')         AS caracteres_personnalises;

Par défaut, ces fonctions suppriment les espaces. En passant un second argument, vous précisez les caractères à retirer — chaque caractère de cet argument est considéré comme un membre d'un « ensemble à supprimer », et non comme une sous-chaîne littérale. Ainsi, TRIM('xxxhelloxx', 'x') renvoie 'bonjour'.

printf : formater nombres et chaînes

Quand il vous faut une chaîne formatée — nombre de décimales fixe, chiffres complétés par des zéros, sortie en hexadécimal — printf (aussi disponible sous le nom format) fait le travail :

SELECT
    printf('%.2f',         3.14159)            AS deux_decimales,
    printf('%05d',         42)                 AS rembourre,
    printf('$%,.2f',       1234567.89)         AS argent,
    printf('%s a %d ans',  'Ada',  30)         AS phrase;

Les spécificateurs de format suivent les conventions du C : %d, %s, %f, %x, le remplissage avec des 0 ou des espaces, etc. C'est nettement plus propre que de bricoler des chaînes à coups de || et d'une tripotée de CAST.

sqlite LIKE vs GLOB : la recherche par motif

Deux opérateurs, deux logiques bien distinctes.

LIKE s'appuie sur les jokers classiques du SQL — % pour une suite quelconque de caractères, _ pour un caractère unique — et ignore la casse en ASCII :

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

GLOB s'appuie sur les jokers du shell Unix — * pour une séquence quelconque, ? pour un seul caractère, [abc] pour une classe de caractères — et il est sensible à la casse :

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

Comment choisir entre les deux : LIKE pour les recherches « à la humaine » du genre « commence par », « contient » ou « se termine par ». GLOB quand la casse compte ou que vous avez besoin de classes de caractères. Les deux peuvent exploiter un index, mais seulement si le motif est ancré au début ('foo%', pas '%foo') — un joker en tête force un parcours complet de la table.

Découper une chaîne en SQLite : pas de fonction SPLIT

SQLite ne fournit aucune fonction SPLIT_STRING native. Voici les deux contournements qui marchent en pratique :

-- Extraire une partie en utilisant 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;

Pour découper sur un séparateur et obtenir plusieurs lignes, le plus propre c'est json_each sur un tableau JSON, ou bien une CTE récursive. On reviendra sur les deux dans les chapitres suivants — pour l'instant, retenez juste que « donne-moi chaque mot » n'est pas une affaire d'une ligne en SQLite.

Cas pratique : nettoyer des noms

On met tout bout à bout. Imaginez une table users avec des noms d'affichage un peu sales — espaces en trop, casse incohérente, titres optionnels du genre "Dr. " ou "Mr. " que vous voulez virer :

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'expression se lit de l'intérieur vers l'extérieur : on retire les espaces aux extrémités, on passe en minuscules, on supprime les titres, puis on retrim au cas où la suppression du titre aurait laissé un espace en tête. Chaque étape se résume à une seule fonction — toute la complexité vient de leur empilement. Quand la pile dépasse trois ou quatre niveaux, c'est le signal qu'il vaut mieux passer par une colonne générée (chapitre : Fonctionnalités avancées) ou faire le nettoyage au moment de l'import des données.

Ce qu'il faut retenir

• || pour la concaténation sqlite ; attention, NULL contamine tout le résultat, d'où l'intérêt de COALESCE.
• SUBSTR et INSTR combinés couvrent la majorité des besoins du type « trouver puis découper » pour extraire une sous-chaîne sqlite.
• REPLACE remplace toutes les occurrences d'une sous-chaîne littérale.
• TRIM et ses variantes acceptent un jeu de caractères personnalisé, pas uniquement les espaces.
• printf est l'outil idéal pour le formatage de sortie.
• LIKE pour des motifs SQL insensibles à la casse, GLOB pour des motifs façon shell sensibles à la casse.

Suite : les fonctions numériques

Les chaînes étant réglées, la suite logique passe par les nombres : arrondis, valeurs absolues, subtilités de la division entière, et les fonctions mathématiques ajoutées dans les versions récentes de SQLite. C'est l'objet de la page suivante.