sql拆分字符串 ‘,’ 并insert新表

DECLARE @bid INT
DECLARE @bstr NVARCHAR(255)
 
DECLARE CurB CURSOR FOR SELECT id,documentType FROM [dbo].[DocumentDb] --where id=41
 
OPEN CurB
 
FETCH NEXT FROM CurB INTO @bid,@bstr
WHILE @@FETCH_STATUS=0
 
BEGIN
 
 INSERT INTO dtb
 ( 
 [docid],
 [dtid]
 )
 SELECT @bid,word FROM [dbo].[udf_split](@bstr,',')
 
 FETCH NEXT FROM CurB INTO @bid,@bstr
END
CLOSE CurB
DEALLOCATE CurB
delete dtb where dtid is null
select * from dtb​

 

关键是  SELECT @bid,word FROM [dbo].[udf_split](@bstr,',')   

创建函数 udf_split  

ALTER FUNCTION [dbo].[udf_Split](@value NVARCHAR(MAX), @delimiter CHAR(1))
RETURNS @Results TABLE ([ID] INT IDENTITY(1,1),[WORD] NVARCHAR(MAX))
AS
BEGIN
 DECLARE @index INT
 DECLARE @slice NVARCHAR(MAX)
 
 SET @index = 1
 IF @value IS NULL 
 RETURN
 
 WHILE @index <> 0
 BEGIN
 SET @index = CHARINDEX(@delimiter, @value)
 IF @index !=0
 SET @slice = LEFT(@value,@index - 1)
 ELSE
 SET @slice = @value
 
 INSERT INTO @Results([WORD]) VALUES(@slice)
 SET @value = RIGHT(@value,LEN(@value) - @index)
 
 IF LEN(@value) = 0 BREAK
 END 
 RETURN
END​