Chaîne divisée T-SQL

J’ai une colonne SQL Server 2008 R2 contenant une chaîne que je dois séparer par une virgule. J’ai vu beaucoup de réponses sur StackOverflow mais aucune ne fonctionne dans R2. Je me suis assuré d’avoir des permissions de sélection sur tous les exemples de fonctions fractionnées. Toute aide grandement appréciée.

J’ai déjà utilisé ce SQL qui peut vous convenir: –

 CREATE FUNCTION dbo.splitssortingng ( @ssortingngToSplit VARCHAR(MAX) ) RETURNS @returnList TABLE ([Name] [nvarchar] (500)) AS BEGIN DECLARE @name NVARCHAR(255) DECLARE @pos INT WHILE CHARINDEX(',', @ssortingngToSplit) > 0 BEGIN SELECT @pos = CHARINDEX(',', @ssortingngToSplit) SELECT @name = SUBSTRING(@ssortingngToSplit, 1, @pos-1) INSERT INTO @returnList SELECT @name SELECT @ssortingngToSplit = SUBSTRING(@ssortingngToSplit, @pos+1, LEN(@ssortingngToSplit)-@pos) END INSERT INTO @returnList SELECT @ssortingngToSplit RETURN END 

et pour l’utiliser: –

 SELECT * FROM dbo.splitssortingng('91,12,65,78,56,789') 

Au lieu des CCE récursifs et des boucles while, quelqu’un a-t-il envisagé une approche plus basée sur les ensembles?

 CREATE FUNCTION [dbo].[SplitSsortingng] ( @List NVARCHAR(MAX), @Delim VARCHAR(255) ) RETURNS TABLE AS RETURN ( SELECT [Value] FROM ( SELECT [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number], CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number]))) FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name) FROM sys.all_objects) AS x WHERE Number <= LEN(@List) AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim ) AS y ); 

Plus d'informations sur les fonctions split, pourquoi (et la preuve que) alors que les boucles et les CTE récursifs ne sont pas mis à l'échelle, et de meilleures alternatives, si vous séparez des chaînes provenant de la couche application:

http://www.sqlperformance.com/2012/07/t-sql-queries/split-ssortingngs

http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-ssortingngs-now-with-less-t-sql

https://sqlblog.org/2010/07/07/splitting-a-list-of-integers-another-roundup

Enfin, l’attente est terminée dans SQL Server 2016, ils ont introduit la fonction Chaîne STRING_SPLIT : STRING_SPLIT

 select * From STRING_SPLIT ('a,b', ',') cs 

Toutes les autres méthodes pour diviser une chaîne comme XML, une table Tally, une boucle while, etc. ont été STRING_SPLIT par cette fonction STRING_SPLIT .

Voici un excellent article avec comparaison des performances: Sursockets et hypothèses de performance: STRING_SPLIT

La méthode la plus simple consiste à utiliser le format XML .

1. Conversion d’une chaîne en lignes sans table

QUESTION

 DECLARE @Ssortingng varchar(100) = 'Ssortingng1,Ssortingng2,Ssortingng3' -- To change ',' to any other delimeter, just change ',' to your desired one DECLARE @Delimiter CHAR = ',' SELECT LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value' FROM ( SELECT CAST ('' + REPLACE(@Ssortingng, @Delimiter, '') + '' AS XML) AS Data ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a) 

RÉSULTAT

  x---------x | Value | x---------x | Ssortingng1 | | Ssortingng2 | | Ssortingng3 | x---------x 

2. Conversion en lignes à partir d’une table qui possède un ID pour chaque ligne CSV

TABLEAU SOURCE

  x-----x--------------------------x | Id | Value | x-----x--------------------------x | 1 | Ssortingng1,Ssortingng2,Ssortingng3 | | 2 | Ssortingng4,Ssortingng5,Ssortingng6 | x-----x--------------------------x 

QUESTION

 -- To change ',' to any other delimeter, just change ',' before '' to your desired one DECLARE @Delimiter CHAR = ',' SELECT ID,LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value' FROM ( SELECT ID,CAST ('' + REPLACE(VALUE, @Delimiter, '') + '' AS XML) AS Data FROM TABLENAME ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a) 

