Comment Gérer les Retours à la Ligne avec SQL Server ?

Comment insérer ou supprimer les retours à la ligne avec SQL Server, également appelés retours de chariot, dans les chaînes, les colonnes et les variables ?

Scripts Transact-SQL pour gérer les retours à la ligne avec SQL Server dans les dans les chaînes de caractères stockées en colonnes ou dans des variables. En d’autres termes, comment les supprimer ou les insérer pour rationaliser les processus d’intégration de données. Par exemple, après l’exportation d’une requête SQL de SSMS vers un document Excel, certaines lignes sont divisées en deux parties différentes. Et les lignes sont interrompues. Cela est dû à des erreurs de saisie dans les logiciels sources, y compris les sauts de ligne.

Pour reproduire ce problème, copiez les données des résultats de votre requête 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 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 d’erreur en détail. Vous devez donc 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 (CR) 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é. 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 de données.

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

Tout d’abord, préparons un 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 ce tutoriel.

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

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

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

-- 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');

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

Ensuite, si vous copiez et collez la colonne AddressText du résultat SQL Server, vous remarquerez que l’adresse contient deux sauts de ligne comme dans l’instruction INSERT. Vous remarquez 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 intégrée REPLACE de SQL Server.

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’instruction INSERT:

123,
Mount Gatineau Avenue,
Ottawa

Pour supprimer ces sauts de ligne SQL, utiliser la fonction replace du serveur SQL. En effet, les codes spécifiques CHAR(13) et CHAR(10) représentent les caractères du saut de ligne, 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 nous 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 sont simplement deux opération inverses, mais effectuées avec les mêmes fonctions textes T-SQL.

Voici un autre tutoriel pour apprendre comment 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)
• CHAR(10)

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;

Le contrôle des sauts de ligne avec du code 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 CHAR(10) et CHAR(13) du serveur SQL sont remplacés, ou insérés si nécessaire. En général, les contrôles d’entrée dans les applications aident beaucoup à réduire le nombre de caractères invalides insérés dans une base de données.

En effet, il permet de réduire considérablement les erreurs lors de la mise en forme manuelle des données et de leur transfert entre des applications comme SSMS et Excel par exemple. Et plus encore les erreurs d’intégration de données par lots. Comme l’échange automatique de données via des tâches programmées SSIS, ou lors de transferts entre bases de données ou à 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