时间:2021-07-01 10:21:17 帮助过:46人阅读
/*============================================================================== * *Filename:TB_UpdateList.sql *Description:sql数据库字段分割字符指定位置批量替换,引用f_split *Version:1.0 *Created:2014.03.12 *Author:liangjw *E-mail:liangjw0
/*==============================================================================/****** Object: UserDefinedFunction [dbo].[f_split1] Script Date: 12/14/2010 21:45:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --测试数据信息 -- SELECT * FROM dbo.[f_split1] ('21|80|102|92|38|0|0|0|102|0|0|0|0','|') ALTER function [dbo].[f_split1](@c varchar(2000),@split varchar(2))--@c表示字符串,@split表示分隔符 returns @t table(id INT,col varchar(20)) ----返回一个表,用这个表来存储分割出来的所有数据 as begin DECLARE @tmpid int=1 while(charindex(@split,@c)<>0)----当分隔符的索引不等于0时 begin insert @t(id,col) values (@tmpid,substring(@c,1,charindex(@split,@c)-1))----分割出来的单个字符 set @c = stuff(@c,1,charindex(@split,@c),'')----设置分割后的字符串 SET @tmpid=@tmpid+1 end insert @t(id,col) values (@tmpid,@c) ----把分割出来的单个字符插入到表@t表 return END -------------------------------------------------------------------------- -------------------------------------------------------------------------- /****** Object: StoredProcedure [dbo].[TB_UpdateList] Script Date: 03/12/2014 17:19:15 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --更改指定字符串中,指定位置的数据信息。 --SELECT * FROM dbo.[f_split1] ('21|80|102|92|38|0|0|0|102|0|0|0|0','|') ALTER PROCEDURE [dbo].[TB_UpdateList] AS --判断临时表数据是否存在,如果存在则删除临时表 if OBJECT_ID('tempdb..##project') is not null DROP TABLE ##project if OBJECT_ID('tempdb..##projectItem') is not null DROP TABLE ##projectItem --获取数据信息 SELECT id, SizeSum INTO ##project FROM dbo.TB_MakeTrimPlan WHERE Size9>0 --定义变量 DECLARE @tmpid INT, @SizeSum NVARCHAR(500),@tmplid INT,@SizeLSum NVARCHAR(500) DECLARE @sql NVARCHAR(max),@listvalue NVARCHAR(500),@getF NVARCHAR(500),@getd NVARCHAR(500) --创建临时表 CREATE TABLE ##projectItem( id INT , col NVARCHAR(50) ) --查询数据信息 SELECT @tmpid = MIN(id) FROM ##project WHILE @tmpid IS NOT NULL BEGIN SELECT @SizeSum = SizeSum FROM ##project WHERE id =@tmpid INSERT INTO ##projectItem SELECT * FROM dbo.[f_split1](@SizeSum,'|') -- DROP TABLE ##projectItem SELECT @tmplid=MIN(id) FROM ##projectItem --定义变量 DECLARE @Ttotallistvalue NVARCHAR(500),@tmpq NVARCHAR(500) SET @Ttotallistvalue='' SET @tmpq='' WHILE @tmplid IS NOT NULL BEGIN --查询数据信息 SELECT @listvalue =col FROM ##projectItem WHERE id = @tmplid --取出数据信息 SET @tmpq = @tmpq + @listvalue+'|' IF @tmplid=9 BEGIN --分割后第九个,可以自定义位数 SET @listvalue= 0 END --如果是指定位置就去修改数值信息 SET @Ttotallistvalue= @Ttotallistvalue + @listvalue+'|' --找出最小值依次循环 SELECT @tmplid = MIN(id) FROM ##projectItem WHERE id > @tmplid END --去除最后一个字符 SET @getF=left(@Ttotallistvalue,len(@Ttotallistvalue)-1) SET @getD=left(@tmpq,len(@tmpq)-1) --更新数据信息 SET @sql ='update TB_MakeTrimPlan set SizeSum= ''' +@getF+''' where SizeSum='''+@getD+'''' EXEC(@sql) SELECT @tmpid = MIN(id) FROM dbo.##project WHERE id >@tmpid END --判断临时表数据是否存在,如果存在则删除临时表 if OBJECT_ID('tempdb..##project') is not null DROP TABLE ##project if OBJECT_ID('tempdb..##projectItem') is not null DROP TABLE ##projectItem