Comment gérer les retours à la ligne avec SQL Server ?

Comment insérer ou supprimer des retours à la ligne avec des scripts T-SQL, aussi appelés retour chariot, depuis des chaînes de caractères, des colonnes ou des variables ?

Exemples de scripts fonctionnels pour gérer les retours à la ligne avec SQL Server, dans les chaînes de caractères, stockées en colonnes ou encore dans des variables.

Soit comment les supprimer ou encore les insérer pour rationaliser les processus d’intégration de données, et éviter les erreurs. Par exemple, après l’exportation d’une requête SQL depuis SSMS vers un document Excel, certaines lignes sont divisées en deux parties distinctes : les lignes sont interrompues par un retour à la ligne.

Cela est principalement dû à des erreurs de saisie dans les logiciels sources:

• Copier – coller depuis un autre logiciel ou traitement de texte
• Utilisation de la touche Entrée dans un champ non prévu pour cela
• Bien sûr, pas de contrôle au niveau du logiciel métier pour corriger ou alerter sur la qualité des données.

Pour reproduire ce problème, copiez les résultats de votre requête SQL, depuis SSMS, et collez-les dans votre éditeur de texte favori tel que Notepad++. Il est possible que des sauts de ligne apparaissent.

Dans le même cas d’utilisation lors de l’intégration de fichiers CSV dans SQL Server à l’aide de SSIS, ces sauts de ligne provoquent des erreurs lors de l’exécution du package ou des données complètement incohérentes dans la table cible.

En fonction de la taille des fichiers sources, il peut être très difficile d’identifier toutes les lignes avec des erreurs. Il faut alors automatiser cette détection à l’aide d’une requête et les remplacer pour garantir une intégration correcte.

A propos des sauts de ligne dans les fichiers textes

Dans les fichiers de type texte, le caractère appelé saut de ligne ou retour chariot indique au curseur de passer au début de la ligne suivante. En d’autres termes, la séquence CR+LF sert de marqueur pour indiquer la fin d’une ligne logique et le début d’une autre dans un fichier donné.

Pour information, en Anglais:

• CR = Carriage Return
• LF = Line Feed

Cette séquence est présente à la fin de chaque ligne logique d’un fichier texte. Cette séquence est utilisée pour différencier les lignes de texte dans un fichier.

1. Insérer des retours à la ligne dans une table SQL Server

Tout d’abord, préparons une table SQL avec quelques exemples de données contenant des sauts de ligne. Nous avons divisé la colonne adresse en trois lignes différentes. La requête ci-dessous exécute 4 opérations SQL Server différentes pour insérer des sauts de ligne que nous gérerons dans les étapes suivantes de cet article.

1. Vérifier si la table CUSTOMERS (Clients) existe et la supprimer si nécessaire.
2. Créer la table CUSTOMERS.
3. Insérer une ligne unique de données, avec plusieurs sauts de ligne, dans la colonne Adresse (AddressText).
4. Sélectionner toutes les données de la table.

-- 1. Si la table des clients existe déjà, alors supprimer la table
IF exists( 	SELECT 1 FROM sys.objects
            WHERE object_id = object_id(N'dbo.CUSTOMERS') AND type in (N'U') )
BEGIN DROP TABLE [dbo].[CUSTOMERS]
END;

-- 2. Création de la table des clients
CREATE TABLE [dbo].[CUSTOMERS] (
    [CustomerID] INT IDENTITY(1,1),
    [FirstName] NVARCHAR(20),
    [LastName] NVARCHAR(20),
    [AddressText] NVARCHAR(100),
    [CountryCode] NVARCHAR(3),
    [CountryName] NVARCHAR(50)
);

-- 3. Insertion des coordonnées d'un client avec des sauts de lignes
INSERT INTO dbo.CUSTOMERS ( FirstName, LastName, AddressText, CountryCode, CountryName )
VALUES ( N'SMITH',N'John', 
N'123,
Mount Gatineau Avenue,
Ottawa',
N'CA',N'CANADA');

