SQL : comment découper une chaîne pour accéder à un item ?

En SQL, il n'est pas possible de manipuler directement les chaînes de caractères récupérées, pour par exemple les découper selon un séparateur. Il est cependant possible de contourner le problème.

Le langage SQL est principalement utilisé pour effectuer des requêtes sur une base de données. On ne pouvait pas manipuler directement les chaînes de caractères récupérées, pour par exemple les découper selon un séparateur. Il est cependant possible de contourner ce problème avec des cas particuliers ou alors en développant une fonction. Depuis la version 2016 de SQL Server, il existe une fonction implémentée qui peut découper une chaîne de caractères.

La fonction PARSENAME() est normalement utilisée pour retourner la partie spécifiée d'un nom d'objet. On peut détourner son usage pour séparer une chaîne de caractères mais celle-ci doit ressembler à un nom d'objet. Un nom d'objet est constitué de quatre parties séparées par un point. On ne peut donc utiliser cette technique que pour une chaîne qui n'a pas déjà des points et qui ne comporte qu'au maximum quatre parties. Il ne faut pas oublier de remplacer le caractère de séparation par des points avec la fonction REPLACE(). Le deuxième argument de la fonction PARSENAME() est l'index de l'élément à retourner, en partant du dernier.

SELECT PARSENAME(REPLACE('Séparer cette chaîne', ' ', '.'), 1) -- Retourne chaîne

Une autre solution consiste à développer une procédure qui va parcourir la chaîne dans une boucle. La directive PATINDEX retourne l'index trouvé du séparateur. On utilise ensuite SUBSTRING pour découper la chaîne et récupérer l'élément situé avant le séparateur. On modifie ensuite la chaîne en ne conservant que ce qui est après le séparateur trouvé. De cette manière, on découpe la chaîne au fur et à mesure.

Declare @liste varchar(200) = '1|20|3|343|44|6|8765'
Declare @element varchar(20) = null
WHILE LEN(@liste) > 0
BEGIN
IF PATINDEX('%|%',@liste) > 0
BEGIN
SET @element = SUBSTRING(@liste, 0, PATINDEX('%|%',@liste))
SELECT @element

SET @liste = SUBSTRING(@liste, LEN(@element + '|') + 1,
LEN(@liste))
END
ELSE
BEGIN
SET @element = @liste
SET @liste = NULL
SELECT @element
END
END</pre>

Une autre solution consiste à écrire une fonction. Cette fonction va utiliser les CTE pour créer une table temporaire qui contiendra les éléments de la chaîne découpée. Elle va ensuite utiliser la récursivité pour découper la chaîne selon le séparateur indiqué.

<pre>create function dbo.SplitString
(
@chaine nvarchar(max),
@separateur char(1)
)
returns table
AS
return (
with tokens(p, a, b) AS (
select
cast(1 as bigint),
cast(1 as bigint),
charindex(@separateur, @chaine)
union all
select
p + 1,
b + 1,
charindex(@separateur, @chaine, b + 1)
from tokens
where b &gt; 0
)
select
p-1 ItemIndex,
substring(
@chaine,
a,
case when b &gt; 0 then b-a ELSE LEN(@chaine) end)
AS s
from tokens
);
GO
-- Utilisation de la fonction
select s from dbo.SplitString('Séparer cette chaîne', ' ') where zeroBasedOccurance=1</pre>

Cette procédure ne fonctionnera que jusqu'à 100 éléments à cause de la limite de la récursivité du serveur. Il est possible d'augmenter cette limite en modifiant le paramètre MAXRECURSION mais plus le paramètre sera élevé, plus le serveur consommera de ressources. On peut également modifier la valeur à 0, ce qui enlève la limite mais ce réglage peut provoquer un plantage du serveur.

Si vous utilisez la version 2016 de SQL Server, il existe la fonction STRING_SPLIT() qui peut découper une chaîne selon un séparateur. On l'utilise dans la clause FROM et elle retourne une table d'une seule colonne contenant les fragments.

<pre>SELECT valeur FROM STRING_SPLIT('Séparer cette chaîne', ' ');
-- Résultat
valeur
Séparer
cette
chaîne</pre>