1. 网上介绍的文章对于旧版的 sql split 均是用  patindex 或 CHARINDEX 等函数来查找分隔字符来实现。

create function f_splitStrByCharIndex (@input varchar(100))returns @result table (str varchar(100)) as begin -- 1. 对于老的SQL数据库，基本上所有的网络上的文章采用的方式，都是类似于下面的用 patindex 或 charindex 函数 declare @pos int SELECT @pos = CHARINDEX(‘,‘,@input) while (@pos>0 and @pos<len(@input)) begin insert into @result(str) select substring(@input, 0, @pos) SELECT @input = substring(@input, @pos + 1, len(@input)) SELECT @pos = CHARINDEX(‘,‘,@input) end insert into @result(str) select @input returnend 

2. 对于新版本的sqlserver数据库，可以直接用 openjson

　　核心语句是：SELECT [value] from OPENJSON(‘[1,2,3,4,5]‘)

-- 2. json 方式
insert into @result (str)SELECT [value] from OPENJSON(‘[‘+@input+‘]‘)

3. 对于老版本的 sql server 其实也有其它办法：

 declare @sql nvarchar(max) select @sql = ‘select ‘‘‘ + replace(‘1,2,3,4,5‘, ‘,‘,‘‘‘ union all select ‘‘‘) + ‘‘‘‘ insert into @tb3(str) exec sys.sp_executesql @sql 

效率比较：

declare @dt1 datetime, @dt2 datetime, @dt3 datetime, @dt4 datetime declare @tb1 table (str varchar(100))declare @tb2 table (str varchar(100))declare @tb3 table (str varchar(100))declare @c1 int, @c2 int, @c3 int select @c1 = 0, @c2 = 0, @c3 = 0 select @dt1 = getdate()while @c1 < 1000begin insert into @tb1(str) select * from dbo.f_splitStrByCharIndex(‘1,2,3,4,5‘) select @c1 = @c1 + 1 endselect @dt2 = getdate()while @c2 < 1000begin insert into @tb2(str) SELECT [value] from OPENJSON(‘[‘+‘1,2,3,4,5‘+‘]‘) select @c2 = @c2 + 1 endselect @dt3 = getdate()while @c3 < 1000begin declare @sql nvarchar(max) select @sql = ‘select ‘‘‘ + replace(‘1,2,3,4,5‘, ‘,‘,‘‘‘ union all select ‘‘‘) + ‘‘‘‘ insert into @tb3(str) exec sys.sp_executesql @sql select @c3 = @c3 + 1 endselect @dt4 = getdate()select (select count(1) from @tb1), (select count(1) from @tb2), (select count(1) from @tb3), datediff(ms, @dt1, @dt2), datediff(ms, @dt2, @dt3), datediff(ms, @dt3, @dt4)

 个人强烈推荐升级数据库，使用 json 的方式。