Découper un texte délimité avec SQL Server et les fonctions XML ou STRING_SPLIT

Comment découper un texte délimité avec les fonctions SQL Server ?

Pour découper un texte délimité avec SQL Server, c’est à dire une variable ou une colonne avec un délimiteur de type virgule ou point-virgule, par exemple. En utilisant une requête SQL Server, vous pouvez couper une chaîne composée de texte, délimitée par des virgules, des points-virgules, des tabulations, des tirets, des tirets bas ou même des points.

Il peut s’agir en fait de n’importe quel caractère qui définit un texte délimité. En effet, dans les versions précédentes à SQL Server 2016, le moyen le plus simple et le plus efficace est d’utiliser les fonctions XML intégrées SQL Server comme CONVERT et NODES. Utilisez ensuite une requête Transact-SQL pour diviser le texte en plusieurs lignes.

Fonctions XML SQL Server pour découper un texte avec délimiteur

En effet, la puissance des fonctions XML permet au développeur SQL d’analyser et de structurer le texte plus facilement. Par exemple, découpons facilement cette chaîne contenant les dix plus grandes villes américaines délimitées par des points-virgules :

“New York ; Los Angeles ; Chicago ; Houston ; Phoenix ; Philadelphie ; San Antonio ; San Diego ; Dallas ; San Jose”

Cette solution permet de découper les textes sur les versions antérieures à SQL Server 2016, comme par exemple :

• SQL Server 2008 R2
• SQL Server 2012
• et SQL Server 2014

Cet exemple fonctionne avec tous les types de séparateurs, également appelés délimiteurs. Les plus utilisés étant les fichiers délimités les séparateurs suivants :

• Virgules : ,
• Point-virgules : ;
• Tabulations : \t
• Barres verticales : |
• Tirets : –

Par exemple, cette requête Transact-SQL découpe la chaîne contenant la liste des villes délimitées par des points-virgules.

-- Déclaration des variables
DECLARE @String nvarchar(max),
@Delimiter char(1),
@XMLString xml ;

-- Initialisation de la chaîne de caractères et du délimiteur, ici c'est le caractère point-virgule
SELECT @String = 'New York;Los Angeles;Chicago;Houston;Phoenix;Philadelphie;San Antonio;San Diego;Dallas;San Jose',
@Delimiter = ';'

-- Construction d'une chaîne XML à l'aide de la fonction T-SQL CONVERT()
SET @XMLString =
CONVERT(xml,'' + REPLACE(@String,@Delimiter,'') + '')
SELECT @XMLString;

-- Sélection du résultat en analysant la variable @XMLString avec les fonctions XML .value() et .nodes()
SELECT Resultat.value('.', 'varchar(20)') AS CITY
FROM @XMLString.nodes('/root/city') AS T(Resultat);

En effet, pour comprendre la logique de la requête, la variable @XMLString ne contient que la liste initiale des villes. Cependant, cette fois-ci, les balises XML <root></root> et <city></city> entoure la liste des villes.

<root>
	<city>New York</city>
	<city>Los Angeles</city>
	<city>Chicago</city>
	<city>Houston</city>
	<city>Phoenix</city>
	<city>Philadelphia</city>
	<city>San Antonio</city>
	<city>San Diego</city>
	<city>Dallas</city>
	<city>San Jose</city>
</root>

De même, un autre tutoriel explique comment supprimer les sauts de ligne d’une chaîne de caractères SQL Server afin de la stocker sur une seule ligne d’une table.

Couper un texte avec STRING_SPLIT depuis MS SQL 2016

En effet, veuillez noter qu’à partir de SQL Server 2016, une nouvelle fonction intégrée permet cette fonctionnalité. Il s’agit de la fonction String Split. Voici donc un exemple pour obtenir le même résultat en une seule ligne de code :

-- Déclaration de variables
DECLARE
	@String nvarchar(max),
	@Separator char(1);

-- Initialisation de la chaîne de caractères et du séparateur
SET @String = 'New York;Los Angeles;Chicago;Houston;Phoenix;Philadelphie;San Antonio;San Diego;Dallas;San Jose' ;
SET @Separator = ";";

-- Utilisez le Split String ici au lieu de la solution XML
SELECT @String = STRING_SPLIT (@String, @Separator);

En résumé, ce tutoriel montre pas à pas comment découper un texte délimité avec les fonctions XML ou la fonction Split_String de SQL Server. Le premier exemple utilise les fonctions XML et il est facile à copier et coller. Le deuxième exemple utilise une unique fonction intégrée : Split_String.

Vous pouvez donc le réutiliser pour un scénario de projet réel ou autre Proof-Of-Concept. De même, le deuxième exemple utilise simplement la fonction native du serveur SQL Split_String.

Questions fréquentes sur les champs textes délimités SQL Server

Procédure T-SQL avec une variable en dur et du code dynamique