SQL Split返回表

创建Function

Create FUNCTION [dbo].[SplitStr]
(@List nvarchar(max)
,@Delim varchar(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_objects) AS x
              WHERE Number <= LEN(@List)
              AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
          ) AS y
        );

调用

declare @str varchar(10) = 'A,B,C,D,E'
select [Value] from dbo.[SplitStr](@str, ',')
posted @ 2021-09-17 08:45  vvull  阅读(37)  评论(0编辑  收藏  举报