Cours MariaDB / Assembler, extraire et mesurer des chaînes de caractères

Pour assembler du texte dans MariaDB, utilisez CONCAT ou CONCAT_WS ; pour en extraire une partie ou remplacer un motif, utilisez SUBSTRING et REPLACE. CHAR_LENGTH compte les caractères, tandis que LENGTH compte les octets en mode habituel : cette distinction devient essentielle avec les accents, le coréen et les émojis.

Choisir une fonction selon le résultat recherché

Une fonction de chaîne reçoit une ou plusieurs valeurs et produit une nouvelle valeur. Dans un SELECT, elle transforme le résultat affiché sans modifier les données enregistrées. C’est un bon moyen de préparer une étiquette, d’examiner un numéro ou de vérifier une longueur avant de décider si une modification permanente est nécessaire.

Besoin Fonction Point à surveiller
Assembler plusieurs valeurs CONCAT(a,b,…) En mode habituel, un seul argument NULL rend le résultat NULL.
Assembler avec un séparateur CONCAT_WS(sep,a,b,…) Ignore les valeurs NULL après le séparateur, mais conserve les chaînes vides.
Extraire des caractères SUBSTRING(texte,début,longueur) La première position est 1 ; une position négative part de la fin.
Remplacer toutes les occurrences d’un motif REPLACE(texte,motif,remplacement) La recherche du motif est sensible à la casse.
Compter les caractères CHAR_LENGTH(texte) Un caractère multioctet compte pour un caractère, sans garantir le décompte des symboles visuels composés.
Compter les octets LENGTH(texte) ou OCTET_LENGTH(texte) OCTET_LENGTH conserve ce sens même en mode Oracle.

Les exemples ci-dessous supposent le mode SQL habituel, sans mode Oracle ni option transformant les chaînes vides en NULL. Vérifiez votre connexion avec SELECT @@SESSION.sql_mode; si vos résultats diffèrent. Le mode Oracle change notamment le traitement de NULL dans CONCAT, et LENGTH y compte les caractères. Ne changez pas le mode global d’un serveur pour reproduire un exercice.

Une chaîne vide contient zéro caractère ; NULL représente une valeur absente ou inconnue. Cette différence, détaillée dans le cours sur NULL et les chaînes vides, explique plusieurs résultats de ce chapitre.

Construire des libellés à partir d’une table complète

Préparez la base d’exercice shopcourse et connectez-vous avec le compte course_user disposant des droits de création, lecture, insertion et suppression sur cette base. Utilisez un fichier enregistré en UTF-8 et une connexion utf8mb4, par exemple avec l’option cliente --default-character-set=utf8mb4. Ce jeu de données utilise volontairement les noms coréens du cours d’origine : 노트 signifie carnet, 볼펜 stylo et 문구 papeterie.

La table products_19_lab est indépendante des tables des autres exercices. Si elle existe déjà, vérifiez son contenu avant d’utiliser la suppression ciblée présentée en fin de chapitre ; relancer simplement ce bloc ne réinitialise pas une table existante.

