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

jlthlx

　　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