declare @sql varchar(1024)
declare @oldhscodepatten varchar(50)
declare @newhscodepatten varchar(50)
declare @corpidpatten varchar(50)
declare @chinesenamepatten varchar(50)
declare @newchinesenamepatten varchar(50)
declare @oldhscode varchar(50)
declare @newhscode varchar(50)
declare @corpid varchar(50)
declare @chinesename varchar(50)
declare @newchinesename varchar(50)
declare @i int
declare @j int
set @oldhscodepatten='Set @oldHsCode='--匹配字符串
set @newhscodepatten='Set @newHsCode='--匹配字符串
set @corpidpatten='Set @corpid='--匹配字符串
set @chinesenamepatten='Set @chinesename='--匹配字符串
set @newchinesenamepatten='Set @newchinesename='--匹配字符串
--set nocount off
DECLARE @temp TABLE (corpid char(4) ,
oldhscode varchar(110),
newhscode varchar(110),
chinesename varchar(50),
newchinesename varchar(50)
)
--查询字符串信息
DECLARE c0 CURSOR FOR
select modifysql from modifyrec
where modifylocation='海关' and convert(char(10),modifytime,120)>='2008-01-01'
and modifybilltype='修改HS编码' and modifysql not like '%update firstcheck%'
OPEN c0 --打开游标
FETCH NEXT FROM c0 INTO @sql
WHILE @@FETCH_STATUS=0
BEGIN
--oldhsode
select @i=charindex(@oldhscodepatten,@sql)+15
select @oldhscode=substring(@sql,@i,13)
select @oldhscode=replace(@oldhscode,'''','')
--newhscode
select @i=charindex(@newhscodepatten,@sql)+15
select @newhscode=substring(@sql,@i,13)
select @newhscode=replace(@newhscode,'''','')
--corpid
select @i=charindex(@corpidpatten,@sql)+13
select @corpid=substring(@sql,@i,4)
SET QUOTED_IDENTIFIER OFF--设置区分符号
--chinesename
select @i=charindex(@chinesenamepatten,@sql)+18
select @chinesename=substring(@sql,@i,50)
print @chinesename
select @j=charindex("'",@chinesename)--'改为"
if @j>0
select @chinesename=substring(@sql,@i,@j-1)
--newchinesename
select @i=charindex(@newchinesenamepatten,@sql)+21
select @newchinesename=substring(@sql,@i,50)
print @newchinesename
select @j=charindex("'",@newchinesename)
if @j=0
set @newchinesename=@chinesename
else
select @newchinesename=substring(@sql,@i,@j-1)
--插入到表变量
insert @temp(corpid,oldhscode,newhscode,chinesename,newchinesename)
values(@corpid,@oldhscode,@newhscode,@chinesename,@newchinesename)
--insert @temp(corpid,oldhscode,newhscode)
--values(@corpid,@oldhscode,@newhscode)
FETCH NEXT FROM c0 INTO @sql
END
CLOSE c0
DEALLOCATE c0
select * from @temp
order by corpid,oldhscode,newhscode
declare @oldhscodepatten varchar(50)
declare @newhscodepatten varchar(50)
declare @corpidpatten varchar(50)
declare @chinesenamepatten varchar(50)
declare @newchinesenamepatten varchar(50)
declare @oldhscode varchar(50)
declare @newhscode varchar(50)
declare @corpid varchar(50)
declare @chinesename varchar(50)
declare @newchinesename varchar(50)
declare @i int
declare @j int
set @oldhscodepatten='Set @oldHsCode='--匹配字符串
set @newhscodepatten='Set @newHsCode='--匹配字符串
set @corpidpatten='Set @corpid='--匹配字符串
set @chinesenamepatten='Set @chinesename='--匹配字符串
set @newchinesenamepatten='Set @newchinesename='--匹配字符串
--set nocount off
DECLARE @temp TABLE (corpid char(4) ,
oldhscode varchar(110),
newhscode varchar(110),
chinesename varchar(50),
newchinesename varchar(50)
)
--查询字符串信息
DECLARE c0 CURSOR FOR
select modifysql from modifyrec
where modifylocation='海关' and convert(char(10),modifytime,120)>='2008-01-01'
and modifybilltype='修改HS编码' and modifysql not like '%update firstcheck%'
OPEN c0 --打开游标
FETCH NEXT FROM c0 INTO @sql
WHILE @@FETCH_STATUS=0
BEGIN
--oldhsode
select @i=charindex(@oldhscodepatten,@sql)+15
select @oldhscode=substring(@sql,@i,13)
select @oldhscode=replace(@oldhscode,'''','')
--newhscode
select @i=charindex(@newhscodepatten,@sql)+15
select @newhscode=substring(@sql,@i,13)
select @newhscode=replace(@newhscode,'''','')
--corpid
select @i=charindex(@corpidpatten,@sql)+13
select @corpid=substring(@sql,@i,4)
SET QUOTED_IDENTIFIER OFF--设置区分符号
--chinesename
select @i=charindex(@chinesenamepatten,@sql)+18
select @chinesename=substring(@sql,@i,50)
print @chinesename
select @j=charindex("'",@chinesename)--'改为"
if @j>0
select @chinesename=substring(@sql,@i,@j-1)
--newchinesename
select @i=charindex(@newchinesenamepatten,@sql)+21
select @newchinesename=substring(@sql,@i,50)
print @newchinesename
select @j=charindex("'",@newchinesename)
if @j=0
set @newchinesename=@chinesename
else
select @newchinesename=substring(@sql,@i,@j-1)
--插入到表变量
insert @temp(corpid,oldhscode,newhscode,chinesename,newchinesename)
values(@corpid,@oldhscode,@newhscode,@chinesename,@newchinesename)
--insert @temp(corpid,oldhscode,newhscode)
--values(@corpid,@oldhscode,@newhscode)
FETCH NEXT FROM c0 INTO @sql
END
CLOSE c0
DEALLOCATE c0
select * from @temp
order by corpid,oldhscode,newhscode