RÉSULTAT

  x-----x----------x | Id | Value | x-----x----------x | 1 | Ssortingng1 | | 1 | Ssortingng2 | | 1 | Ssortingng3 | | 2 | Ssortingng4 | | 2 | Ssortingng5 | | 2 | Ssortingng6 | x-----x----------x 

J’avais besoin d’un moyen rapide pour se débarrasser du +4 d’un code postal .

 UPDATE #Emails SET ZIPCode = SUBSTRING(ZIPCode, 1, (CHARINDEX('-', ZIPCODE)-1)) WHERE ZIPCode LIKE '%-%' 

Pas de proc … pas de UDF … juste une petite commande en ligne qui fait ce qu’il faut. Pas luxueux, pas élégant.

Changez le délimiteur selon vos besoins, etc., et cela fonctionnera pour tout.

si vous remplacez

 WHILE CHARINDEX(',', @ssortingngToSplit) > 0 

avec

 WHILE LEN(@ssortingngToSplit) > 0 

vous pouvez éliminer ce dernier insert après la boucle while!

 CREATE FUNCTION dbo.splitssortingng ( @ssortingngToSplit VARCHAR(MAX) ) RETURNS @returnList TABLE ([Name] [nvarchar] (500)) AS BEGIN DECLARE @name NVARCHAR(255) DECLARE @pos INT WHILE LEN(@ssortingngToSplit) > 0 BEGIN SELECT @pos = CHARINDEX(',', @ssortingngToSplit) if @pos = 0 SELECT @pos = LEN(@ssortingngToSplit) SELECT @name = SUBSTRING(@ssortingngToSplit, 1, @pos-1) INSERT INTO @returnList SELECT @name SELECT @ssortingngToSplit = SUBSTRING(@ssortingngToSplit, @pos+1, LEN(@ssortingngToSplit)-@pos) END RETURN END 

Toutes les fonctions de fractionnement de chaînes qui utilisent un type de boucle (itérations) ont de mauvaises performances. Ils doivent être remplacés par une solution basée sur les ensembles.

Ce code s’exécute excellent.

 CREATE FUNCTION dbo.SplitSsortingngs ( @List NVARCHAR(MAX), @Delimiter NVARCHAR(255) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT Item = yivalue('(./text())[1]', 'nvarchar(4000)') FROM ( SELECT x = CONVERT(XML, '' + REPLACE(@List, @Delimiter, '') + '').query('.') ) AS a CROSS APPLY x.nodes('i') AS y(i) ); GO 

J’ai dû écrire quelque chose comme ça récemment. Voici la solution que j’ai trouvée. Il est généralisé pour toute chaîne de délimiteur et je pense qu’il fonctionnerait un peu mieux:

 CREATE FUNCTION [dbo].[SplitSsortingng] ( @ssortingng nvarchar(4000) , @delim nvarchar(100) ) RETURNS @result TABLE ( [Value] nvarchar(4000) NOT NULL , [Index] int NOT NULL ) AS BEGIN DECLARE @str nvarchar(4000) , @pos int , @prv int = 1 SELECT @pos = CHARINDEX(@delim, @ssortingng) WHILE @pos > 0 BEGIN SELECT @str = SUBSTRING(@ssortingng, @prv, @pos - @prv) INSERT INTO @result SELECT @str, @prv SELECT @prv = @pos + LEN(@delim) , @pos = CHARINDEX(@delim, @ssortingng, @pos + 1) END INSERT INTO @result SELECT SUBSTRING(@ssortingng, @prv, 4000), @prv RETURN END 

Une solution utilisant un CTE, si quelqu’un devait en avoir besoin (à part moi, qui l’a fait évidemment, c’est pourquoi je l’ai écrit).

 declare @SsortingngToSplit varchar(100) = 'Test1,Test2,Test3'; declare @SplitChar varchar(10) = ','; with SsortingngToSplit as ( select lsortingm( rsortingm( subssortingng( @SsortingngToSplit, 1, charindex( @SplitChar, @SsortingngToSplit ) - 1 ) ) ) Head , subssortingng( @SsortingngToSplit, charindex( @SplitChar, @SsortingngToSplit ) + 1, len( @SsortingngToSplit ) ) Tail union all select lsortingm( rsortingm( subssortingng( Tail, 1, charindex( @SplitChar, Tail ) - 1 ) ) ) Head , subssortingng( Tail, charindex( @SplitChar, Tail ) + 1, len( Tail ) ) Tail from SsortingngToSplit where charindex( @SplitChar, Tail ) > 0 union all select lsortingm( rsortingm( Tail ) ) Head , '' Tail from SsortingngToSplit where charindex( @SplitChar, Tail ) = 0 and len( Tail ) > 0 ) select Head from SsortingngToSplit 

