T-sql - split sans table temporaire

Commenter

Contenu du snippet

Voici un algorithme qui permet de faire un split sur une colonne d'une table, et de sortir les données splittées sans passer par une table temporaire.

Source / Exemple : 


CREATE     PROCEDURE dbo.sp_SPLIT
	@tableName as varchar(200)    ,
	@col as varchar(200) ,
	@nbChamps as int,
	@sep as varchar(4000),
	@firstCol as int
	   
AS
set nocount on

declare @sql nvarchar(4000)
declare @subStrPrec nvarchar(4000)
declare @colLen nvarchar(4000)
declare @curIndex varchar(4000)
declare @sFirstCol varchar(10)
set @sFirstCol = cast(@firstCol as varchar)
set @curIndex = @sFirstCol

declare @i int
set @i = 1
set @sql = ''

WHILE @i <= @nbChamps begin

	if @i = 1 begin
		-- Pour la 1ère ligne, aller jusqu'à la 1ere occurence du séparateur
		set @subStrPrec = 'SUBSTRING(' +
			@col + ',' +
			@sFirstCol + ',' + -- 1ère colonne
			'CHARINDEX(''' + @sep + ''',' + @col + ',' + @sFirstCol + ')-' + @sFirstCol + ')'
	end
	else begin
		--  Lignes suivantes : calcul de la longueur de la colonne (de currentIndex au prochain séparateur / fin)
		if @i < @nbChamps begin
			print '< nbChamps'
			print '@sep=' + @sep
			print '@col=' + @col
			print '@curIndex=' + @curIndex
			set @colLen = '(CHARINDEX(''' + @sep + ''',' + @col + ',' + @curIndex + ')-(' + @curIndex + '))'
		end else begin
			print '= nbChamps'
			set @colLen = 999 --  // Dernière colonne : aller jusqu'en fin de ligne (il n'y a plus de séparateur !) - NB : on pourrait le calculer : colLen=LEN(TMP_COL)-currentIndex
		end
		
		set @subStrPrec = 'SUBSTRING(' + @col + ',' + @curIndex + ',' + @colLen + ')'
	end
	
	set @sql = @sql + ' RTRIM(LTRIM(' + @subStrPrec + ')),';
	-- MAJ de la position dans la ligne
	set @curIndex = @curIndex + '+LEN(' + @subStrPrec + ')+' + cast(LEN(@sep) as varchar)
	
	print 'i=' + cast(@i as varchar)
	print @sql

	set @i = @i + 1
END

-- On retire la virgule de la fin
set @sql = SUBSTRING(@sql, 1, LEN(@sql)-1)

set @sql = 'SELECT ' + @sql + ' FROM ' + @tableName

execute(@sql)

GO

-- Exemple de code client :

declare @tableName as varchar(200)  
declare	@col as varchar(200)
declare	@nbChamps as int
declare	@sep as varchar(4000)
declare	@firstCol as int

set @tableName = 'TABLE_SOURCE'
set @col = 'COLUMN_1'
set @nbChamps = 3
set @sep = ' ; '
set @firstCol = 1

delete TABLE_FINALE
INSERT INTO TABLE_FINALE exec dbo.sp_SPLIT @tableName, @col, @nbChamps, @sep, @firstCol

GO

select * from TABLE_SOURCE
select * from TABLE_FINALE

-- L'instruction SELECT générée par la procédure est la suivante :

SELECT  
RTRIM(LTRIM(SUBSTRING(COLUMN_1,1,CHARINDEX(' ; ',COLUMN_1,1)-1))), 
RTRIM(LTRIM(SUBSTRING(COLUMN_1,1+LEN(SUBSTRING(COLUMN_1,1,CHARINDEX(' ; ',COLUMN_1,1)-1))+2,(CHARINDEX(' ; ',COLUMN_1,1+LEN(SUBSTRING(COLUMN_1,1,CHARINDEX(' ; ',COLUMN_1,1)-1))+2)-(1+LEN(SUBSTRING(COLUMN_1,1,CHARINDEX(' ; ',COLUMN_1,1)-1))+2))))), 
RTRIM(LTRIM(SUBSTRING(COLUMN_1,1+LEN(SUBSTRING(COLUMN_1,1,CHARINDEX(' ; ',COLUMN_1,1)-1))+2+LEN(SUBSTRING(COLUMN_1,1+LEN(SUBSTRING(COLUMN_1,1,CHARINDEX(' ; ',COLUMN_1,1)-1))+2,(CHARINDEX(' ; ',COLUMN_1,1+LEN(SUBSTRING(COLUMN_1,1,CHARINDEX(' ; ',COLUMN_1,1)-1))+2)-(1+LEN(SUBSTRING(COLUMN_1,1,CHARINDEX(' ; ',COLUMN_1,1)-1))+2))))+2,999))) 
FROM TABLE_SOURCE

Conclusion :


Voici le principe, qui n'est pas évident à expliquer :

Regardez l'instruction SQL qui est générée à la fin du code source.
Le code qui permet de retourner la 1ère chaîne :

SUBSTRING(COLUMN_1, 1, CHARINDEX(' ; ',COLUMN_1,1)-1)

se retrouve dans le calcul de la recherche du 2ème séparateur. En effet, il suffit de faire un LEN() de cette instruction pour savoir où commencer à chercher la prochaine occurrence du séparateur.
Ainsi de suite, l'instruction qui a permis de trouver la chaîne se retrouve dans le calcul de la chaîne suivante (ce qu'on calcule c'est la position des occurrences du séparateur).

En fait, c'est une requête fractale ^^

Le gros avantage de cette méthode est la rapidité d'exécution, par rapport à une solution classique dans laquelle, à chaque boucle, on sauvegarde les morceaux de chaînes splittées dans une table temporaire, ce qui peut très rapidement générer des milliers d'insert.

La seule limite qui existe est la longueur maximale de la requête générée.

A voir également

S'inscrire maintenant

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.

Du même auteur (TheOnlyMaX)