-- 4. Vérifier les résultats avec une requête SELECT
SELECT 	* 
FROM 	dbo.CUSTOMERS;

Ensuite, copier et coller la colonne AddressText du résultat SQL Server, noter que l’adresse contient deux sauts de ligne comme dans l’instruction INSERT.

123,
Mount Gatineau Avenue,
Ottawa

Pour aller plus loin, voici la documentation de la fonction REPLACE de SQL Server, depuis le site officiel de Microsoft.

2. Supprimer les retours chariots avec la fonction REPLACE en T-SQL

Pour continuer, copier et coller maintenant la colonne Texte de l’adresse à partir du résultat SQL Server. Remarquer que l’adresse contient deux sauts de ligne, comme dans l’étape 1 :

123,
Mount Gatineau Avenue,
Ottawa

Pour supprimer ces sauts de ligne en SQL, utiliser la fonction REPLACE. En effet, les codes spécifiques CHAR(13) et CHAR(10) représentent les caractères du saut de ligne, respectivement CR et LF.

Puis on les remplace par un espace ou une virgule, par exemple:

-- Vérifier les résultats
SELECT   CustomerID, FirstName, LastName, AddressText
FROM   dbo.CUSTOMERS;

-- Utiliser de la fonction REPLACE pour supprimer les sauts de ligne
SELECT   CustomerID, FirstName, LastName,
        REPLACE(AddressText,CHAR(13)+CHAR(10),' ')
FROM dbo.CUSTOMERS;

Cette fois, le copier-coller du texte donne ce résultat. Il affiche clairement l’adresse du client sans aucun retour à la ligne. Les trois parties de l’adresse sont maintenant sur la même ligne.

123, avenue du Mont Gatineau, Ottawa

Ainsi ajouter ou supprimer un retour chariot avec SQL Server simplement avec deux opération inverses, mais effectuées avec les mêmes fonctions textes T-SQL: CHAR() et REPLACE().

Voici un autre tutoriel pour apprendre à découper un texte après un caractère avec SQL Server.

Découper un texte après un caractère en T-SQL

3. Ajouter un retour à la ligne à une colonne en Transact-SQL

A l’inverse, pour ajouter un retour chariot à une colonne depuis une requête T-SQL, il suffit donc d’ajouter les deux caractères spéciaux ci-dessous, concaténés:

• CHAR(13) = CR
• CHAR(10) = LF

La requête ressemble à cela. Pour voir les retours chariots, activer l’option « Résultats vers Textes » depuis SSMS.

-- Requête pour ajouter un retour chariot et construire l'adresse complète du client
SELECT 
	[CustomerID]
	,[FirstName]
	,[LastName]
	,[AddressText] + CHAR(13) + CHAR(10)
	+[CountryCode] + CHAR(13) + CHAR(10)
	+[CountryName] AS [FullAdress]
FROM 	dbo.CUSTOMERS;

Un meilleur contrôle des sauts de ligne en T-SQL améliore la qualité des données

En conclusion, il est relativement facile de gérer les retours à la ligne avec des requêtes SQL Server, à condition de bien les comprendre.

Ils peuvent être gérés en s’assurant que les caractères spéciaux CHAR(10) et CHAR(13) sont détectés. En général, les contrôles de saisie dans les applications métiers aident à réduire le nombre de caractères invalides insérés dans une base de données, à la source.

En effet, ces contrôles réduisent considérablement les erreurs de lecture lors de leur transfert entre des applications comme SSMS et Excel par exemple. Et plus encore les erreurs d’intégration de données par packages ou traitements (batchs). Comme l’échange automatique de données via des packages ordonnancés SSIS, ou encore des transferts entre bases de données à l’aide d’API.

Découper un texte après un caractère en T-SQL

Questions sur la gestion des retours chariots en T-SQL