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

Myth丶恋晨 2023-06-25 13:26 48阅读 0赞

效果如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N6eDIwMTg_size_16_color_FFFFFF_t_70]  
下面贴上存储过程的代码，大家可以自行复制过去创建存储过程

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N6eDIwMTg_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191229103941716.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N6eDIwMTg=,size_16,color_FFFFFF,t_70