- 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
- --该片段来自于http://www.codesnippet.cn/detail/140320149016.html
来源: http://www.codesnippet.cn/detail/140320149016.html