Il y a une version correcte ici mais j’ai pensé qu’il serait bon d’append un peu de tolérance aux pannes au cas où ils auraient une virgule à la fin et de la rendre utilisable non pas comme une fonction mais comme une partie de code plus grande . Juste au cas où vous ne l’utilisez qu’une fois et que vous n’avez pas besoin d’une fonction. Ceci est également pour les entiers (ce qui était ce dont j’avais besoin), vous devrez peut-être modifier vos types de données.

 DECLARE @SsortingngToSeperate VARCHAR(10) SET @SsortingngToSeperate = '1,2,5' --SELECT @SsortingngToSeperate IDs INTO #Test DROP TABLE #IDs CREATE TABLE #IDs (ID int) DECLARE @CommaSeperatedValue NVARCHAR(255) = '' DECLARE @Position INT = LEN(@SsortingngToSeperate) --Add Each Value WHILE CHARINDEX(',', @SsortingngToSeperate) > 0 BEGIN SELECT @Position = CHARINDEX(',', @SsortingngToSeperate) SELECT @CommaSeperatedValue = SUBSTRING(@SsortingngToSeperate, 1, @Position-1) INSERT INTO #IDs SELECT @CommaSeperatedValue SELECT @SsortingngToSeperate = SUBSTRING(@SsortingngToSeperate, @Position+1, LEN(@SsortingngToSeperate)-@Position) END --Add Last Value IF (LEN(LTRIM(RTRIM(@SsortingngToSeperate)))>0) BEGIN INSERT INTO #IDs SELECT SUBSTRING(@SsortingngToSeperate, 1, @Position) END SELECT * FROM #IDs 

J’ai modifié un peu la fonction d’Andy Robinson. Maintenant, vous ne pouvez sélectionner que la partie requirejse de la table de retour:

 CREATE FUNCTION dbo.splitssortingng ( @ssortingngToSplit VARCHAR(MAX) ) RETURNS @returnList TABLE ([numOrder] [tinyint] , [Name] [nvarchar] (500)) AS BEGIN DECLARE @name NVARCHAR(255) DECLARE @pos INT DECLARE @orderNum INT SET @orderNum=0 WHILE CHARINDEX('.', @ssortingngToSplit) > 0 BEGIN SELECT @orderNum=@orderNum+1; SELECT @pos = CHARINDEX('.', @ssortingngToSplit) SELECT @name = SUBSTRING(@ssortingngToSplit, 1, @pos-1) INSERT INTO @returnList SELECT @orderNum,@name SELECT @ssortingngToSplit = SUBSTRING(@ssortingngToSplit, @pos+1, LEN(@ssortingngToSplit)-@pos) END SELECT @orderNum=@orderNum+1; INSERT INTO @returnList SELECT @orderNum, @ssortingngToSplit RETURN END Usage: 

SELECT Name FROM dbo.splitssortingng('ELIS.YD.CRP1.1.CBA.MDSP.T389.BT') WHERE numOrder=5

Voici une version qui peut être divisée sur un modèle en utilisant patindex, une simple adaptation du post ci-dessus. J’ai eu un cas où je devais diviser une chaîne contenant plusieurs caractères séparateurs.

 alter FUNCTION dbo.splitssortingng ( @ssortingngToSplit VARCHAR(1000), @splitPattern varchar(10) ) RETURNS @returnList TABLE ([Name] [nvarchar] (500)) AS BEGIN DECLARE @name NVARCHAR(255) DECLARE @pos INT WHILE PATINDEX(@splitPattern, @ssortingngToSplit) > 0 BEGIN SELECT @pos = PATINDEX(@splitPattern, @ssortingngToSplit) SELECT @name = SUBSTRING(@ssortingngToSplit, 1, @pos-1) INSERT INTO @returnList SELECT @name SELECT @ssortingngToSplit = SUBSTRING(@ssortingngToSplit, @pos+1, LEN(@ssortingngToSplit)-@pos) END INSERT INTO @returnList SELECT @ssortingngToSplit RETURN END select * from dbo.splitssortingng('ssortingnga/ssortingngb/x,y,z','%[/,]%'); 

