1. 通过函数,将一条数据按照某个字符分割成多条
2.建立函数
Create FUNCTION [dbo].[fn_SplitTSQL]
(@String NVARCHAR(MAX), @Separator NCHAR(1) = N',') RETURNS TABLE
AS
RETURN
SELECT
-- N - LEN(REPLACE(LEFT(S, N), @Separator, '')) + 1 AS POS,
ROW_NUMBER() OVER (ORDER BY S) AS POS,
SUBSTRING(S, N, CHARINDEX(@Separator, S + @Separator, N) -N) AS ELEMENT
FROM ( SELECT @String AS S ) AS D
JOIN dbo.ZD_NUMS ON N <= LEN(S) AND SUBSTRING(@Separator + S, N, 1) = @Separator;
GO
3.例如 将一条数据‘1,2,3,4,5’ 用,分割成5行
select * from [dbo].[fn_SplitTSQL]('1,2,3,4,5',',')