SQL Split Function and Ordering Issue?

Question

I have the following Split function,

ALTER FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))     
                returns @temptable TABLE (items varchar(8000))     
            as     
            begin
                set @String = RTRIM(LTRIM(@String))
                declare @idx int     
                declare @slice varchar(8000)     

                select @idx = 1     
                    if len(@String)<1 or @String is null  return     

                while @idx!= 0     
                begin     
                    set @idx = charindex(@Delimiter,@String)     
                    if @idx!=0     
                        set @slice = left(@String,@idx - 1)     
                    else     
                        set @slice = @String     

                    if(len(@slice)>0)
                        insert into @temptable(Items) values(@slice)     

                    set @String = right(@String,len(@String) - @idx)     
                    if len(@String) = 0 break     
                end 
            return     
            end

When I write,

SELECT Items 
FROM Split('around the home,clean and protect,soaps and air fresheners,air fresheners',',')

This will give me,

air fresheners
around the home
clean and protect
soaps and air fresheners

I need to maintain the order.

Answer 1

This will be a much faster solution when your string has 1000 or more values to split. For table-valued functions, to have any ordering, you must apply "ORDER BY" at the place of use. This is because "SELECT" from a table without "ORDER BY" is by convention not having any sorting.

CREATE FUNCTION [dbo].[Split]
( 
    @String VARCHAR(max), 
    @Delimiter VARCHAR(max)
) 
RETURNS @Data TABLE 
(
    [Order] INT IDENTITY(1,1), 
    [Value] VARCHAR(max)
)
AS
BEGIN 
    DECLARE @x XML = cast('<i>' + replace(@String, @Delimiter, '</i><i>') + '</i>' AS XML)
    INSERT INTO @Data 
    SELECT v.value('.', 'varchar(max)') FROM @x.nodes('i') AS x(v)
    RETURN 
END
GO

Answer 2

Your function will need to set an order column (seq in this sample):

ALTER FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))     
            returns @temptable TABLE (seq int, items varchar(8000))     
        as     
        begin
            set @String = RTRIM(LTRIM(@String))
            declare @idx int     
            declare @seq int
            declare @slice varchar(8000)     

            set @seq=1

            select @idx = 1     
                if len(@String)<1 or @String is null  return     

            while @idx!= 0     
            begin     
                set @idx = charindex(@Delimiter,@String)     
                if @idx!=0     
                    set @slice = left(@String,@idx - 1)     
                else     
                    set @slice = @String     

                if(len(@slice)>0)
                begin
                    set @seq = @seq + 1
                    insert into @temptable(seq, Items) values(@seq,@slice)     
                end

                set @String = right(@String,len(@String) - @idx)     
                if len(@String) = 0 break     
            end 
        return     
        end
GO
SELECT * FROM Split('around the home,clean and protect,soaps and air fresheners,air fresheners',',') order by seq 

Answer 3

declare @Version nvarchar(3000)
declare @Delimiter char(1) = ','
declare @result nvarchar(3000)
set @Version = 'Terça-feira, Quarta-feira, Sexta-feira, Segunda-feira';

with V as (select value v, Row_Number() over (order by (select 0)) n 
    from String_Split(@Version, @Delimiter)
)
    SELECT @result = STUFF((SELECT ', ' + RTRIM(LTRIM(v))
      FROM V
      ORDER BY CASE RTRIM(LTRIM(v))
            WHEN 'Segunda-feira' then 1 
            WHEN 'Terça-feira' then 2
            WHEN 'Quarta-feira' then 3 
            WHEN 'Quinta-feira' then 4 
            WHEN 'Sexta-feira' then 5
           END FOR XML PATH('')), 1, LEN(@Delimiter), '')
PRINT @result

Answer 4

A simpler function:

CREATE FUNCTION dbo.SplitStrings_Ordered
(
    @List       nvarchar(MAX),
    @Delimiter  nvarchar(255)
)
RETURNS TABLE
AS
RETURN 
(
  SELECT [Index] = CHARINDEX(@Delimiter, @List + @Delimiter, Number),
         Item = SUBSTRING(@List, Number, CHARINDEX(@Delimiter, 
                @List + @Delimiter, Number) - Number)
    FROM 
    (
      SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.all_objects
    ) AS n(Number)
    WHERE Number <= CONVERT(INT, LEN(@List))
    AND SUBSTRING(@Delimiter + @List, Number, LEN(@Delimiter)) = @Delimiter
);
GO

Sample usage:

DECLARE @s nvarchar(MAX) = N',around the home,clean and protect,soaps and air'
  + ' fresheners,air fresheners';

SELECT Item FROM dbo.SplitStrings_Ordered(@s, N',') ORDER BY [Index];

Or to return orders from a table ordered by input:

SELECT o.OrderID
  FROM dbo.Orders AS o
  INNER JOIN dbo.SplitStrings_Ordered('123,789,456') AS f
  ON o.OrderID = CONVERT(int, f.Item)
  ORDER BY f.[Index];

Answer 5

If you can abide compatibility level 130 of SQL Server, you can use the String_Split() function.

With this and the Row_Number() function, you can return a table that includes the original sequence. For example:

declare @Version nvarchar(128)
set @Version = '1.2.3';

with V as (select value v, Row_Number() over (order by (select 0)) n 
    from String_Split(@Version, '.')
)
    select
        (select v from V where n = 1) Major,
        (select v from V where n = 2) Minor,
        (select v from V where n = 3) Revision

Note that Row_Number requires an ordering, but if you pass a literal value the results are in the parsed sequence. This isn't guaranteed to be the case with future SQL Server version, as according to the String_Split documentation, there is no official ordering. I doubt Microsoft will break this, at least before introducing a version of the function that returns the order as it should, but in the mean time you best not depend on this ordering when writing code that decides whether or not to launch the missile.

Returns:

Major Minor Revision
----- ----- --------
1     2     3