résultat ressemble à ceci

ssortingnga ssortingngb x y z

Personnellement j’utilise cette fonction:

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

J’ai développé un double splitter (prend deux caractères séparés) comme demandé ici . Pourrait avoir une certaine valeur dans ce thread en voyant le plus référencé pour les requêtes relatives au fractionnement de chaînes.

 CREATE FUNCTION uft_DoubleSplitter ( -- Add the parameters for the function here @Ssortingng VARCHAR(4000), @Splitter1 CHAR, @Splitter2 CHAR ) RETURNS @Result TABLE (Id INT,MId INT,SValue VARCHAR(4000)) AS BEGIN DECLARE @FResult TABLE(Id INT IDENTITY(1, 1), SValue VARCHAR(4000)) DECLARE @SResult TABLE(Id INT IDENTITY(1, 1), MId INT, SValue VARCHAR(4000)) SET @Ssortingng = @Ssortingng+@Splitter1 WHILE CHARINDEX(@Splitter1, @Ssortingng) > 0 BEGIN DECLARE @WorkingSsortingng VARCHAR(4000) = NULL SET @WorkingSsortingng = SUBSTRING(@Ssortingng, 1, CHARINDEX(@Splitter1, @Ssortingng) - 1) --Print @workingSsortingng INSERT INTO @FResult SELECT CASE WHEN @WorkingSsortingng = '' THEN NULL ELSE @WorkingSsortingng END SET @Ssortingng = SUBSTRING(@Ssortingng, LEN(@WorkingSsortingng) + 2, LEN(@Ssortingng)) END IF ISNULL(@Splitter2, '') != '' BEGIN DECLARE @OStartLoop INT DECLARE @OEndLoop INT SELECT @OStartLoop = MIN(Id), @OEndLoop = MAX(Id) FROM @FResult WHILE @OStartLoop <= @OEndLoop BEGIN DECLARE @iString VARCHAR(4000) DECLARE @iMId INT SELECT @iString = SValue+@Splitter2, @iMId = Id FROM @FResult WHERE Id = @OStartLoop WHILE CHARINDEX(@Splitter2, @iString) > 0 BEGIN DECLARE @iWorkingSsortingng VARCHAR(4000) = NULL SET @IWorkingSsortingng = SUBSTRING(@iSsortingng, 1, CHARINDEX(@Splitter2, @iSsortingng) - 1) INSERT INTO @SResult SELECT @iMId, CASE WHEN @iWorkingSsortingng = '' THEN NULL ELSE @iWorkingSsortingng END SET @iSsortingng = SUBSTRING(@iSsortingng, LEN(@iWorkingSsortingng) + 2, LEN(@iSsortingng)) END SET @OStartLoop = @OStartLoop + 1 END INSERT INTO @Result SELECT MId AS PrimarySplitID, ROW_NUMBER() OVER (PARTITION BY MId ORDER BY Mid, Id) AS SecondarySplitID , SValue FROM @SResult END ELSE BEGIN INSERT INTO @Result SELECT Id AS PrimarySplitID, NULL AS SecondarySplitID, SValue FROM @FResult END RETURN 

Usage:

 --FirstSplit SELECT * FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===','&',NULL) --Second Split SELECT * FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===','&','=') 

Utilisation possible (Récupère la deuxième valeur de chaque division):

 SELECT fn.SValue FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===', '&', '=')AS fn WHERE fn.mid = 2 

L’approche souvent utilisée avec les éléments XML se casse en cas de caractères interdits. Ceci est une approche pour utiliser cette méthode avec n’importe quel type de caractère, même avec le point-virgule comme délimiteur.

