将逗号分隔字符串变为数据表代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】将逗号分隔字符串变为数据表

作者:京基 / 发布于2017/11/20/ 1096

declare @splitChar varchar(max)
declare @text varchar(max)
declare @textLen int
declare @lp int
declare @commaPos int
declare @remainText varchar(max)
declare @splitText varchar(max)
declare @splitTable table
(
value varchar(max)
)

set @text='1,2,6,8,102,103,104,106,107,108,109,110,111,112,113,115,201,202,203,204,205,206,207,208,209,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,301,302,303,304,305,306,307,309,310'
set @remainText=@text
set @textLen=LEN(@text)
set @commaPos=1
set @splitChar=','

while(@commaPos!=0)
begin
	print 'enter while'
	--
	set @commaPos=CHARINDEX(@splitChar,@remainText)
	print @commaPos
	if(@commaPos is null Or @commaPos=0)
	begin
		if(LEN(@remainText)>0)
		begin
			insert into @splitTable
			select @remainText
		end
		break
	end
	--
	set @splitText=LEFT(@remainText,@commaPos)
	--
	insert into @splitTable
	select REPLACE(@splitText,@splitChar,'')
	--
	set @remainText=RIGHT(@remainText,LEN(@remainText)-len(@splitText))
	
	print @remainText
end

select * from @splitTable

试试其它关键字

同语言下

可能有用的

京基贡献的其它代码(19)