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截取以逗号分隔的字符串SQL截取以逗号分隔的字符串SQL截取以逗号分隔的字符串SQL截取以逗号分隔的字符串
在SQL Server 2014中，自定义字符串聚合函数是一项重要的编程任务，尤其是在处理大量字符串数据时。标准的T-SQL提供了一些内置的聚合函数，如SUM、AVG、MAX和MIN，但它们并不支持将多个字符串合并为一个单一的字符串...
sqlserver中根据某个字符切割字符串函数，比如根据逗号切割字符串，结果返回的是一个表值函数，这个函数返回结果字段包含Id和Value，Id为序号，Value为切割后的值
Sql Server数据库中自定义拆分字符串函数Split()
然而，有时系统内置的聚合函数无法满足特定需求，此时就需要利用C#来扩展SQL Server的功能，创建自定义的聚合函数，特别是针对字符串类型的处理。 字符串聚合函数是指能够处理字符串集合并返回单个字符串结果的函数...
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=...
2、按指定符号分割字符串，返回“分割后指定索引的第几个元素”的值，象数组一样方便 3、检查一个元素是否在数组中，返回1或0 4、检查一个元素是否与数组中的相匹配，并返回相匹配的数组的值。参数值为0表示全模糊...
sqlserver 实现 行转列 split 分割的函数，具体使用方法写有在文件里。
标题“SqlSserver自定义字符聚合”所指向的知识点主要是关于如何在SQL Server中创建和使用自定义聚合函数，特别是在处理字符串数据时的特殊需求。自定义字符聚合允许开发者根据业务需求定制特定的聚合逻辑，比如实现...
总结起来，SQL Server 2008中拆分字符串通常需要自定义解决方案，如上述的交叉连接方法。虽然这个方法可以工作，但在处理大量数据时，可能需要寻找更优化的策略，例如使用用户定义的函数（UDF），或者在支持的版本中...
标题和描述所提及的问题是关于如何在Mapper.xml文件中处理字符串形式的参数，这些参数由逗号分隔，并在`AND`语句中拼接`OR`子句来实现动态查询。这种场景在处理用户多选过滤条件时非常常见，比如在一个搜索框中，...
在SQL Server中，将某一列的值拼接成字符串是一个常见的需求，这通常涉及到数据的格式化输出，比如创建报告或者导出数据。这个过程可以通过几种不同的方法来实现，其中包括使用FOR XML PATH子句和CONCAT函数。我们将...
输入六位以内的数字，返回六位字符，如：输入‘123’则返回'000123'
例如，如果你有一个字段存储了逗号分隔的值（如ID列表），你可以先使用`TOKENIZE()`分隔字符串，然后通过循环处理每个ID，这在处理批量数据时非常有用。 5. 性能优化： 当处理大量字符串数据时，注意优化查询，...
SQL Server 字符串分隔与拼接实例 本文将为读者详细讲解 SQL Server 中字符串分隔与拼接的实例，包括创建函数、使用字符串函数 CHARINDEX 和 STUFF 等知识点。 字符串分隔与拼接实例 在实际应用中，我们经常需要...
自己写了一个function 用于处理拆分时的逻辑运算，性能比网上找的快好几倍， 代码和说明：https://blog.csdn.net/jimyao37/article/details/123522885
sqlServer中，给字符串开头或结尾自动补零，可以控制补几个零，共三种方法。
在Oracle数据库中，进行字符串查询时，特别是当字符串中包含逗号等特殊字符时，我们需要巧妙地使用SQL的LIKE操作符来实现精确匹配。这里，我们面对的问题是想要查询DWDH字段中含有"飞信"但不包含"移动飞信"或"飞信...