Guide de SUBSTRING en SQL

SUBSTRING renvoie une partie d’une chaîne, à partir de la position que tu lui donnes et sur autant de caractères que tu demandes. C’est ainsi que SQL extrait un préfixe, un code ou le domaine d’un e-mail d’une colonne texte. Les exemples fonctionnent avec PostgreSQL, MySQL, SQLite et SQL Server.

Comment utiliser SUBSTRING

Donne à SUBSTRING une chaîne, la position de départ (le premier caractère est 1) et le nombre de caractères à prendre. Omets la longueur pour tout prendre jusqu’à la fin :

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

Dans une vraie requête, la chaîne est généralement une colonne. Les numéros de commande comme ORD-2024-001 contiennent l’année dans les caractères 5 à 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

Les règles à connaître

• Les positions commencent à 1, pas à 0. Un départ négatif compte à rebours depuis la fin de la chaîne, mais uniquement dans MySQL et SQLite :

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

• Dépasser la fin n’est pas une erreur. Tu obtiens les caractères qui existent, ou une chaîne vide si le départ est au-delà de la fin :

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

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

• NULL en entrée, NULL en sortie. Enveloppe l’appel dans COALESCE si tu as besoin d’une valeur à la place :

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

• Filtre sur un préfixe avec LIKE, pas avec SUBSTRING. WHERE SUBSTRING(product_code, 1, 3) = 'ELE' renvoie les mêmes lignes que WHERE product_code LIKE 'ELE%', mais seule la version LIKE peut utiliser un index sur product_code.

Syntaxe alternative

Tu croiseras aussi SUBSTR, un autre nom de SUBSTRING, ainsi que LEFT et RIGHT, qui prennent des caractères à l’une ou l’autre extrémité :

-- 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

Pour commencer après un caractère précis, trouve d’abord sa position :

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

Quand utiliser SUBSTRING ?

Utilise SUBSTRING quand la partie de la chaîne dont tu as besoin se trouve à une position connue, ou juste après un caractère que tu peux repérer. La plupart des usages entrent dans l’un de ces quatre cas.

Prendre les premiers caractères

Les codes et les noms ont souvent du sens au début : la catégorie dans un code produit, une initiale, un court aperçu d’une longue description. SUBSTRING(column, 1, n) prend les n premiers caractères.

Extraire des champs de valeurs au format fixe

Quand toutes les valeurs ont la même structure, chaque partie se trouve à la même position sur chaque ligne : l’indicatif régional dans un numéro comme (303) 555-0147, l’année dans ORD-2024-001, ou les champs d’un enregistrement à largeur fixe issu d’un ancien système.

Extraire le texte après un délimiteur

Les domaines d’e-mail, les extensions de fichiers et les chemins d’URL commencent après un caractère dont la position change d’une ligne à l’autre. Trouve sa position avec INSTR, commence un caractère plus loin et prends le reste de la chaîne.

Garder les derniers caractères

Masquer un numéro de carte ou de compte, ou lire un suffixe, implique de compter depuis la fin. Calcule le départ à partir de la longueur de la chaîne ou, dans MySQL et SQLite, utilise un départ négatif.

Exemples concrets de SUBSTRING

Compter les produits par catégorie dans leur code

Les codes produits commencent par une catégorie de trois lettres. Regroupe par ce préfixe pour compter les produits de chaque catégorie :

-- 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

Analyser un enregistrement à largeur fixe

Les exports d’anciens systèmes placent souvent les champs à des positions fixes, sans délimiteur. Ici, le nom occupe 8 caractères (complétés par des espaces, que TRIM supprime), la date de naissance les 8 suivants et le code de ville les 3 derniers :

-- 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

Récupérer l’extension de chaque fichier

Trouve le point, puis prends tout ce qui suit. La clause WHERE ignore les fichiers qui n’en ont pas :

-- 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

Dans PostgreSQL, utilise POSITION('.' IN filename) au lieu de INSTR. Dans SQL Server, utilise CHARINDEX('.', filename) et ajoute une longueur, comme LEN(filename).

Masquer un numéro de carte

Les numéros de carte ne font pas tous 16 chiffres, donc une position de départ fixe tronque les numéros American Express. Compte plutôt depuis la fin :

-- 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

Dans MySQL et SQL Server, concatène les chaînes avec CONCAT() au lieu de ||. SQL Server a aussi besoin de LEN() au lieu de LENGTH().

Pour aller plus loin avec SUBSTRING

Sur ce blog :

• Guide de LIKE en SQL : trouver du texte qui commence par, finit par ou contient un motif
• Guide de COALESCE en SQL : remplacer les NULL que SUBSTRING laisse passer
• Guide de GROUP BY en SQL : regrouper et compter des lignes selon une valeur extraite
• Guide des formats de date en SQL : formater des dates et en extraire des parties sans découper de chaînes

Documentation officielle :

• PostgreSQL : fonctions de chaînes
• MySQL : SUBSTRING()
• SQLite : substr()
• SQL Server : SUBSTRING