Fonction split simple

**Donpi** · 08/08/2014, 12h06

Bonjour,

Voilà j'écrivais fièrement une fonction d'extraction de chaines en fonction d'un séparateur et d'un indice... Demandez pas pourquoi j'ai trop honte.

Le fait c'est que j'ai trouvé tellement d'aide sur ce forum que je me suis dit pourquoi pas partager ...

Alors voilà, si un jour ça sert à qqun.

Elle est facilement modifiable pour avoir toute la liste.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- =============================================
-- Author:		Juan Jimenez
-- Create date: 08.08.2014
-- Description:	Extraction de chaine a l'indice spécifié avec le séparateur spécifié
-- =============================================
CREATE FUNCTION fExtraitChaine 
(
	@Texte varchar(max),
	@Indice int,
	@Separateur varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
	Declare @Resultat varchar(max);
	With Split AS
				(
					Select CASE CHARINDEX(@Separateur,@Texte,1) WHEN 0 THEN '' ELSE Substring(@Texte,1,CHARINDEX(@Separateur,@Texte,1)-1) END  Element, Substring(@Texte,CHARINDEX(@Separateur,@Texte,1)+len(@Separateur),len(@Texte)) Rest,CHARINDEX(@Separateur,@Texte,1) Pos,1 ID
					UNION ALL
					SELECT CASE CHARINDEX(@Separateur,R.Rest,1) WHEN 0 THEN R.Rest ELSE Substring(R.Rest,1,CHARINDEX(@Separateur,R.Rest,1)-1) END Element, Substring(R.Rest,CHARINDEX(@Separateur,R.Rest,1)+len(@Separateur),len(R.Rest)) Rest,CHARINDEX(@Separateur,R.Rest,1) Pos,R.Id+1 ID
					FROM (Values(1)) Tbl(Bidon)
					INNER JOIN Split R on 1 = 1 AND R.Element<>R.Rest
					WHERE R.Id+1<=@Indice
				)
	Select @Resultat = Element
	from Split
	Where CHARINDEX(@Separateur,@Texte,1)<> 0 and Id=@Indice
 
	RETURN @Resultat
END
GO

**aieeeuuuuu** · 08/08/2014, 17h10

Bonjour et merci pour le partage.

Les manipulations de chaine n'étant le fer de lance des SGBD en général, il serait préférable de les réduire au minimum. Ainsi, dans votre récursive, vous pourriez vous contenter de calculer les bornes (l'emplacement de chaque séparateur), et de ne faire qu'un SUBSTRING final :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
CREATE FUNCTION fExtraitChaine
(
	@Texte varchar(max),
	@Indice int,
	@Separateur varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
	Declare @Resultat varchar(max);
 
	;WITH Split AS
				(
					SELECT 1 as i, CAST(0 AS BIGINT) as sep 
					UNION ALL
					SELECT i + 1, CHARINDEX(@Separateur,@Texte,sep + 1)
					FROM Split  
					WHERE i<=@Indice
					AND CHARINDEX(@Separateur,@Texte,sep + 1) > 0
				)
	SELECT @Resultat = SUBSTRING(@Texte, sep + 1, CHARINDEX(@Separateur,@Texte + @separateur,sep + 1) - (sep + 1))
	from split
	WHERE i = @Indice
 
	RETURN @Resultat
END

Cependant, il sera peut être plus performant de passer par du xml plutôt qu'une récursive, surtout si la chaine est longue et contient beaucoup de séparateurs :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
CREATE FUNCTION fExtraitChaine
(
	@Texte varchar(max),
	@Indice int,
	@Separateur varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
	DECLARE @x XML
	DECLARE @Result VARCHAR(MAX)
	SET @x =  CAST('<c>' + REPLACE(@Texte, @Separateur, '</c><c>') + '</c>' AS XML)
	SET @Result =  @x.value('c[sql:variable("@Indice")][1]', 'VARCHAR(MAX)')
	RETURN @Result
END

Enfin, dans votre cas particulier, pour ne récupérer qu'une seule occurrence à partir de son indice, le mieux sera sûrement un traitement en boucle sur la variable @Texte :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
CREATE FUNCTION fExtraitChaine
(
	@Texte varchar(max),
	@Indice int,
	@Separateur varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
        --si l'indice est plus grand que le nombre de séparateurs, on renvoie NULL
	IF PATINDEX(REPLICATE('%' + @Separateur, @Indice - 1) + '%', @Texte) =  0 
		RETURN NULL
 
	DECLARE @n INT = 1
	WHILE	@n < @Indice
		SELECT @Texte = SUBSTRING(@Texte, CHARINDEX(@Separateur,@Texte)+LEN(@Separateur), 2000000000), @n += 1
 
	SET @texte = LEFT(@Texte,CHARINDEX(@Separateur,@Texte + @separateur) - 1)
 
	RETURN @Texte
END

**hmira** · 08/08/2014, 17h20

Oui, Merci Donpi cela peut toujours servir !

Je me suis inspiré des fonctions split de Elsulet (Nicolas Souquet) sur ce blog :
http://blog.developpez.com/elsuket/p...et_inversement

pour créer une variante, plus "light" de ta fonction fExtraitChaine_2

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE FUNCTION dbo.fExtraitChaine_2 
(
	@Texte varchar(8000),
	@Sep char(1), 
	@Indice int
 
)
RETURNS varchar(8000)
AS
BEGIN
	Declare @Resultat varchar(8000);
	WITH Split AS
				( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL) ) AS Indice, 
                   N.v.value('.', ' varchar(32)') AS value
                  FROM  (VALUES (CAST('<v>' + REPLACE(RIGHT(LEFT(@Texte, LEN(@Texte) - 1), LEN(@Texte) - 2),   
                  @Sep, '</v><v>') + '</v>' AS xml))) AS X(v)
                  CROSS APPLY	X.v.nodes('/v') AS N(v)
				)
	SELECT @Resultat = value
	FROM Split
	WHERE Indice=@Indice; 
 
	RETURN @Resultat
END
GO

A+

**elsuket** · 10/08/2014, 10h26

Bonjour,

Les fonctions scalaires et de table à instructions multiples sont contre-performantes, puisqu'elles sont exécutées 1 fois par ligne de résultat.
On peut donc faire évoluer la fonction comme suit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE FUNCTION dbo.fExtraitChaine_3
	(
		@chaine varchar(max)
		, @separateur varchar(2)
		, @indice int = NULL
	)
RETURNS TABLE
AS RETURN
(
	SELECT	D.indice
		, D.valeur_scalaire
	FROM	(
			SELECT		ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS indice
					, Y.vs.value('.', 'varchar(max)') AS valeur_scalaire
			FROM		(
						VALUES
						(
							CAST('<v>'+ REPLACE(@chaine, @separateur ,'</v><v>') + '</v>' AS  xml)
						)
					) AS X(v)
			CROSS APPLY	X.v.nodes('/v') AS Y(vs)
		) AS D
	WHERE	D.indice = @indice
	OR	@indice IS NULL
)

Et quelques exemples d'appel :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT	*
FROM	dbo.fExtraitChaine_3('toto,titi,tutu,tata', ',', NULL)

Ou encore avec le petit jeu de données suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE chaine
(
	id tinyint NOT NULL IDENTITY (0,1)
		CONSTRAINT PK_chaine PRIMARY KEY
	, uneChaine varchar(128) NOT NULL
)
GO
 
INSERT INTO dbo.chaine (uneChaine) VALUES ('jiji,jojo,juju,jaja')
INSERT INTO dbo.chaine (uneChaine) VALUES ('toto,titi,tutu,tata')
GO
 
SELECT		C.id
		, FEC.valeur_scalaire
FROM		dbo.chaine AS C
CROSS APPLY	dbo.fExtraitChaine_3(C.uneChaine, ',', NULL) AS FEC

**StringBuilder** · 11/08/2014, 15h49

Juste une petite question.

Avec la fonction SPLIT, n'est-on pas dans un cas où utiliser une fonction CLR est un vrai plus ?

D'un point de vue performances, ça va être dur de faire mieux qu'un String.Split() en .NET j'imagine.
Et d'un point de vue syntaxe aussi... (donc maintenabilité, tout ça, etc.)

Votre avis ?

**Donpi** · 11/08/2014, 16h51

Envoyé par StringBuilder

Juste une petite question.

Avec la fonction SPLIT, n'est-on pas dans un cas où utiliser une fonction CLR est un vrai plus ?

D'un point de vue performances, ça va être dur de faire mieux qu'un String.Split() en .NET j'imagine.
Et d'un point de vue syntaxe aussi... (donc maintenabilité, tout ça, etc.)

Votre avis ?

Bas mon avis c'est que je vais faire un benchmarck (je sais pas écrire benchmarck) pour être sur

**Tidus159** · 12/08/2014, 16h58

Envoyé par StringBuilder

Juste une petite question.

Avec la fonction SPLIT, n'est-on pas dans un cas où utiliser une fonction CLR est un vrai plus ?

D'un point de vue performances, ça va être dur de faire mieux qu'un String.Split() en .NET j'imagine.
Et d'un point de vue syntaxe aussi... (donc maintenabilité, tout ça, etc.)

Votre avis ?

Bonjour, certainement mais parfois on a besoin d'un paramètre (chaine) pour traiter des listes variables d'éléments dans une procédure...

**StringBuilder** · 12/08/2014, 21h07

C'est pour ça que je parle de fonctions CLR, donc des procédures stockées et fonctions stockées dans SQL Server, écrites avec .NET

Donc pour les traitements de calcul intensif, ou manipulations de chaînes, là où T-SQL pêche énormément, on peut tirer partie de .NET directement dans SQL Server.

http://msdn.microsoft.com/fr-fr/library/ms131103.aspx

**StringBuilder** · 12/08/2014, 22h18

Pour illustrer ma réponse, un exemple concret avec code complet :
http://www.developpez.net/forums/d14...r/#post7927380

**Tidus159** · 13/08/2014, 10h42

Bonjour, je ne connaissais pas cette fonction de SQL Server, d'où ma réponse HS.
Merci pour ton poste explicatif très intéressant (que je commente de ce pas).
A+

Fonction split simple

MS SQL Server

Discussions similaires

Partager

Partager