几个非常有用SQL Server的函数(SQL Server的函数的使用)

SQL Server的函数的使用能大大方便某些转换。

(1)中元年与民国年的区分

SELECT DBO.FN_ConvertYear('2007')

CREATE FUNCTION [dbo].[FN_ConvertYear](@Year VARCHAR(10))
RETURNS VARCHAR(10)
AS

BEGIN

   --返回值
   DECLARE @ReturnValue VARCHAR(10)
   SET @ReturnValue = ''

   --日期類型
   DECLARE @Date_Type VARCHAR(4)
   SET @Date_Type = ''

   --從系統參數設定表(BSYSTEMM)中獲得系統設定的日期類型

   --定義游標
   DECLARE cur_Query CURSOR
   FOR
   SELECT DATA_TYPE FROM BSYSTEMM
  
   --打開游標
   OPEN  cur_Query

   --獲取系統參數設定表中設定的Date_Type的值
   FETCH NEXT FROM cur_Query INTO @Date_Type

   --判斷游標存取數據是否正確
   IF @@Fetch_status<>-1
   BEGIN
      IF @Date_Type = 'cn'
      BEGIN
         SET @ReturnValue = @Year
      END

      ELSE
      BEGIN
         SET @ReturnValue= Right('00'+cast(cast(@Year as int) - 1911 as varchar(10)),3)
      END
   END

   ELSE
   BEGIN
      SET @ReturnValue=  Right('00'+cast(cast(@Year as int) - 1911 as varchar(10)),3)
   END
 
   --返回值
   return @ReturnValue
END

(2)把數值型化為三位一撇

/*
功能：把數值型化為三位一撇
作者：Akuan
日期：2007/08/20
范例：SELECT DBO.FN_FormatNumber('9998886660')

*/

ALTER FUNCTION [dbo].[FN_FormatNumber](@Value VARCHAR(100))
RETURNS VARCHAR(100)
AS

BEGIN
 
   --臨時變量
   DECLARE @Temp varchar(100)
   SET @Temp=''

   --返回值
   DECLARE @ReturnValue VARCHAR(100)
   SET @ReturnValue = ''

    DECLARE @i INT
    SET @i=charindex('.',@Value)

    IF @i>0
    BEGIN
       SET @Temp = RIGHT(@Value,LEN(@Value)-@i+1)
       SET @ReturnValue = LEFT(@Value,@i-1)
    END

    ELSE
    BEGIN
       SET @ReturnValue = @Value
    END
   
    IF LEN(@ReturnValue)>3
    BEGIN
       WHILE LEN(@ReturnValue)>3
       BEGIN
           SET @Temp = ',' + RIGHT(@ReturnValue,3) + @Temp
           SET @ReturnValue = LEFT(@ReturnValue,LEN(@ReturnValue)-3)
       END
       SET @ReturnValue = @ReturnValue + @Temp
    END
   
    ELSE
    BEGIN
       SET @ReturnValue = @Value;
    END

 
   --返回值
   return @ReturnValue
END

(3)數字格式化
CREATE  FUNCTION FormatNumber(@flNum float,@Dot INT ) 
/*
程式功能：數字格式化
參數：@flNum被格式化的數字，@Dot 欲輸出小數位數
調用方法：SELECT dbo.FormatNumber(123.006,2)
*/
RETURNS varchar(50) AS
BEGIN  
 DECLARE @strReturn varchar(50),
   @mflNum float,
   @mDot INT
 SELECT @mDot=CASE WHEN @Dot<0 THEN 0 ELSE @Dot END
 IF @mDot=0
   SELECT @strReturn=convert(varchar,convert(int,ROUND(@flNum,@mDot)))
 ELSE
          SELECT @strReturn=convert(varchar,convert(int,ROUND(@flNum,@mDot)))+(case ROUND(@flNum,@mDot)-convert(int,ROUND(@flNum,@mDot)) when 0 then '.'+REPLICATE('0',@mDot) else SUBSTRING(convert(varchar,ROUND(@flNum,@mDot)-convert(int,ROUND(@flNum,@mDot)))+REPLICATE('0',@mDot),2,@mDot+1) end)
        

 return @strReturn
END

(4)查找函数

