拆分json格式数组

把转换成

 

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; 
end

close my_cursor --关闭游标
deallocate my_cursor  --删除游标