1.前言.
sqlserver2005没有splite函数，所以只能自己写。下面实现了一个字段存放1天的1440个点的数据，数据以int类型保存并以逗号分隔，如1,2,3,4,5,7,9.
2.程序
ALTER FUNCTION [dbo].[getDelays]
(
@DELAYS varchar(8000),
@DELAY int,
@E_TIME datetime
)
RETURNS varchar(8000) AS
BEGIN
DECLARE @delayResult varchar(8000)
DECLARE @miCount int
DECLARE @count1 int --计数
DECLARE @index int
DECLARE @start int
DECLARE @location int
DECLARE @loopCount int
DECLARE @lastData varchar(10)
DECLARE @lastLocation int
--根据实际情况把字符串分成1000个逗号分隔,如果前面一个有延时那么填平
--循环，如果前面没数，那么沿用最初的数据，一直填充到本数据为止,获取分钟数，0到59
--1.先获取天数 .
SET @miCount= CONVERT(int,datename(hh,@E_TIME))*60+CONVERT(int,datename(mi,@E_TIME))+1;
--2.判断是否有数，保证有1440个点
SET @loopCount=1440;
--如果还没数或者数根本没逗号，那么先组织成1440个零
IF(@DELAYS IS NULL) OR charindex(',',@DELAYS)=0
begin
SET @delayResult=0;
--如果没有数据，则预定1440个点，里面插入数据
WHILE @loopCount>1
BEGIN
SET @delayResult=@delayResult+',0';
SET @loopCount=@loopCount-1;
END
end
ELSE
BEGIN
--如果有，则沿用已经有数的字符串
SET @delayResult=@DELAYS;
END;
--3根据序号插入数
--3.1先定位置
set @location=charindex(',',@delayResult);
set @start=1;
SET @count1=0;
while @location>0
BEGIN
set @start=@location+1
set @lastLocation=@location;
set @location=charindex(',',@delayResult,@start);
SET @count1=@count1+1;
IF(@count1=@miCount)
BEGIN
BREAK;
END
END
--3.2然后插入,将从
SET @delayResult=stuff(@delayResult,@lastLocation-1,@location-@lastLocation+1,@DELAY )
RETURN @delayResult;
END
--SELECT dbo.getDelays('0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,100,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0',100,'2013-09-05 00:00:00');
3.补充。
另一填充例子。
--根据日期将对应的次数填充到类似0,0,0,1,3,2,0,0三十一个逗号的字符串中，用来合并月数据
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[setCounts]
(
@Counts varchar(2000),
@E_TIME datetime,
@times bigint
)
RETURNS varchar(2000) AS
BEGIN
DECLARE @CountsResult varchar(2000)
DECLARE @miCount int --1个月的第几天
DECLARE @count1 int --计数
DECLARE @index int
DECLARE @start int
DECLARE @location int
DECLARE @loopCount int
DECLARE @lastData varchar(10)
DECLARE @lastLocation int
DECLARE @theCount bigint
DECLARE @theCountStr varchar(100)
--总体功能:将对应天数对应的次数加1
--思路:固定32个数字加31个豆号,
--1.先获取天数 .
SET @miCount=datepart(dd,@E_TIME) ;
--2.默认值
if(@Counts is null or ''=@Counts)
begin
set @CountsResult='0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0';
end
else
begin
SET @CountsResult=@Counts;
end
--截取第一个逗号
set @location=charindex(',',@CountsResult);
set @start=1;
SET @count1=0;
if(@miCount=1)
begin
--如果是1号特殊处理
set @theCountStr=substring(@CountsResult,1,@location-1);
set @theCount=cast(@theCountStr as bigint);
return stuff(@CountsResult,1,@location-1,convert(varchar(100),@theCount+@times) );
end
else
begin
---不是1号则找到对应的位置
while @location>0
BEGIN
set @start=@location+1;
set @lastLocation=@location;
set @location=charindex(',',@CountsResult,@start);
SET @count1=@count1+1;
IF(@count1=@miCount-1)
BEGIN
BREAK;
END
end
set @theCountStr=substring(@CountsResult,@lastLocation+1,@location-@lastLocation-1);
set @theCount=cast(@theCountStr as bigint);
SET @CountsResult= stuff(@CountsResult,@lastLocation+1,@location-@lastLocation-1,convert(varchar(100),@theCount+@times) );
RETURN @CountsResult;
end
RETURN @CountsResult;
END
分享到：
相关推荐
SQLServer逗号分隔的字符串转换成表
SQL截取以逗号分隔的字符串SQL截取以逗号分隔的字符串SQL截取以逗号分隔的字符串SQL截取以逗号分隔的字符串
Sql Server数据库中自定义拆分字符串函数Split()
sqlserver中根据某个字符切割字符串函数，比如根据逗号切割字符串，结果返回的是一个表值函数，这个函数返回结果字段包含Id和Value，Id为序号，Value为切割后的值
SQLServer数据库中创建字符串截取功能（Split），调用方法： Split(string,str)
NULL 博文链接：https://yufeng521000.iteye.com/blog/2076607
以下测试用于去除任何字符串中连线的分隔符 代码如下: –去除字符串中连续的分隔符 declare @str nvarchar(200) declare @split nvarchar(200),@times int set @str=’中 国1 2 34 55 5 6 7 7′;–字符 set @split=...
将sql sqlser 日期类型转化成各种格式的字符串。
sql自定义函数,用于合并字符串列，可以在分组的情况下对varchar类型的字段合并
SQL Server 2005 杂谈 在SQL Server2005中按列 连接字符串 三种方法 doc SQL Server 2005 杂谈 在SQL Server2005中按列 连接字符串 三种方法 doc
2、按指定符号分割字符串，返回“分割后指定索引的第几个元素”的值，象数组一样方便 3、检查一个元素是否在数组中，返回1或0 4、检查一个元素是否与数组中的相匹配，并返回相匹配的数组的值。参数值为0表示全模糊...
将带分隔符的字符串 例如 '1,2,3,4,5,6,7,8,9,10' 转换成table 的列的函数
sqlserver 实现 行转列 split 分割的函数，具体使用方法写有在文件里。
sql server拼接字符串查询语句。 普通拼接字符串和拼接某一列的所有值。
主要用于在数据库设计时需要自己生成定长的字符串作为关键字。这一个用户字符串每次递增而形成新串的数据库函数方法。
http://yufeng521000.iteye.com/blog/2076607； 根据这篇BOLG的函数编译的DLL，方便大家直接使用； 里面有两个DLL分别是NET3.5，以及NET4.0，下载后根据BOLG以及数据库版本选择对应的DLL使用；
将字符串数据进行聚合，来实现 系统的sum（），count（）等效果
ADO连接SQL Server的数据库连接字符串
自己写了一个function 用于处理拆分时的逻辑运算，性能比网上找的快好几倍， 代码和说明：https://blog.csdn.net/jimyao37/article/details/123522885
sqlserver连接字符串大全，基本涵盖全了