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