sqlsever 文本中固定符号拆分成表

采用函数 

CREATE FUNCTION [dbo].[fn_split](@p_str NVARCHAR(MAX), @p_split VARCHAR(10))
RETURNS @tab TABLE(tid VARCHAR(2000))
AS
BEGIN
DECLARE @idx INT
DECLARE @len INT
SELECT @len = LEN(@p_split), @idx = CHARINDEX(@p_split, @p_str, 1)
WHILE(@idx >= 1)
BEGIN
INSERT INTO @tab SELECT LEFT(@p_str, @idx -1)
SELECT @p_str = RIGHT(@p_str, LEN(@p_str) - @idx - @len + 1), @idx = CHARINDEX(@p_split, @p_str, 1)
END

if(@p_str <> '') INSERT INTO @tab SELECT @p_str
RETURN

END

 

select * into #temp from dbo.fn_split('23,34,34,566,55', ',')

得到结果：

tid
23
34
34
566
55