L’astuce consiste à utiliser d’abord SELECT SomeSsortingng AS [*] FOR XML PATH('') pour que tous les caractères interdits soient correctement échappés. C’est la raison pour laquelle je remplace le délimiteur par une valeur magique pour éviter les problèmes avec ; comme délimiteur.

 DECLARE @Dummy TABLE (ID INT, SomeTextToSplit NVARCHAR(MAX)) INSERT INTO @Dummy VALUES (1,N'A&B;C;D;E, F') ,(2,N'"C" & ''D'';;D;E, F'); DECLARE @Delimiter NVARCHAR(10)=';'; --special effort needed (due to entities coding with "&code;")! WITH Casted AS ( SELECT * ,CAST(N'' + REPLACE((SELECT REPLACE(SomeTextToSplit,@Delimiter,N'§§Split$me$here§§') AS [*] FOR XML PATH('')),N'§§Split$me$here§§',N'') + N'' AS XML) AS SplitMe FROM @Dummy ) SELECT Casted.ID ,x.value(N'.',N'nvarchar(max)') AS Part FROM Casted CROSS APPLY SplitMe.nodes(N'/x') AS A(x) 

Le résultat

 ID Part 1 A&B 1 C 1 D 1 E, F 2 "C" & 'D' 2  2 D 2 E, F 

Si vous avez besoin d’une solution ad hoc rapide pour les cas courants avec un code minimum, cette solution à deux lignes récursives le fera:

 DECLARE @s VARCHAR(200) = ',1,2,,3,,,4,,,,5,' ;WITH a AS (SELECT i=-1, j=0 UNION ALL SELECT j, CHARINDEX(',', @s, j + 1) FROM a WHERE j > i), b AS (SELECT SUBSTRING(@s, i+1, IIF(j>0, j, LEN(@s)+1)-i-1) s FROM a WHERE i >= 0) SELECT * FROM b 

Vous pouvez soit l’utiliser comme une déclaration autonome, soit simplement append les CTE ci-dessus à l’une de vos requêtes et vous pourrez joindre la table résultante b avec d’autres pour l’utiliser dans d’autres expressions.

edit (par Shnugo)

Si vous ajoutez un compteur, vous obtiendrez un index de position avec la liste:

 DECLARE @s VARCHAR(200) = '1,2333,344,4' ;WITH a AS (SELECT n=0, i=-1, j=0 UNION ALL SELECT n+1, j, CHARINDEX(',', @s, j+1) FROM a WHERE j > i), b AS (SELECT n, SUBSTRING(@s, i+1, IIF(j>0, j, LEN(@s)+1)-i-1) s FROM a WHERE i >= 0) SELECT * FROM b; 

Le résultat

 ns 1 1 2 2333 3 344 4 4 

Ceci est plus étroitement adapté. Quand je fais cela, j’ai généralement une liste délimitée par des virgules d’ID uniques (INT ou BIGINT), que je veux convertir en une table à utiliser comme jointure interne à une autre table ayant une clé primaire de INT ou BIGINT. Je veux une fonction de table en ligne renvoyée pour que la jointure soit la plus efficace possible.

Exemple d’utilisation serait:

  DECLARE @IDs VARCHAR(1000); SET @IDs = ',99,206,124,8967,1,7,3,45234,2,889,987979,'; SELECT me.Value FROM dbo.MyEnum me INNER JOIN dbo.GetIntIdsTableFromDelimitedSsortingng(@IDs) ids ON me.PrimaryKey = ids.ID 

J’ai volé l’idée de http://sqlrecords.blogspot.com/2012/11/converting-delimited-list-to-table.html , en la transformant en valeur de table en ligne et en la convertissant en INT.

 create function dbo.GetIntIDTableFromDelimitedSsortingng ( @IDs VARCHAR(1000) --this parameter must start and end with a comma, eg ',123,456,' --all items in list must be perfectly formatted or function will error ) RETURNS TABLE AS RETURN SELECT CAST(SUBSTRING(@IDs,Nums.number + 1,CHARINDEX(',',@IDs,(Nums.number+2)) - Nums.number - 1) AS INT) AS ID FROM [master].[dbo].[spt_values] Nums WHERE Nums.Type = 'P' AND Nums.number BETWEEN 1 AND DATALENGTH(@IDs) AND SUBSTRING(@IDs,Nums.number,1) = ',' AND CHARINDEX(',',@IDs,(Nums.number+1)) > Nums.number; GO 
 ALTER FUNCTION [dbo].func_split_ssortingng ( @input as varchar(max), @delimiter as varchar(10) = ";" ) RETURNS @result TABLE ( id smallint identity(1,1), csv_value varchar(max) not null ) AS BEGIN DECLARE @pos AS INT; DECLARE @ssortingng AS VARCHAR(MAX) = ''; WHILE LEN(@input) > 0 BEGIN SELECT @pos = CHARINDEX(@delimiter,@input); IF(@pos<=0) select @pos = len(@input) IF(@pos <> LEN(@input)) SELECT @ssortingng = SUBSTRING(@input, 1, @pos-1); ELSE SELECT @ssortingng = SUBSTRING(@input, 1, @pos); INSERT INTO @result SELECT @ssortingng SELECT @input = SUBSTRING(@input, @pos+len(@delimiter), LEN(@input)-@pos) END RETURN END 

