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