Fonctions ② — Fonctions de chaîne (LENGTH / TRIM / REPLACE / UPPER / SUBSTR)

Données utilisées dans cet article — la table staff

Le deuxième article sur les fonctions se concentre sur les fonctions de chaîne. Nous allons parcourir les fonctions auxquelles tu auras recours dès que tu auras besoin de nettoyer ou transformer du texte extrait d'une table en production : longueur, suppression d'espaces, substitution, conversion de casse, et extraction de sous-chaîne.

Le jeu de données est la même table staff que la dernière fois (10 lignes : name / city / salary, etc.). En utilisant les noms de la colonne name comme vedettes, quatre exercices couvrent le calcul de longueur, la substitution de nom de famille, la mise en majuscules, et l'extraction d'initiales.

Avant de te lancer dans les exercices, vérifions les définitions de colonnes et les données d'exemple de la table staff.

① Exécute PRAGMA table_info(staff); pour voir les noms de colonnes, types et clé primaire.

② Exécute SELECT * FROM staff LIMIT 5; pour prévisualiser les 5 premières lignes.

Longueur de chaîne — `LENGTH` et `CHAR_LENGTH`

La fonction pour mesurer la longueur d'une chaîne se comporte différemment entre la console de ce cours (SQLite) et MySQL, donc fais attention :

- Console de ce cours (SQLite) : LENGTH(s) renvoie le nombre de caractères ('café' → 4)

- MySQL : LENGTH(s) renvoie le nombre d'octets ('café' → 5, car é occupe 2 octets en UTF-8). Pour obtenir le nombre de caractères, utilise CHAR_LENGTH(s)

Cet article utilise LENGTH() pour les exercices, mais il est important de connaître la différence — toute chaîne avec accents (français, espagnol, etc.) tombe sur ce piège.

-- 1) Compter les caractères (dans la console de ce cours, LENGTH renvoie le nombre de caractères)
SELECT LENGTH('Alice');         -- 5
SELECT LENGTH('café');          -- 4 (ici), 5 (LENGTH de MySQL = nombre d'octets)

-- 2) Trier par longueur de colonne
SELECT name, LENGTH(name) AS name_len FROM staff
ORDER BY name_len DESC;

-- Référence : en MySQL, le nombre de caractères est CHAR_LENGTH(s)
--   SELECT CHAR_LENGTH('café') FROM dual;  -- 4

Imagine une liste d'employés qui met en avant le « TOP 3 des employés par longueur de nom complet ». (Si tu l'exécutes correctement, une explication apparaîtra.)

① Depuis la table staff, extrais name et `LENGTH(name)` avec l'alias `name_len` — 2 colonnes au total.

② Trie par name_len décroissant. Pour les égalités, départage par name croissant (alphabétique) — tri multi-niveaux.

③ Limite aux 3 premières lignes.

④ Vérifie que le résultat est 3 lignes : Iris Watanabe 13 / Alice Tanaka 12 / Carol Tanaka 12.

Suppression d'espaces et substitution — `TRIM` et `REPLACE`

Dans les tables du monde réel, tu rencontreras des espaces non voulus — « quelqu'un a fait un copier-coller avec des espaces au début / à la fin », « un système avant migration a laissé des espaces pleine chasse dans les données », etc. `TRIM(s)` retire les espaces des deux côtés d'une chaîne ; LTRIM(s) retire seulement à gauche, et RTRIM(s) seulement à droite.

`REPLACE(s, recherche, remplacement)` remplace toutes les occurrences de recherche dans s par remplacement. C'est la fonction de prédilection pour la normalisation et les migrations — « abréger le nom de famille fréquent Tanaka en T. pour un affichage compact », « changer le domaine d'une adresse e-mail pour un nouveau », etc. Combine-la avec UPDATE et tu peux réécrire des valeurs directement dans la table elle-même.

-- 1) Suppression d'espaces : TRIM / LTRIM / RTRIM
SELECT TRIM('  Hello  ');     -- 'Hello'
SELECT LTRIM('  Hello  ');    -- 'Hello  '
SELECT RTRIM('  Hello  ');    -- '  Hello'

-- TRIM sur une colonne : détecte les lignes avec des espaces via différence de longueur
SELECT name FROM staff
WHERE LENGTH(name) <> LENGTH(TRIM(name));

-- 2) Substitution : REPLACE
SELECT REPLACE('I LIKE APPLE', 'APPLE', 'BANANA');
-- 'I LIKE BANANA'

-- REPLACE sur une colonne — abréger 'Tanaka' en 'T.'
SELECT name, REPLACE(name, 'Tanaka', 'T.') AS short_name
FROM staff;

Appliquer TRIM — combiner avec UPDATE pour corriger un texte incohérent

TRIM n'est pas seulement pour la lecture — couplée avec UPDATE, elle peut réécrire des valeurs directement dans la table. Par exemple, pour normaliser en masse une colonne name avec des espaces parasites au début / à la fin, écris quelque chose comme UPDATE staff SET name = TRIM(name) WHERE LENGTH(name) <> LENGTH(TRIM(name)); — le motif sûr est filtrer d'abord avec WHERE, puis UPDATE. Si tu retires le WHERE, chaque ligne reçoit TRIM (presque sans dommage mais avec des UPDATE inutiles). REPLACE fonctionne pareil : UPDATE table SET colonne = REPLACE(colonne, 'A', 'B') est une technique de base pour corriger en masse un texte incohérent.