Vous pouvez utiliser cette fonction:

  CREATE FUNCTION SplitSsortingng ( @Input NVARCHAR(MAX), @Character CHAR(1) ) RETURNS @Output TABLE ( Item NVARCHAR(1000) ) AS BEGIN DECLARE @StartIndex INT, @EndIndex INT SET @StartIndex = 1 IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character BEGIN SET @Input = @Input + @Character END WHILE CHARINDEX(@Character, @Input) > 0 BEGIN SET @EndIndex = CHARINDEX(@Character, @Input) INSERT INTO @Output(Item) SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1) SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input)) END RETURN END GO 

Voici un exemple que vous pouvez utiliser comme fonction ou vous pouvez également mettre la même logique dans la procédure. –SELECT * de [dbo] .fn_SplitSsortingng;

 CREATE FUNCTION [dbo].[fn_SplitSsortingng] (@CSV VARCHAR(MAX), @Delimeter VARCHAR(100) = ',') RETURNS @retTable TABLE ( [value] VARCHAR(MAX) NULL )AS BEGIN DECLARE @vCSV VARCHAR (MAX) = @CSV, @vDelimeter VARCHAR (100) = @Delimeter; IF @vDelimeter = ';' BEGIN SET @vCSV = REPLACE(@vCSV, ';', '~!~#~'); SET @vDelimeter = REPLACE(@vDelimeter, ';', '~!~#~'); END; SET @vCSV = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@vCSV, '&', '&'), '<', '<'), '>', '>'), '''', '''), '"', '"'); DECLARE @xml XML; SET @xml = '' + REPLACE(@vCSV, @vDelimeter, '') + ''; INSERT INTO @retTable SELECT xivalue('.', 'varchar(max)') AS COLUMNNAME FROM @xml.nodes('//i')AS x(i); RETURN; END; 

/ *

Réponse à la chaîne fractionnée T-SQL
Basé sur les réponses d’ Andy Robinson et d’ AviG
Fonctionnalités améliorées ref: fonction LEN n’incluant pas les espaces de fin dans SQL Server
Ce fichier devrait être valide à la fois comme fichier de démarquage et comme fichier SQL

“ `

* /

 CREATE FUNCTION dbo.splitssortingng ( --CREATE OR ALTER @ssortingngToSplit NVARCHAR(MAX) ) RETURNS @returnList TABLE ([Item] NVARCHAR (MAX)) AS BEGIN DECLARE @name NVARCHAR(MAX) DECLARE @pos BIGINT SET @ssortingngToSplit = @ssortingngToSplit + ',' -- this should allow ensortinges that end with a `,` to have a blank value in that "column" WHILE ((LEN(@ssortingngToSplit+'_') > 1)) BEGIN -- `+'_'` gets around LEN sortingmming terminal spaces. See URL referenced above SET @pos = COALESCE(NULLIF(CHARINDEX(',', @ssortingngToSplit),0),LEN(@ssortingngToSplit+'_')) -- COALESCE grabs first non-null value SET @name = SUBSTRING(@ssortingngToSplit, 1, @pos-1) --MAX size of ssortingng of type nvarchar is 4000 SET @ssortingngToSplit = SUBSTRING(@ssortingngToSplit, @pos+1, 4000) -- With SUBSTRING fn (MS web): "If start is greater than the number of characters in the value expression, a zero-length expression is returned." INSERT INTO @returnList SELECT @name --additional debugging parameters below can be added -- + ' pos:' + CAST(@pos as nvarchar) + ' remain:''' + @ssortingngToSplit + '''(' + CAST(LEN(@ssortingngToSplit+'_')-1 as nvarchar) + ')' END RETURN END GO 

/ *

“ `

