SQL 拆分函数和排序问题? [英] SQL Split Function and Ordering Issue?

问题描述

我有以下Split函数，

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

当我写作时，

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

这会给我，

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

我需要维持秩序.

推荐答案

更简单的功能:

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

示例用法:

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

这篇关于SQL 拆分函数和排序问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！