SqlServer 中字符串 Split 的新用法

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 < 1000
begin
	insert	into @tb1(str)
	select	*
	from	dbo.f_splitStrByCharIndex(‘1,2,3,4,5‘)

	select @c1 = @c1 + 1 
end

select	@dt2 = getdate()

while @c2 < 1000
begin
	insert	into @tb2(str)
	SELECT	[value] from OPENJSON(‘[‘+‘1,2,3,4,5‘+‘]‘)

	select	@c2 = @c2 + 1 
end

select	@dt3 = getdate()

while @c3 < 1000
begin
	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 
end

select	@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 的方式。

SqlServer 中字符串 Split 的新用法

标签：http   inf   from   tst   value   fun   个人   var   介绍