Cas de test: voir l’URL référencée comme “fonctionnalité améliorée” ci-dessus

SELECT *,LEN(Item+'_')-1 'L' from splitssortingng('a,,b')

 Item | L --- | --- a | 1 | 0 b | 1 

SELECT *,LEN(Item+'_')-1 'L' from splitssortingng('a,,')

 Item | L --- | --- a | 1 | 0 | 0 

SELECT *,LEN(Item+'_')-1 'L' from splitssortingng('a,, ')

 Item | L --- | --- a | 1 | 0 | 1 

SELECT *,LEN(Item+'_')-1 'L' from splitssortingng('a,, c ')

 Item | L --- | --- a | 1 | 0 c | 3 

* /

Une solution récursive basée sur cte

 declare @T table (iden int identity, col1 varchar(100)); insert into @T(col1) values ('ROOT/South America/Lima/Test/Test2') , ('ROOT/South America/Peru/Test/Test2') , ('ROOT//South America/Venuzuala ') , ('RtT/South America / ') , ('ROOT/South Americas// '); declare @split char(1) = '/'; select @split as split; with cte as ( select t.iden, case when SUBSTRING(REVERSE(rsortingm(t.col1)), 1, 1) = @split then LTRIM(RTRIM(t.col1)) else LTRIM(RTRIM(t.col1)) + @split end as col1, 0 as pos , 1 as cnt from @T t union all select t.iden, t.col1 , charindex(@split, t.col1, t.pos + 1), cnt + 1 from cte t where charindex(@split, t.col1, t.pos + 1) > 0 ) select t1.*, t2.pos, t2.cnt , lsortingm(rsortingm(SUBSTRING(t1.col1, t1.pos+1, t2.pos-t1.pos-1))) as bingo from cte t1 join cte t2 on t2.iden = t1.iden and t2.cnt = t1.cnt+1 and t2.pos > t1.pos order by t1.iden, t1.cnt; 

Avec tout le respect dû à @AviG, il s’agit de la version gratuite de la fonction conçue par lui pour renvoyer tous les jetons en entier.

 IF EXISTS (SELECT * FROM sys.objects WHERE type = 'TF' AND name = 'TF_SplitSsortingng') DROP FUNCTION [dbo].[TF_SplitSsortingng] GO -- ============================================= -- Author: AviG -- Amendments: Parameterize the delimeter and included the missing chars in last token - Gemunu Wickremasinghe -- Description: Tabel valued function that Breaks the delimeted ssortingng by given delimeter and returns a tabel having split results -- Usage -- select * from [dbo].[TF_SplitSsortingng]('token1,token2,,,,,,,,token969',',') -- 969 items should be returned -- select * from [dbo].[TF_SplitSsortingng]('4672978261,4672978255',',') -- 2 items should be returned -- ============================================= CREATE FUNCTION dbo.TF_SplitSsortingng ( @ssortingngToSplit VARCHAR(MAX) , @delimeter char = ',' ) RETURNS @returnList TABLE ([Name] [nvarchar] (500)) AS BEGIN DECLARE @name NVARCHAR(255) DECLARE @pos INT WHILE LEN(@ssortingngToSplit) > 0 BEGIN SELECT @pos = CHARINDEX(@delimeter, @ssortingngToSplit) if @pos = 0 BEGIN SELECT @pos = LEN(@ssortingngToSplit) SELECT @name = SUBSTRING(@ssortingngToSplit, 1, @pos) END else BEGIN SELECT @name = SUBSTRING(@ssortingngToSplit, 1, @pos-1) END INSERT INTO @returnList SELECT @name SELECT @ssortingngToSplit = SUBSTRING(@ssortingngToSplit, @pos+1, LEN(@ssortingngToSplit)-@pos) END RETURN END 

Le moyen le plus simple:

  1. Installer SQL Server 2016
  2. Utilisez STRING_SPLIT https://msdn.microsoft.com/en-us/library/mt684588.aspx

Cela fonctionne même en édition express :).