SQL2000 中常用的函数收集（持续更新）

------------------------------------------------------分拆函数：----------------------------------------------

    Create function F_splitstring
    (
      @str varchar(8000) --要分拆的字符串
      ,@spli varchar(10) --字符串分隔符
    )
    returns @retab table(istr varchar(50))
    as
    begin

    declare @i int
    declare @splen int
    select @splen=len(@spli),@i=charindex(@spli,@str)
    while @i > 0
    begin
    insert into @retab
    values(left(@str,@i-1))
    select @str=substring(@str,@i+@splen,8000)
    select @i=charindex(@spli,@str)
    end
    if @str <> ' ' insert into @retab values(@str)
    return
/*
select * from uf_splitstring( '1,2,3,4,5,6,7,8 ', ', ')


istr
-----------------
1
2
3
4
5
6
7
8
*/
end

-----------------------------------------十进制到十六进制转换：----------------------------------------------------
Create FUNCTION dbo.f_dec_hex(@s varchar(16))
RETURNS varchar(10)
AS
BEGIN
  DECLARE @i int,@Dec bigint,@Result varchar(10),@TmpDec int
  select @Dec=convert(bigint,@s),@Result=''
  while @Dec>=16 begin
    set @TmpDec=@dec%16
    set @Dec=@dec/16
    set @Result=(case
      when @TmpDec<10 then Convert(varchar(1),@TmpDec)
      when @TmpDec=10 then 'A'
      when @TmpDec=11 then 'B'
      when @TmpDec=12 then 'C'
      when @TmpDec=13 then 'D'
      when @TmpDec=14 then 'E'
      when @TmpDec=15 then 'F'
    end)+@Result
  end
    set @Result=(case
      when @Dec<10 then Convert(varchar(1),@Dec)
      when @Dec=10 then 'A'
      when @Dec=11 then 'B'
      when @Dec=12 then 'C'
      when @Dec=13 then 'D'
      when @Dec=14 then 'E'
      when @Dec=15 then 'F'
    end)+@Result
  return @Result

/*

    select dbo.f_dec_hex('254')

*/

end

-----------------------------------------十六进制到十进制转换：----------------------------------------------------

Create   FUNCTION dbo.f_hex_dec(@s varchar(16))
RETURNS varchar(10)
AS
BEGIN
  DECLARE @i int,@result bigint
  SELECT @i=0,@result=0,@s=RTRIM(LTRIM(UPPER(REVERSE(@s))))
  WHILE @i<LEN(@s)
  BEGIN
    IF SUBSTRING(@s,@i+1,1) not between '0' and '9' and SUBSTRING(@s,@i+1,1) not between 'A' and 'F'
    BEGIN
      SELECT @result=0
      break
    END
    SELECT @result=@result+(CHARINDEX(SUBSTRING(@s,@i+1,1),'0123456789ABCDEF')-1)*cast(POWER(16,@i) as bigint),@i=@i+1
  END
  RETURN convert(varchar(10),@result)
END

---------------------------------------获得两时间之间的分钟数-----------------------------------------------------------

Create  function DiffHour(@d1 decimal,@d2 decimal) returns int
as
begin
  declare @t1 decimal
  declare @t2 decimal
  declare @f1 decimal
  declare @f2 decimal
  declare @r1 decimal
  declare @r2 decimal
  declare @hour int
  select @t1=@d1/10000
  select @t2=@d2/10000
  select @f1=@d1-floor(@d1/10000)*10000
  select @f2=@d2-floor(@d2/10000)*10000
  select @r1=floor(@f1/100)*60+@f1-floor(@f1/100)*100
  select @r2=floor(@f2/100)*60+@f2-floor(@f2/100)*100
  if @t2>@t1 begin
     select @hour=@r2+1440-@r1
  end
  else begin
     select @hour=@r2-@r1
  end
  return (@hour)

/*

    --select personal.dbo.diffhour(200409032233,200409050133)
*/

end

-------------------------------------当前日期为星期几-----------------------------------------

Create  function GetWeekCName(@Date datetime)
returns nvarchar(4)
as
begin
    declare @wk int,@weekname nvarchar(4)
    set @wk=datepart(weekday,@Date)
    if @wk=1
        set @weekname='星期日'
    if @wk=2
        set @weekname='星期一'
    if @wk=3
        set @weekname='星期二'
    if @wk=4
        set @weekname='星期三'
    if @wk=5
        set @weekname='星期四'
    if @wk=6
        set @weekname='星期五'
    if @wk=7
        set @weekname='星期六'

    return @weekname
end

-------------------------------------转BarCode128a码------------------------------------------------------

Create FUNCTION GetCode128a(@DataToEncode nvarchar(200))
RETURNS nvarchar(200)
AS  
BEGIN
     declare @PrintableString nvarchar(200),@WeightedTotal int,@StringLength int,@CurrentCharNum int,@CurrentValue int,@CheckDigitValue int,@C128CheckDigit nvarchar(200),@Code128a nvarchar(200),@I int

     select @WeightedTotal = 103,@PrintableString = nChar(203),@StringLength = Len(@DataToEncode),@I=1

     while @I<=@StringLength
     begin
          set @CurrentCharNum = unicode(substring(@DataToEncode, @I, 1))
          If @CurrentCharNum < 135
         set @CurrentValue = @CurrentCharNum - 32
          If @CurrentCharNum > 134
         set @CurrentValue = @CurrentCharNum - 100
          set @CurrentValue = @CurrentValue * @I
          set @WeightedTotal = @WeightedTotal + @CurrentValue
          If @CurrentCharNum = 32
         set @CurrentCharNum = 194
          set @PrintableString = @PrintableString + nChar(@CurrentCharNum)
      set @I=@I+1
     end

     set @CheckDigitValue = (@WeightedTotal % 103)
     If @CheckDigitValue < 95 And @CheckDigitValue > 0
     set @C128CheckDigit = nChar(@CheckDigitValue + 32)
     If @CheckDigitValue > 94
     set @C128CheckDigit = nChar(@CheckDigitValue + 100)
     If @CheckDigitValue = 0
     set @C128CheckDigit = nChar(194)
     set @PrintableString = @PrintableString + @C128CheckDigit + nChar(206)
     set @Code128a = @PrintableString

     return @Code128a
END

----------------------------------------------------------------------获取数据表中的所有字段列表-------------------------------------------------------------------

select a.name,(case when b.name='varchar' then 'varchar('+convert(varchar(10),COLUMNPROPERTY(a.id,a.name,'PRECISION'))+')' else b.name end),b.name,COLUMNPROPERTY(a.id,a.name,'PRECISION') from syscolumns a
  left join master.dbo.systypes b on a.xusertype=b.xusertype
    where id=object_id(N'psn_personal')
      order by a.colid