Imagine une requête : « sur un tableau de bord compact, abréger le nom de famille fréquent Tanaka en T. ».

① Depuis la table staff, extrais name et `REPLACE(name, 'Tanaka', 'T.')` avec l'alias `short_name` — 2 colonnes au total.

② Vérifie que le résultat a 10 lignes. Les 4 noms contenant Tanaka (Alice / Carol / Frank / Jack) devraient avoir short_name comme 'Alice T.' / 'Carol T.'.

Conversion de casse et sous-chaîne — `UPPER` / `LOWER` / `SUBSTR`

`UPPER(s)` convertit une chaîne tout en majuscules, et `LOWER(s)` tout en minuscules. Utilise-les quand tu veux des conditions de recherche insensibles à la casse (WHERE UPPER(email) = 'X@Y.COM') ou un formatage d'affichage cohérent. Elles n'ont aucun effet sur les caractères sans distinction de casse (comme le japonais).

`SUBSTR(s, début, longueur)` extrait `longueur` caractères à partir de la position `début` (tu peux aussi l'écrire SUBSTRING). Les positions SQL sont basées sur 1, donc SUBSTR('Alice Tanaka', 1, 5) renvoie 'Alice'. Omets le troisième argument et il renvoie tout jusqu'à la fin ; passe un début négatif comme SUBSTR(s, -3) et il compte depuis la fin.

-- 1) UPPER / LOWER
SELECT UPPER('apple'), LOWER('APPLE');
-- 'APPLE', 'apple'

-- Sur une colonne : extraire les noms en majuscules et minuscules
SELECT name, UPPER(name) AS upper_name, LOWER(name) AS lower_name
FROM staff;

-- 2) SUBSTR : positions basées sur 1
SELECT SUBSTR('Alice Tanaka', 1, 5);   -- 'Alice'
SELECT SUBSTR('Alice Tanaka', 7, 6);   -- 'Tanaka'
SELECT SUBSTR('Alice Tanaka', 7);      -- 'Tanaka'  -- omettre longueur → jusqu'à la fin
SELECT SUBSTR('Alice Tanaka', -6);     -- 'Tanaka'  -- négatif → depuis la fin

-- Premier caractère de chaque nom
SELECT name, SUBSTR(name, 1, 1) AS initial FROM staff;

-- Référence : REVERSE est une fonction MySQL / Oracle — pas dans la console de ce cours
--   SELECT REVERSE('Alice Tanaka');  -- 'akanaT ecilA' (fonctionne en MySQL)

Imagine une requête : « grouper les fichiers d'employés par initiale, donc extraire chaque initiale et trier ».

① Depuis la table staff, extrais name, `UPPER(name)` comme `upper_name`, et `SUBSTR(name, 1, 1)` comme `initial` — 3 colonnes au total.

② Trie par initial croissant (alphabétique), avec name croissant comme départageur.

③ Vérifie que le résultat a 10 lignes, commençant par Alice Tanaka / ALICE TANAKA / A et finissant par Jack Tanaka / JACK TANAKA / J.

Trouver la position d'une sous-chaîne — `INSTR`

`INSTR(cible, recherche)` renvoie la position (index basé sur 1) où `recherche` apparaît pour la première fois dans cible. Les positions commencent à 1, et si la chaîne recherchée n'est pas trouvée, elle renvoie 0. Par exemple, INSTR('Alice Tanaka', ' ') renvoie 6 parce que l'espace est à la 6e position.

Combine-la avec SUBSTR et tu peux découper une chaîne en se basant sur un séparateur plutôt qu'une position fixe. C'est le combo de prédilection pour transformer des chaînes de longueur variable — « séparer une adresse e-mail autour du @ », « extraire juste le nom de famille d'un nom complet », etc.

-- 1) INSTR seul — vérifie la position de l'espace
SELECT name, INSTR(name, ' ') AS space_pos FROM staff;
-- 'Alice Tanaka' → 6, 'Bob Suzuki' → 4, ...

-- 2) Combiné avec SUBSTR pour extraire le nom de famille
SELECT name,
       SUBSTR(name, INSTR(name, ' ') + 1) AS last_name
FROM staff;

-- 3) Séparation autour de '@' (exemple d'e-mail)
SELECT INSTR('alice@example.com', '@'); -- 6

Imagine une requête : « extraire juste la partie nom de famille de chaque nom complet et montrer le top 5 par ordre alphabétique du nom de famille ». Dans staff, name a la forme « Alice Tanaka » — « prénom espace nom », séparé par une espace à mi-chasse. Comme les noms varient en longueur, au lieu de couper à une position fixe, utilise `INSTR` pour trouver la position de l'espace puis découpe après.

① Depuis la table staff, extrais name et `SUBSTR(name, INSTR(name, ' ') + 1)` avec l'alias `last_name` — 2 colonnes au total.

② Tri multi-niveaux : `last_name` croissant, avec `name` croissant comme départageur. Limite aux 5 premières lignes.

③ Vérifie que le résultat est 5 lignes (David Sato / Emi Sato / Henry Sato / Bob Suzuki / Grace Suzuki).