Eu tenho o seguinteSplit função,

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

Quando escrevo

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

Isso vai me dar

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

Eu preciso manter o pedido.