T-SQL scinde une chaîne

Au lieu de récursive d'expressions de table communes et les boucles while, a toute personne considérée comme une série de plus, l'approche basée?

CREATE FUNCTION [dbo].[SplitString]
    (
        @List nvarchar(MAX),
        @Delim nvarchar(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_columns) AS x
              WHERE Number <= LEN(@List)
              AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
          ) AS y
        );

Si vous voulez éviter la limitation de la longueur de la chaîne en cours <= le nombre de lignes dans sys.all_columns (9,980 dans model dans SQL Server 2017; beaucoup plus élevés dans vos propres bases de données utilisateur), vous pouvez utiliser d'autres méthodes pour calculer les nombres, tels que la construction de votre propre tableau de nombres. Vous pouvez également utiliser une expression de table commune récursive dans le cas où vous ne pouvez pas utiliser les tables système ou créer votre propre:

CREATE FUNCTION [dbo].[SplitString]
(
    @List nvarchar(MAX),
    @Delim nvarchar(255)
)
RETURNS TABLE WITH SCHEMABINDING
AS
    RETURN ( WITH n(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM n 
      WHERE n < LEN(@List))
      SELECT [Value] FROM 
      ( 
        SELECT 
          [Value] = LTRIM(RTRIM(SUBSTRING(@List, n,
          CHARINDEX(@Delim, @List + @Delim, n) - n)))
        FROM n
        WHERE SUBSTRING(@Delim + @List, n, LEN(@Delim)) = @Delim
      ) AS y
    );

Mais vous aurez à ajouter OPTION (MAXRECURSION 0) (ou MAXRECURSION <longest possible string length if < 32768>) de la requête externe afin d'éviter les erreurs de la récursivité pour cordes > 100 caractères. Si c'est pas une bonne solution de rechange alors voir cette réponse comme l'a souligné dans les commentaires.

Plus sur les fonctions split, pourquoi (et la preuve) tandis que les boucles et les expressions cte n'est pas à l'échelle, et de meilleures alternatives, si le fractionnement des chaînes provenant de la couche application:

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

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

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

La meilleure façon de le faire est par l'utilisation de XML format.

1. La conversion de la chaîne de lignes sans table

REQUÊTE

DECLARE @String varchar(100) = 'String1,String2,String3'
-- 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 ('<M>' + REPLACE(@String, @Delimiter, '</M><M>') + '</M>' AS XML) AS Data            
) AS A 
CROSS APPLY Data.nodes ('/M') AS Split(a)

RÉSULTAT

 x---------x
 | Value   |
 x---------x
 | String1 |
 | String2 |
 | String3 |
 x---------x

2. La conversion de lignes d'une table qui ont un ID pour chaque CSV ligne

TABLE SOURCE

 x-----x--------------------------x
 | Id  |           Value          |
 x-----x--------------------------x
 |  1  |  String1,String2,String3 |
 |  2  |  String4,String5,String6 |     
 x-----x--------------------------x

REQUÊTE

-- To change ',' to any other delimeter, just change ',' before '</M><M>' to your desired one
DECLARE @Delimiter CHAR = ','