USE shopcourse;
CREATE TABLE products_19_lab (
  product_id INT NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(30) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  note VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (product_id),
  CONSTRAINT ck_p19_price CHECK (price >= 0),
  CONSTRAINT ck_p19_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_19_lab (product_id,product_name,category,price,stock,note) VALUES (1, '노트', '문구', 3500.00, 20, NULL),
  (2, '볼펜', '문구', 1500.00, 0, ''),
  (3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
  (4, '스탠드', '생활', 25000.00, 5, NULL),
  (5, '파일', '문구', 1500.00, 50, ''),
  (6, '케이블', '전자', 8000.00, 0, '호환 확인');

Les six produits permettent de comparer des notes absentes, vides ou renseignées. Exécutez les blocs dans la même base ; si vous utilisez le client MariaDB, vous pouvez enregistrer le premier dans un fichier puis lancer SOURCE chemin/creation-produits-19.sql;. Après l’insertion, SELECT COUNT(*) FROM shopcourse.products_19_lab; doit renvoyer 6.

SELECT product_id, CONCAT(product_name,' / ',category) AS label,
 CONCAT_WS(' / ',product_name,note) AS note_label
FROM products_19_lab ORDER BY product_id;

Pour le produit 1, label vaut 노트 / 문구 et note_label vaut seulement 노트 : la note NULL est ignorée par CONCAT_WS. Pour le produit 2, la note est une chaîne vide ; le résultat conserve donc le séparateur et vaut 볼펜 / , avec une espace après la barre oblique. Le produit 3 donne 머그컵 / 선물 포장 dans note_label.

Selon le client, l’espace finale peut être difficile à voir. Pour examiner précisément la valeur, utilisez HEX(CONCAT_WS(' / ',product_name,note)) dans votre sélection : pour une note vide, les derniers octets du résultat sont 202F20, soit espace, barre oblique, espace. Les règles de CONCAT_WS concernent les arguments après le séparateur : si le séparateur lui-même est NULL, le résultat est NULL.

Si votre règle d’affichage considère une note vide comme absente, NULLIF(note,'') peut fournir NULL à la place de cette chaîne vide. Décidez d’abord de cette règle : ne supprimez pas automatiquement une distinction utile aux données sources.

Extraire une portion et remplacer un motif

SELECT SUBSTRING('MariaDB',1,5), SUBSTRING('MariaDB',-2);
SELECT REPLACE('010-1234-5678','-','') AS normalized_phone;

La première requête renvoie Maria puis DB. Dans SUBSTRING('MariaDB',1,5), 1 désigne le premier caractère et 5 le nombre de caractères à prendre. Avec -2 sans longueur, la lecture commence deux caractères avant la fin et se poursuit jusqu’au dernier caractère. La fonction travaille en positions de caractères, y compris pour une chaîne multioctet ; elle ne découpe pas arbitrairement les octets d’un caractère UTF-8.

Une position située au-delà de la chaîne donne une chaîne vide. En mode habituel, la position 0 donne aussi une chaîne vide ; en mode Oracle, elle est traitée comme 1. Consultez la syntaxe de SUBSTRING pour les variantes. Une position fixe convient à un format réellement fixe ; elle devient fragile si un code métier peut changer de préfixe ou de longueur.

La seconde requête renvoie 01012345678. REPLACE retire ici toutes les occurrences du tiret en les remplaçant par une chaîne vide. Un numéro de téléphone reste du texte : le convertir en nombre ferait perdre le zéro initial et ne représenterait pas correctement les indicatifs ou les extensions. Retirer les tirets ne suffit pas, à lui seul, à valider un numéro international.

La fonction REPLACE recherche le motif en respectant la casse : REPLACE('AaA','a','x') donne AxA. Elle est distincte de l’instruction de modification de lignes portant également le nom REPLACE. Dans le SELECT ci-dessus, aucun numéro enregistré n’est modifié.

Distinguer longueur en caractères et taille en octets

SELECT CHAR_LENGTH('한글'), LENGTH('한글'),
 CHAR_LENGTH('🙂'), LENGTH('🙂');
Chaîne en utf8mb4 CHAR_LENGTH LENGTH en mode habituel
한글 2 caractères 6 octets
🙂 1 caractère 4 octets

Ces résultats supposent que le fichier et la connexion transmettent correctement les caractères en utf8mb4. Une limite de 100 caractères et une limite de 100 octets ne désignent donc pas la même capacité. Pour examiner les types de stockage, reportez-vous au cours sur les types de chaînes ; le choix du type et du jeu de caractères complète le choix de la fonction.

CHAR_LENGTH compte chaque caractère multioctet une seule fois, mais ne constitue pas un compteur universel de symboles visibles. Un « é » précomposé peut occuper un caractère, tandis qu’un « e » suivi d’un accent combinant en occupe deux. Des émojis composés peuvent également réunir plusieurs caractères. Une limite destinée à une interface utilisateur doit préciser ce qu’elle entend par « caractère ».

Pour demander explicitement une taille en octets, OCTET_LENGTH conserve cette unité, y compris en mode Oracle. Évitez de tronquer vous-même une séquence UTF-8 au milieu de ses octets ; utilisez une fonction adaptée aux caractères lorsque le besoin porte sur du texte.

Prévoir les effets de NULL, des chaînes vides et des espaces

SELECT CONCAT('노트',NULL),
 CONCAT_WS(' / ','노트',NULL,'');
SELECT CONCAT('[',TRIM('  노트  '),']');

En mode habituel, les deux colonnes de la première requête donnent respectivement NULL et 노트 / . CONCAT propage NULL ; CONCAT_WS ignore cet argument mais conserve l’argument vide suivant, donc le séparateur. La seconde requête donne [노트] : les crochets rendent visible la suppression des espaces situées aux extrémités.

TRIM sans motif explicite retire les espaces ordinaires aux deux extrémités. Il ne supprime pas les espaces intérieures, toutes les tabulations ou toutes les espaces Unicode. Une chaîne contenant uniquement des espaces ordinaires devient vide ; en mode Oracle, un résultat vide peut devenir NULL. Un argument NULL donne NULL.

Nettoyez seulement ce que votre règle métier autorise. Les espaces d’un nom ou d’un mot de passe peuvent être significatives. Le type de colonne et sa collation influencent aussi certaines comparaisons, notamment la casse, les accents ou les espaces finales ; ajouter TRIM à une expression ne remplace pas l’examen de ces règles et ne garantit pas l’unicité des données.

Vérifier la transformation et réinitialiser uniquement l’exercice

Avant d’envisager un UPDATE, affichez côte à côte l’identifiant de la ligne, la valeur originale et l’expression calculée. Par exemple, sélectionnez product_id, product_name, CHAR_LENGTH(product_name), LENGTH(product_name) depuis shopcourse.products_19_lab. Les identifiants permettent de retrouver les cas anormaux sans perdre la correspondance avec les données d’origine.

  • Un assemblage devenu NULL : examinez chaque argument, puis choisissez si une absence doit être conservée ou ignorée.
  • Un séparateur final inattendu : vérifiez si le dernier argument est vide plutôt que NULL.
  • Une extraction décalée : contrôlez la position de départ, les caractères réellement présents et l’hypothèse de format fixe.
  • Une longueur inattendue ou un texte illisible : comparez caractères et octets, puis contrôlez l’encodage du fichier et de la connexion.

Un SELECT de diagnostic laisse les données intactes. Pour enregistrer réellement un changement, appliquez ensuite les contrôles de cible et de transaction du cours sur UPDATE et DELETE, après avoir validé la transformation sur les cas absents, vides et multioctets.

DROP TABLE shopcourse.products_19_lab;

Cette dernière commande supprime définitivement la table d’exercice et ses six lignes. Vérifiez son nom complet avant de l’exécuter : elle ne supprime ni shopcourse.products ni les tables des autres chapitres. DROP TABLE est une opération de définition de structure ; ne comptez pas sur ROLLBACK pour la restaurer. Pour recommencer, exécutez à nouveau le premier bloc de création et d’insertion, puis les SELECT de ce chapitre.