Create FUNCTION [dbo].[SplitToTable]
(
    @SplitString nvarchar(max),
    @Separator nvarchar(10)=' '
)
RETURNS @SplitStringsTable TABLE
(
	[id] int identity(1,1),
	[value] nvarchar(max)
)
AS
BEGIN
    DECLARE @CurrentIndex int;
	DECLARE @NextIndex int;
	DECLARE @ReturnText nvarchar(max);
	SELECT @CurrentIndex=1;
	WHILE(@CurrentIndex<=len(@SplitString))
	 BEGIN
		 SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
		 IF(@NextIndex=0 OR @NextIndex IS NULL)
			 SELECT @NextIndex=len(@SplitString)+1;
			 SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
			 INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText);
			 SELECT @CurrentIndex=@NextIndex+1;
	 END
	RETURN;
END

select * FROm dbo.SplitToTable('111,b2222,323232,32d,e,323232f,g3222', ',')
--或者select * FROm dbo.SplitToTable('我 是 中国 人', default)

结果为

id          value
----------- ---------------------------------------
1           111
2           b2222
3           323232
4           32d
5           e
6           323232f
7           g3222

(7 行受影响)

Create function [dbo].[GetSplitLength]
(
	@String nvarchar(max),  --要分割的字符串
	@Split nvarchar(10)  --分隔符号
)
returns int
as
begin
	declare @location int
	declare @start int
	declare @length int
 
	set @String=ltrim(rtrim(@String))
	set @location=charindex(@split,@String)
	set @length=1
	while @location<>0
	begin
		set @start=@location+1
		set @location=charindex(@split,@String,@start)
		set @length=@length+1
	end
	return @length
end
go

select dbo.GetSplitLength('111,b2222,323232,32d,e,323232f,g3222',',')

结果为7。

Create function [dbo].[GetSplitOfIndex]
(
	@String nvarchar(max),  --要分割的字符串
	@split nvarchar(10),  --分隔符号
	@index int --取第几个元素
)
returns nvarchar(1024)
as
begin
declare @location int
declare @start int
declare @next int
declare @seed int

set @String=ltrim(rtrim(@String))
set @start=1
set @next=1
set @seed=len(@split)
  
set @location=charindex(@split,@String)
while @location<>0 and @index>@next
begin
	set @start=@location+@seed
	set @location=charindex(@split,@String,@start)
	set @next=@next+1
end
	if @location =0 
		select @location =len(@String)+1 
	return substring(@String,@start,@location-@start)
end

select dbo.GetSplitOfIndex('111,b2222,323232,32d,e,323232f,g3222',',', 3)

结果323232。

DECLARE @Tags nvarchar(max);
SELECT @Tags='111,b2222,323232,32d,e,323232f,g3222';
DECLARE @Tag nvarchar(1000)
DECLARE @next int;
set @next=1
DECLARE @Length int;
SELECT @Length=dbo.GetSplitLength(@Tags,',')

while @next<=@Length
begin
	SET @Tag = left(dbo.GetSplitOfIndex(@Tags,',',@next), 16);
	print @Tag
	SET @Next=@Next+1;
END

111
b2222
323232
32d
e
323232f
g3222