TSQL Astuce 3 : Découper une chaine de caractères délimitée par un séparateur

15 octobre 2014

TSQL Astuce 3 : Découper une chaine de caractères délimitée par un séparateur

Rédigé par marmous Aucun commentaire

Classé dans : Informatique Mots clés : TSQL, developpement, SQL Server

Voici une fonction qui renvoie une table où chaque élément est un morceau de la chaine initiale :

CREATE FUNCTION [dbo].[SplitString](    
     @String NVARCHAR(4000),    
     @Delimiter NCHAR(1))
RETURNS TABLE 
AS
RETURN (    
     WITH Split(stpos,endpos)
     AS(
         SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos        
         UNION ALL
         SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split            
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),        
           'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)    
    FROM Split
)

Cette fonction utilise plusieurs mécanisme de SQL Server.
Elle repose sur l'utilisation d'une CTE (http://msdn.microsoft.com/fr-fr/library/ms175972.aspx) qui retourne une table contenant la position de chaque "mot" en fonction du délimiteur passé en paramètre.
Cette table est ensuite utilisée pour construire la table qui sera retournée.
La fonction SUBSTRING découpe chaque "mot".
Notez l'utilisation des mots-clé COALESCE et NULLIF pour éviter les effets de bords.

Utilisation

select * from dbo.SplitString('pomme,banane,myrtille,fraise,cerise,melon,pastèque',',');

Cette requête renvoie :
1    pomme
2    banane
3    myrtille
4    fraise
5    cerise
6    melon
7    pastèque