create   function f_reseach(@str1 varchar(8000),@str2 varchar(100)) returns int
as
begin
 declare @strtemp varchar(8000)----字符串代表參數1的值
 declare @strtepm1 varchar(100)----字符串代表參數2的值
 declare @returns int---返回的值，計數返回的值
 declare @i int      ----計數，從第一個開始到最後一個字符為止
 declare @str varchar(8000)-----用來存放查詢出來的字符串
 declare @len int ------字符串@str1的長度
 declare @len1 int -----字符串@str2的長度

------初始化各個參數
set  @strtemp=ltrim(rtrim(@str1))
set  @strtepm1=ltrim(rtrim(@str2))
set  @len=len(@strtemp)
set  @len1=len(@strtepm1)
set  @returns=1
set  @i=1
set  @str=''

while(@i<=@len)
begin
 set @str=substring(@strtemp,@i,@len1)
 if  @str= @strtepm1
 begin
  set @returns=@i
  return @returns
 end
set @i=@i+1
end
return  @returns
end

(5)实现程式中分割功能的函数

CREATE  function f_split(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
--实现split功能 的函数
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>'/'
insert @temp values(@SourceSql)
return
end

 

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

(6)將傳入的西元日期時間轉換為日期

CREATE         FUNCTION GetStrDate (@CON_DATE DATETIME ) 
/*
程式功能：將傳入的西元日期時間轉換為日期
參數：@CON_DATE　西元日期
調用方法：SELECT dbo.GetStrDate('2004/12/13 8:11:12')
*/
RETURNS varchar(10) AS
BEGIN  
 DECLARE @strReturn varchar(10)

              IF @CON_DATE ='' OR @CON_DATE IS NULL
                  SELECT @strReturn=''
             ELSE
                  SELECT @strReturn=CONVERT(varchar,CONVERT(INT,DATEPART (YEAR,@CON_DATE))) + '/' +REPLICATE('0',2-LEN(CONVERT(INT,DATEPART (MONTH,@CON_DATE)))) +CONVERT(varchar,CONVERT(INT,DATEPART (MONTH,@CON_DATE))) + '/' +REPLICATE('0',2-LEN(CONVERT(INT,DATEPART (DAY,@CON_DATE))))+ CONVERT(varchar,CONVERT(INT,DATEPART (DAY,@CON_DATE)))

             return @strReturn
END

(7)split方法二

CREATE   function Split(@aString varchar(4000),@pattern varchar(1))
returns @temp table([Sid]  [int] IDENTITY (1, 1) NOT NULL ,mail varchar(100))
as
begin
    declare @i int
    set @aString=rtrim(ltrim(@aString))
    set @i=charindex(@pattern,@aString)
    while @i>=1
    begin
        insert @temp values(left(@aString,@i-1))
        set @aString=substring(@aString,@i+1,len(@aString)-@i)
        set @i=charindex(@pattern,@aString)
    end
    if @aString<>''
       insert @temp values(@aString)
    return
end

(8)列表转换范例(使用游标)

--取得@USER_CODE_LIST參數傳入的用戶代號列表值(如：00100,0010,00200)，然後獲得對應的用戶名列表
CREATE               FUNCTION fnGetUserNameList(@USER_CODE_LIST AS VARCHAR(500))
RETURNS NVARCHAR(3000)
AS
BEGIN
 
      DECLARE @RETURN_VALUE NVARCHAR(2000)
      SET @RETURN_VALUE=''
      --定義一個臨時變量
      DECLARE @TEMP_VALUE VARCHAR(50)
     
      DECLARE CUR_USER CURSOR FOR
      SELECT USER_CHNAME FROM PM021TB WHERE USER_CODE  COLLATE  Chinese_PRC_CS_AI IN (SELECT mail FROM dbo.Split(@USER_CODE_LIST,',') AS R)
      OPEN CUR_USER
      FETCH NEXT FROM CUR_USER INTO @TEMP_VALUE
     WHILE(@@Fetch_status<>-1)
     BEGIN
            IF @RETURN_VALUE=''
            BEGIN
                  SET @RETURN_VALUE=@TEMP_VALUE
            END
            ELSE
            BEGIN
                  SET @RETURN_VALUE=@RETURN_VALUE+','+@TEMP_VALUE
            END
    
            --獲得下一個用戶名
            FETCH NEXT FROM CUR_USER INTO @TEMP_VALUE
     END 
     RETURN @RETURN_VALUE
   
END