Comment utiliser la fonction SQL REPLACE()

En bref

• REPLACE(string, old_substring, new_substring) remplace toutes les occurrences d'une sous-chaîne en un seul appel

• Enchaînez les appels pour remplacer plusieurs sous-chaînes : REPLACE(REPLACE(col, 'a', 'b'), 'c', 'd')

• Passez une chaîne vide pour supprimer des caractères : REPLACE(phone, '-', '')

• Si l'un des arguments est NULL, la fonction renvoie NULL pour cette ligne

• La sensibilité à la casse dépend de votre base de données et de la collation : SQL Server et MySQL sont par défaut insensibles à la casse ; PostgreSQL est sensible à la casse par défaut

Comprendre la fonction SQL REPLACE()

La fonction REPLACE() en SQL recherche la sous-chaîne ou chaîne indiquée dans une colonne et la remplace par une autre chaîne fournie. L'exemple ci-dessous illustre l'utilisation de base de REPLACE(). Les arguments de la fonction sont :

• string : La chaîne d'origine dans laquelle effectuer le remplacement.

• old_substring : La sous-chaîne à remplacer.

• new_substring : La sous-chaîne de remplacement.

REPLACE(string, old_substring, new_substring)

Lorsque vous utilisez REPLACE() pour modifier les chaînes d'une table, la requête adopte la syntaxe suivante, où :

• column_name : La colonne dans laquelle rechercher les occurrences de la sous-chaîne.

• old_substring : La sous-chaîne à remplacer.

• new_substring : La sous-chaîne de remplacement.

-- Sélectionne la colonne description de la table Products
-- et remplace les occurrences de 'old' par 'new'
SELECT 
    REPLACE(column_name, 'old_substring', 'new_substring') AS updated_column
FROM 
    Products;

Exemple d'utilisation de REPLACE()

La fonction SQL REPLACE() modifie ou substitue toutes les occurrences d'une chaîne par une sous-chaîne ou une chaîne donnée. Dans l'exemple ci-dessous, REPLACE() remplace la valeur old par new dans la colonne description. La requête s'exécute ligne par ligne pour effectuer le remplacement.

-- Sélectionne la colonne description de la table Products
-- et remplace les occurrences de 'old' par 'new'
SELECT 
    REPLACE(description, 'old', 'new') AS updated_description
FROM 
    Products;

Remarque : Les bases de données MySQL, PostgreSQL, Oracle et SQL Server prennent toutes en charge la fonction REPLACE().

Cas d'usage de REPLACE() en SQL

La fonction SQL REPLACE() offre de nombreux cas d'usage clés en manipulation de texte et transformation des données, notamment :

Remplacement simple

Vous pouvez utiliser REPLACE() pour effectuer un remplacement simple. Supposons que nous ayons la table suivante dans notre base SQL Server :

Exemple de table Products. Image de l'auteur.

Avec la requête SQL suivante, vous pouvez utiliser REPLACE() pour changer le statut old en new.

SELECT 
    product_id,
    product_name,
    price,
    REPLACE(status, 'old', 'new') AS updated_status
FROM Products;

La table résultante ressemblera à celle ci-dessous.

Table mise à jour avec SQL REPLACE(). Image de l'auteur.

Remplacer plusieurs sous-chaînes

Pour remplacer plusieurs sous-chaînes, enchaînez plusieurs appels à REPLACE(). Ici, nous remplaçons la valeur old par outdated et new par fresh.

-- Enchaîner plusieurs REPLACE() pour remplacer 'old' par 'outdated' et 'new' par 'fresh'
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(REPLACE(status, 'old', 'outdated'), 'new', 'fresh') AS updated_status
FROM Products;

La table résultante est présentée ci-dessous.

Table mise à jour avec plusieurs remplacements. Image de l'auteur.

Suppression de caractères

Passez une chaîne vide en troisième argument pour supprimer entièrement des caractères. C'est la méthode la plus propre pour retirer la ponctuation, les espaces superflus ou les caractères de formatage d'une colonne.

