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.
Table of Contents
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.
- Vérifier si la table Clients existe et supprimez-la si nécessaire.
- Créer la table Clients.
- Insérer une ligne unique de données, avec plusieurs sauts de ligne, dans la colonne Adresse.
- 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.
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.
Questions sur la gestion des retours chariots en T-SQL
Un retour chariot, également appelé saut de ligne, est une commande qui permet de couper une ligne de texte en deux ou plusieurs parties et de poursuivre le texte sur la ligne suivante.
Notepad++ est considéré comme l’un des meilleurs outils gratuits pour éditer des fichiers texte. Cet éditeur de texte offre une grande flexibilité dans la modification de fichiers de taille moyenne sans erreur. Il dispose également d’une reconnaissance avancée de la syntaxe pour de nombreux langages de programmation tels que XML, SQL, HTML, MS-DOS, Java, PHP, entre autres.
Les caractères spéciaux char(13) et char(10) sont utilisés pour les retours à la ligne dans les fichiers texte. Char(13) correspond au retour chariot, qui ramène le curseur au début de la ligne, tandis que char(10) correspond au saut de ligne, qui avance le curseur à la ligne suivante. Les systèmes d’exploitation Windows utilisent souvent la séquence de caractères char(13)+char(10) pour les retours à la ligne, tandis que les systèmes Unix ou Linux utilisent généralement uniquement char(10).
Pour supprimer les sauts de ligne dans une colonne avec SQL Server, utiliser la fonction REPLACE. Cette fonction remplace toutes les occurrences d’une chaîne de caractères par une autre dans une colonne de la table ou de la variable sélectionnée.