sqlserver2005 自定义split,字符串转数组，逗号分隔的形式保存

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