-- Supprimer les tirets des numéros de téléphone
SELECT
    customer_id,
    REPLACE(phone_number, '-', '') AS clean_phone
FROM Customers;

Par exemple, '555-867-5309' devient '5558675309'. La même technique permet de supprimer des caractères spéciaux, des artefacts d'encodage ou des espaces indésirables dans des données importées.

Gérer la sensibilité à la casse

La sensibilité à la casse de REPLACE() dépend de votre base et de la collation. PostgreSQL est sensible à la casse par défaut, donc REPLACE(col, 'Old', 'New') ne correspondra pas à 'old'. SQL Server et MySQL suivent la collation de la colonne ou de la base : la plupart des installations par défaut sont insensibles à la casse, donc REPLACE() fera correspondre quel que soit le cas.

Si vous souhaitez remplacer des valeurs sans tenir compte de la casse, utilisez UPPER() ou LOWER() pour convertir le texte avant le remplacement. Cette technique garantit l'homogénéité des valeurs.

-- Remplacer 'OLD' par 'OUTDATED' en gérant la casse via une conversion en majuscules
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(UPPER(status), 'OLD', 'OUTDATED') AS updated_status
FROM Products;

Table mise à jour avec les fonctions REPLACE() et UPPER(). Image de l'auteur.

-- Remplacer 'old' par 'outdated' en gérant la casse via une conversion en minuscules
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(LOWER(status), 'old', 'outdated') AS updated_status
FROM Products;

Table mise à jour avec les fonctions SQL REPLACE() et LOWER(). Image de l'auteur.

Techniques avancées

Ces techniques combinent REPLACE() avec d'autres fonctions SQL pour gérer des substitutions qui dépassent le simple rechercher-remplacer.

Remplacer avec des valeurs dynamiques

Vous pouvez effectuer des remplacements dynamiques en combinant REPLACE() avec d'autres fonctions SQL. Par exemple, vous pouvez utiliser la valeur d'une autre colonne pour remplacer la sous-chaîne. Dans l'exemple ci-dessous, REPLACE() remplace chaque old par la valeur réelle de product_name.

-- Remplacer 'old' par la valeur de la colonne product_name
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(status, 'old', product_name) AS updated_status
FROM Products;

Table mise à jour avec REPLACE() et des valeurs dynamiques. Image de l'auteur.

Utiliser REPLACE() dans des requêtes UPDATE

La fonction SQL REPLACE() s'utilise dans des instructions UPDATE pour modifier les valeurs d'une colonne sur une ou plusieurs lignes. La requête ci-dessous remplace old par outdated dans la colonne status.

-- Mettre à jour la colonne status en remplaçant 'old' par 'outdated'
UPDATE Products
SET status = REPLACE(status, 'old', 'outdated')
WHERE status LIKE '%old%';

Pour des scénarios plus complexes, comme la mise à jour d'une table via une jointure, consultez notre tutoriel SQL UPDATE with JOIN.

Combiner REPLACE() avec d'autres fonctions de chaîne

Une technique avancée consiste à combiner REPLACE() avec d'autres fonctions de chaîne SQL, comme CHARINDEX() ou SUBSTRING(), lors du remplacement de valeurs textuelles.

-- Remplacer la partie du nom de produit à partir du premier espace par ' Updated'
SELECT 
    product_id,
    product_name,
    price,
    -- Extraire la sous-chaîne de product_name depuis le premier espace jusqu'à la fin
    REPLACE(product_name, SUBSTRING(product_name, CHARINDEX(' ', product_name), LEN(product_name)), ' Updated') AS updated_product_name
FROM Products;

La table résultante de la requête ci-dessus est présentée ci-dessous.

Table mise à jour avec REPLACE() et d'autres fonctions de chaîne. Image de l'auteur.

Remplacer NULL par 0

Vous pouvez utiliser la fonction COALESCE() pour remplacer la valeur NULL par une valeur de repli avant d'appeler REPLACE(), comme ci-dessous.

