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
分享到:
相关推荐
SQL Server 逗号分隔的字符串转换成表 SQL Server 逗号分隔的字符串转换成表是指将一个逗号分隔的字符串转换成一个表结构,以便于进行查询、更新或者删除等操作。下面是将逗号分隔的字符串转换成表的步骤: 1. ...
SQL截取以逗号分隔的字符串SQL截取以逗号分隔的字符串SQL截取以逗号分隔的字符串SQL截取以逗号分隔的字符串
在SQL Server 2014中,自定义字符串聚合函数是一项重要的编程任务,尤其是在处理大量字符串数据时。标准的T-SQL提供了一些内置的聚合函数,如SUM、AVG、MAX和MIN,但它们并不支持将多个字符串合并为一个单一的字符串...
sqlserver中根据某个字符切割字符串函数,比如根据逗号切割字符串,结果返回的是一个表值函数,这个函数返回结果字段包含Id和Value,Id为序号,Value为切割后的值
Sql Server数据库中自定义拆分字符串函数Split()
SQLServer数据库中创建字符串截取功能(Split),调用方法: Split(string,str)
http://yufeng521000.iteye.com/blog/2076607; 根据这篇BOLG的函数编译的DLL,方便大家直接使用; 里面有两个DLL分别是NET3.5,以及NET4.0,下载后根据BOLG以及数据库版本选择对应的DLL使用;
以下测试用于去除任何字符串中连线的分隔符 代码如下: –去除字符串中连续的分隔符 declare @str nvarchar(200) declare @split nvarchar(200),@times int set @str=’中 国1 2 34 55 5 6 7 7′;–字符 set @split=...
sql自定义函数,用于合并字符串列,可以在分组的情况下对varchar类型的字段合并
2、按指定符号分割字符串,返回“分割后指定索引的第几个元素”的值,象数组一样方便 3、检查一个元素是否在数组中,返回1或0 4、检查一个元素是否与数组中的相匹配,并返回相匹配的数组的值。参数值为0表示全模糊...
sqlserver 实现 行转列 split 分割的函数,具体使用方法写有在文件里。
在SQL Server中,聚合函数是用于对一组值进行计算并返回单个值的函数,比如SUM、AVG、MAX和MIN等。然而,SQL Server的标准内置聚合函数可能无法满足所有复杂的数据处理需求,这时我们就需要自定义聚合函数。标题提到...
在SQL Server 2005中,连接字符串是一项常见的任务,尤其在处理包含多个值的列时。在本文中,我们将探讨三种不同的方法来按列连接字符串,这些方法适用于已经存在的表结构,无需进行重大改动。 首先,我们可以考虑...
将带分隔符的字符串 例如 '1,2,3,4,5,6,7,8,9,10' 转换成table 的列的函数
主要用于在数据库设计时需要自己生成定长的字符串作为关键字。这一个用户字符串每次递增而形成新串的数据库函数方法。
标题“SqlSserver自定义字符聚合”所指向的知识点主要是关于如何在SQL Server中创建和使用自定义聚合函数,特别是在处理字符串数据时的特殊需求。自定义字符聚合允许开发者根据业务需求定制特定的聚合逻辑,比如实现...
标题和描述所提及的问题是关于如何在Mapper.xml文件中处理字符串形式的参数,这些参数由逗号分隔,并在`AND`语句中拼接`OR`子句来实现动态查询。这种场景在处理用户多选过滤条件时非常常见,比如在一个搜索框中,...
在SQL Server中,将某一列的值拼接成字符串是一个常见的需求,这通常涉及到数据的格式化输出,比如创建报告或者导出数据。这个过程可以通过几种不同的方法来实现,其中包括使用FOR XML PATH子句和CONCAT函数。我们将...
SQL Server 字符串分隔与拼接实例 本文将为读者详细讲解 SQL Server 中字符串分隔与拼接的实例,包括创建函数、使用字符串函数 CHARINDEX 和 STUFF 等知识点。 字符串分隔与拼接实例 在实际应用中,我们经常需要...
自己写了一个function 用于处理拆分时的逻辑运算,性能比网上找的快好几倍, 代码和说明:https://blog.csdn.net/jimyao37/article/details/123522885