SQL server中将传入的字符串按照特定的分隔符拆分转为一列

效果如下：

下面贴上存储过程的代码，大家可以自行复制过去创建存储过程

CREATE Function [dbo].[GetTableFromText](@ParaContext varchar(max),@splitChar char) returns @ReturnTable table(ResultColumn varchar(200))
as
Begin
 	Declare @startIndex int
	Declare @EndIndex	int
	declare @ParaStr varchar(max)
	declare @End int,@PosIndex	int , @varLength int 
	set @End = 1 
	Set @startIndex=0
	set @varLength = 1000 --字符串长度分隔成定长的来进行处理
	Set @PosIndex=0
	Declare @substr varchar(200)
	Declare @DataTextlen int
	Set @DataTextlen=datalength(@ParaContext)
	while(@End=1)
		begin
			if(@DataTextlen > @varLength)	
				begin				
					set @ParaStr = substring(@ParaContext,@varLength+1,@DataTextlen -@varLength)
					Set @PosIndex=charindex(@splitChar,@ParaStr)
					if(@PosIndex >0) 
						set @ParaStr = substring( @ParaContext,1,@varLength + @PosIndex) 
					else
						set @ParaStr = @ParaContext
					set @DataTextlen = @DataTextlen - @varLength - @PosIndex
					set @ParaContext = substring(@ParaContext,@varLength+@PosIndex+1,@DataTextlen)
					Set @startIndex=0	
				end 
			else
				begin
					select @ParaStr = @ParaContext,@End = 0
					Set @startIndex=0
				end
			
			Set @EndIndex= charindex(@splitChar,@ParaStr)
			While @startIndex<@EndIndex
				Begin
					
					Set @substr=SubString(@ParaStr,@startIndex+1,@EndIndex-@startIndex-1)
					
					insert into @ReturnTable values(@substr)
					Set @startIndex=@EndIndex
					Set @EndIndex= charindex(@splitChar,@ParaStr,@EndIndex+1)
				End
		end
		If @startIndex<@DataTextlen
			Begin
				Set @substr=SubString(@ParaStr,@startIndex+1,@DataTextlen-@startIndex)
				insert into @ReturnTable values(@substr)
			End
	return
End