将字符串, ;分隔为表进行处理

来源：互联网发布：tcp与udp端口号范围编辑：程序博客网时间：2024/04/30 12:36

if OBJECT_ID('tempdb..#tb') is not null
drop table #tb;
declare @comText nvarchar(4000);
create table #tb(transportNo varchar(20),billno varchar(20));

set @comText='150830001001,1;1508250101,1;1508290024,3';

set nocount on;
declare @semicolonlast int,@semicolonCurrent int;
declare @singleCmdText nvarchar(100);
declare @transportNo nvarchar(20);
declare @commalast int,@commaCurrent int;
declare @lastCmdText nvarchar(100);
declare @billno nvarchar(20),@status char(1);
set @semicolonlast=0;
while 1=1
begin
set @semicolonCurrent=CHARINDEX(';',@comText,@semicolonlast);
if @semicolonCurrent>0
set @singleCmdText=SUBSTRING(@comText,@semicolonlast,@semicolonCurrent-@semicolonlast);

else
set @singleCmdText=SUBSTRING(@comText,@semicolonlast,LEN(@comText)-@semicolonlast+1);
print @singleCmdText;
set @semicolonlast=@semicolonCurrent+1;

set @commaCurrent=0;

set @commaCurrent=CHARINDEX(',',@singleCmdText,0);
set @transportNo=SUBSTRING(@singleCmdText,0 ,@commaCurrent);
set @billno=SUBSTRING(@singleCmdText,@commaCurrent+1,LEN(@singleCmdText)-@commaCurrent);
insert #tb(transportNo,billno)values(@transportNo,@billno);

if @semicolonCurrent=0
break;
end
select * from #tb;

0 0