-- Remplacer NULL par 'new' dans la colonne status
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(COALESCE(status, 'NULL'), 'NULL', 0) AS updated_status
FROM Products;

Écueil : si l'un des arguments passés à REPLACE() est NULL, la fonction renvoie NULL pour cette ligne — même si les autres arguments sont valides. Encapsulez les colonnes nullables dans COALESCE() avant d'appeler REPLACE(), pas après.

Applications pratiques de la fonction SQL REPLACE()

Voici des situations où REPLACE() a toute sa place dans des requêtes de production.

• Nettoyage des données : REPLACE() est utile pour nettoyer les données en supprimant ou remplaçant des caractères ou espaces indésirables dans les chaînes d'un jeu de données.

• Transformation des données : REPLACE() sert à transformer des valeurs, à changer de format ou à remplacer des valeurs obsolètes par de nouvelles.

• Gestion des saisies utilisateurs : REPLACE() facilite l'ajustement et la conversion des entrées utilisateurs vers un format standard conforme aux règles de la base. Cela contribue à l'uniformité des données.

• Standardisation du texte : REPLACE() aide à uniformiser les valeurs des colonnes de type texte, par exemple pour harmoniser des abréviations en minuscules ou majuscules.

• Remplacement conditionnel : REPLACE() est utile pour appliquer des règles de remplacement simples dans une optique de gestion courante de la base.

Implémentations spécifiques à chaque SGBD de REPLACE()

MySQL, PostgreSQL, Oracle et SQL Server prennent tous en charge la fonction SQL REPLACE(), avec une syntaxe similaire entre ces bases.

-- Remplacer 'old' par 'new' dans la colonne status
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(status, 'old', 'new') AS updated_status
FROM Products;

Performances et bonnes pratiques avec REPLACE()

Lorsque vous utilisez REPLACE(), gardez à l'esprit les considérations de performance et bonnes pratiques suivantes.

Impact sur les performances des requêtes

REPLACE() peut impacter les performances, en particulier sur de gros volumes de données. La fonction est évaluée pour chaque ligne, ce qui peut rallonger le temps d'exécution.

Considérations d'indexation

Indexez la colonne ciblée avant d'utiliser REPLACE() dans une clause WHERE ou un UPDATE. Sans index, la base doit analyser chaque ligne pour trouver les correspondances — un coût élevé sur de grandes tables.

Surveiller et résoudre les goulots d'étranglement

Pour atténuer les problèmes de performance, appliquez ces bonnes pratiques :

• Utilisez des outils de profilage pour surveiller les requêtes et les optimiser sur de gros jeux de données.

• Lors de modifications via REPLACE(), entretenez régulièrement les index pour garantir leur efficacité.

• Mettez en place des traitements par lots afin de réduire le temps d'exécution sur de grands volumes.

Pour approfondir les fonctions SQL avancées dédiées à la transformation des données, je vous recommande le parcours Learn SQL de DataCamp. Le cours Reporting in SQL aborde aussi l'optimisation des bases, avec différentes techniques pour améliorer les performances.

Dernières réflexions

La fonction REPLACE() couvre l'essentiel des besoins de substitution de texte dans les bases SQL. Gardez en tête le comportement avec les valeurs NULL — tout argument NULL renvoie NULL — et vérifiez les paramètres de collation de votre base lorsque la sensibilité à la casse est déterminante.

Pour en savoir plus sur REPLACE() en SQL, suivez les cours Intermediate SQL et SQL Fundamentals de DataCamp. Je vous recommande également le parcours Associate Data Analyst in SQL pour explorer les applications de SQL en transformation des données et accélérer votre carrière. Enfin, passez la SQL Associate Certification de DataCamp pour valoriser vos compétences en analyse de données avec SQL et vous démarquer en entretien.

Pour aller plus loin sur les fonctions SQL utilisées en analyse de données, consultez aussi mes autres tutoriels :

• How to Use the SQL BETWEEN Operator
• How to Use the SQL LIMIT Clause
• How to Use the SQL IN Operator
• How to Use the SQL OFFSET Clause