把转换成

 

declare @key nvarchar(3);declare @data nvarchar(max);declare    @code nvarchar(50) declare    @countrycn nvarchar(100) declare    @countryus nvarchar(100)declare    @provincecn nvarchar(100) declare    @provinceus nvarchar(100) declare    @citycn nvarchar(100) declare    @cityus nvarchar(100) declare    @cityid nvarchar(50) declare @str nvarchar(1000);declare my_cursor cursor --定义游标for (select [key] from airportdemo  ) -- 把数据集导入游标open my_cursor  -- 打开游标fetch next from my_cursor into @key  --把游标中第一条放到变量key中while @@fetch_status=0      --循环遍历begin     select @code='',@countrycn='',@countryus='',@countrycn='',@provincecn='',@provinceus='',@citycn='',@cityus='',@cityid='';--初始化变量            select @data = data from airportdemo where [key]=@key;    print @data ;    select @code = [key] from dbo.GF_StringSplit(@data);--根据data获得key，data数据表，并把key赋给@code；    select @str = data from dbo.GF_StringSplit(@data);--@str获得data数据    declare @firstData nvarchar(1000);     select @firstData= [string] from dbo.uf_StrSplit(@str,',') where str_id = 1;    declare @secondData nvarchar(1000);     select @secondData= [string] from dbo.uf_StrSplit(@str,',') where str_id = 2;--把data按 ，分割开来     declare @thirdData nvarchar(1000);    select @thirdData =[string] from dbo.uf_StrSplit(@str,',') where str_id = 3;    declare @thirdCount int;    set @thirdCount = dbo.Get_StrArrayLength(@thirdData,'|');    declare @count int ;    set @count = dbo.Get_StrArrayLength(@secondData,'|');    if(@count = 4)    begin        select @countrycn= [string] from dbo.uf_StrSplit(@str,',') where str_id = 3;        select @countryus = [string] from dbo.uf_StrSplit(@secondData,'|') where str_id = 1;--把 CHINA|阿里(NGQ)|97 阿里按'|'来分割 ，取下标为1的数据        set       @cityus=substring(@firstData,2,len(@firstData)-1);        select @cityid = [string] from dbo.uf_StrSplit(@secondData,'|') where str_id = 3;         select @citycn = [string] from dbo.uf_StrSplit(@secondData,'|') where str_id = 4;             end    else if(@thirdCount=4)    begin         select @countrycn= [string] from dbo.uf_StrSplit(@str,',') where str_id=4;        select @countryus = [string] from dbo.uf_StrSplit(@thirdData,'|') where str_id = 1;        select @cityid = [string] from dbo.uf_StrSplit(@thirdData,'|') where str_id = 3;                 select @cityus = substring(@firstData,2,len(@firstData)-1);                select @citycn = [string] from dbo.uf_StrSplit(@thirdData,'|') where str_id = 4;        select @provincecn = [string] from dbo.uf_StrSplit(@str,',') where str_id=5;        select @provinceus = [string] from dbo.uf_StrSplit(@str,',') where str_id=2;    end    else     begin         select @cityid=[string] from dbo.uf_StrSplit(@firstData,'|') where str_id = 3;        select @citycn=[string] from dbo.uf_StrSplit(@firstData,'|') where str_id = 4;        select @countryus=[string] from dbo.uf_StrSplit(@firstData,'|') where str_id = 1;    end    insert into dbo.TurnAirPort(code, countrycn, countryus, provincecn, provinceus, citycn, cityus, cityid) values(@code, @countrycn, @countryus, @provincecn, @provinceus, @citycn, @cityus, @cityid);--把数据插入数据表    fetch next from my_cursor into @key; endclose my_cursor --关闭游标deallocate my_cursor  --删除游标