SELECT ID,LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value' 
FROM  
(     
     SELECT ID,CAST ('<M>' + REPLACE(VALUE, @Delimiter, '</M><M>') + '</M>' 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  |  String1 |
 |  1  |  String2 |  
 |  1  |  String3 |
 |  2  |  String4 |  
 |  2  |  String5 |
 |  2  |  String6 |     
 x-----x----------x

si vous remplacez

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

avec

WHILE LEN(@stringToSplit) > 0

vous pouvez éliminer ce dernier insérer, après la boucle while!

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE LEN(@stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)


if @pos = 0
        SELECT @pos = LEN(@stringToSplit)


  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 RETURN
END

Je devais écrire quelque chose comme cela récemment. Voici la solution que j'ai trouvé. Il est généralisée pour n'importe quel délimiteur de chaîne et je pense qu'il serait d'effectuer un peu mieux:

CREATE FUNCTION [dbo].[SplitString] 
    ( @string 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, @string)
    WHILE @pos > 0
    BEGIN
        SELECT @str = SUBSTRING(@string, @prv, @pos - @prv)
        INSERT INTO @result SELECT @str, @prv

        SELECT @prv = @pos + LEN(@delim)
             , @pos = CHARINDEX(@delim, @string, @pos + 1)
    END

    INSERT INTO @result SELECT SUBSTRING(@string, @prv, 4000), @prv
    RETURN
END

Il y a une version correcte ici, mais j'ai pensé qu'il serait bien d'ajouter un peu de tolérance aux pannes dans le cas où ils ont une virgule ainsi que de faire en sorte que vous pourriez utiliser, non comme une fonction, mais comme faisant partie d'un grand morceau de code. Juste au cas où vous êtes seulement en utilisant une fois de temps et n'avez pas besoin d'une fonction. C'est aussi pour les entiers (ce dont j'ai besoin) de sorte que vous pourriez avoir à changer vos types de données.

DECLARE @StringToSeperate VARCHAR(10)
SET @StringToSeperate = '1,2,5'

--SELECT @StringToSeperate IDs INTO #Test

DROP TABLE #IDs
CREATE TABLE #IDs (ID int) 

DECLARE @CommaSeperatedValue NVARCHAR(255) = ''
DECLARE @Position INT = LEN(@StringToSeperate)

--Add Each Value
WHILE CHARINDEX(',', @StringToSeperate) > 0
BEGIN
    SELECT @Position  = CHARINDEX(',', @StringToSeperate)  
    SELECT @CommaSeperatedValue = SUBSTRING(@StringToSeperate, 1, @Position-1)

    INSERT INTO #IDs 
    SELECT @CommaSeperatedValue

    SELECT @StringToSeperate = SUBSTRING(@StringToSeperate, @Position+1, LEN(@StringToSeperate)-@Position)

END

--Add Last Value
IF (LEN(LTRIM(RTRIM(@StringToSeperate)))>0)
BEGIN
    INSERT INTO #IDs
    SELECT SUBSTRING(@StringToSeperate, 1, @Position)
END

SELECT * FROM #IDs

Si vous avez besoin d'un rapide ad-hoc de la solution pour la plupart des cas, avec un minimum de code, alors cette expression de table commune récursive deux-liner doit le faire:

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

Soit utiliser comme un stand-alone déclaration ou simplement ajouter le au-dessus d'expressions de table communes à toutes vos requêtes et vous serez en mesure de rejoindre la table résultante b avec les autres pour une utilisation dans toutes les autres expressions.

modifier (par Shnugo)

Si vous ajoutez un compteur, vous obtiendrez un index de position ainsi que 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:

n   s
1   1
2   2333
3   344
4   4

Personnellement, j'utilise cette fonction :

ALTER FUNCTION [dbo].[CUST_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
)

Souvent utilisé avec les éléments XML les pauses dans le cas des caractères interdits. C'est une approche de l'utilisation de cette méthode avec tout type de personnage, même avec le point-virgule comme séparateur.

Le truc, c'est, d'abord à utiliser SELECT SomeString AS [*] FOR XML PATH('') pour obtenir tous les caractères interdits échappées. C'est la raison pour laquelle j'ai remplacer le délimiteur à un valeur magique pour éviter les ennuis 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'';<C>;D;E, F');
DECLARE @Delimiter NVARCHAR(10)=';'; --special effort needed (due to entities coding with "&code;")!
WITH Casted AS
(
SELECT *
,CAST(N'<x>' + REPLACE((SELECT REPLACE(SomeTextToSplit,@Delimiter,N'§§Split$me$here§§') AS [*] FOR XML PATH('')),N'§§Split$me$here§§',N'</x><x>') + N'</x>' 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   <C>
2   D
2   E, F

ALTER FUNCTION [dbo].func_split_string
(
@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 @string 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 @string = SUBSTRING(@input, 1, @pos-1);
ELSE
SELECT @string = SUBSTRING(@input, 1, @pos);
INSERT INTO @result SELECT @string
SELECT @input = SUBSTRING(@input, @pos+len(@delimiter), LEN(@input)-@pos)       
END
RETURN  
END

Voici un exemple que vous pouvez utiliser en tant que fonction ou vous aussi, vous pouvez mettre la même logique dans la procédure.
--SELECT * from [dbo].fn_SplitString ;

CREATE FUNCTION [dbo].[fn_SplitString]
(@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, '&', '&amp;'), '<', '&lt;'), '>', '&gt;'), '''', '&apos;'), '"', '&quot;');
DECLARE @xml XML;
SET @xml = '<i>' + REPLACE(@vCSV, @vDelimeter, '</i><i>') + '</i>';
INSERT INTO @retTable
SELECT
x.i.value('.', 'varchar(max)') AS COLUMNNAME
FROM @xml.nodes('//i')AS x(i);
RETURN;
END;

Une expression de table commune récursive solution basée sur

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(rtrim(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
, ltrim(rtrim(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;

Ceci est basé sur Andy Robertson réponse, j'avais besoin d'un séparateur d'autres que par des virgules.

CREATE FUNCTION dbo.splitstring ( @stringToSplit nvarchar(MAX), @delim nvarchar(max))
RETURNS
@returnList TABLE ([value] [nvarchar] (MAX))
AS
BEGIN
DECLARE @value NVARCHAR(max)
DECLARE @pos INT
WHILE CHARINDEX(@delim, @stringToSplit) > 0
BEGIN
SELECT @pos  = CHARINDEX(@delim, @stringToSplit)  
SELECT @value = SUBSTRING(@stringToSplit, 1, @pos - 1)
INSERT INTO @returnList 
SELECT @value
SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos + LEN(@delim), LEN(@stringToSplit) - @pos)
END
INSERT INTO @returnList
SELECT @stringToSplit
RETURN
END
GO

Et de l'utiliser:

SELECT * FROM dbo.splitstring('test1 test2 test3', ' ');

(Testé sur SQL Server 2008 R2)

EDIT: correction d'un code de test