sql分割，剪切，连接字符串实例

declare @str varchar(8000);
set @str='[';

DECLARE @temp TABLE([id] INT IDENTITY(1, 1),[Name] VARCHAR(50)) 

insert into @temp
select distinct Substring(MobileArea,0,charindex(' ',MobileArea,0)) from Dm_Mobile

DECLARE @tempId INT ,@tempName VARCHAR(50)  
WHILE EXISTS ( SELECT   [id] FROM     @temp )
BEGIN  
  SET ROWCOUNT 1;
    SELECT  @tempId = [id],@tempName = [Name] FROM @temp order by name
  SET ROWCOUNT 0  
        
        set @str=@str+'[';
        if (select count(*) from @temp)<=1
        begin
              set @str=@str+'["'+@tempName+'"][';  
        end
        else
        begin
              set @str=@str+'["'+@tempName+'"],[';  
        end
        delete from @temp where [id] = @tempId
        
        DECLARE @temp1 TABLE([id] INT IDENTITY(1, 1),[Name] VARCHAR(50)) 
            
            insert into @temp1
            select distinct Substring(MobileArea,charindex(' ',MobileArea,0)+1,50)
                from Dm_Mobile where MobileArea like '%'+@tempName+'%'; 
                
                DECLARE @tempId1 INT ,@tempName1 VARCHAR(50);
                 WHILE EXISTS ( SELECT   [id] FROM     @temp1 )
                 begin
                      SET ROWCOUNT 1;
                        SELECT  @tempId1 = [id],@tempName1 = [Name] FROM @temp1
                      SET ROWCOUNT 0;
                      if (select count(*) from @temp1)<=1
                        begin
                            set @str=@str+'"'+@tempName1+'"';  
                        end
                        else
                        begin
                                                set @str=@str+'"'+@tempName1+'",';  
                        end
                         
                            delete from @temp1 where [id] = @tempId1  
                      
                 end
                 set @str=@str+']],'; 
        
    end
    
    set @str=@str+']';
    
    print @str

 

转载于:https://www.cnblogs.com/zhushangwei/p/3533298.html