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

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 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