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

原创 2007年10月08日 16:27:00

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

 

Microsoft SQL Server 自定义函数整理大全

SQL SERVER中是允许有自定义函数,Microsoft SQL Server 并不将用户限制在定义为 Transact-SQL 语言一部分的内置函数上,而是允许用户创建自己的用户定义函数。 函数...
  • ycl295644
  • ycl295644
  • 2016年02月29日 11:02
  • 2218

SQL server2005基本sql语句 常用的内置函数、聚合函数的使用详解

实体完整性约束   约束种类                                      功能描述   PRIMARY KEY(主键)约束                      ...
  • xiang462042190
  • xiang462042190
  • 2014年05月24日 09:44
  • 1308

Sql Server 常用系统存储过程大全

EXEC xp_cmdshell --*执行DOS各种命令,结果以文本行返回。 EXEC xp_fixeddrives --*查询各磁盘/分区可用空间 EXEC xp_loginconfig --...
  • peng790
  • peng790
  • 2016年08月16日 14:40
  • 1892

sql server 2005 几个常用的存储过程或函数

--------------------------------------------------------功能:十六进制转换为文件号 --使用:SELECT dbo.convert_page_n...
  • leamonjxl
  • leamonjxl
  • 2011年05月23日 10:06
  • 510

SQL Server 中几个有用的特殊函数

在SQL Server 的使用过程中,发现几个很有用,但不太常用(或细节不太清楚)的函数(存储过程): isnumeric,isdate,patindex,newid,collate,sp_exec...
  • make1828
  • make1828
  • 2012年07月05日 16:15
  • 473

SQL Server 中几个有用的特殊函数

在SQL Server 的使用过程中,发现几个很有用,但不太常用(或细节不太清楚)的函数(存储过程): isnumeric,isdate,patindex,newid,collate,sp_ex...
  • fengqingtao2008
  • fengqingtao2008
  • 2011年12月06日 19:09
  • 426

与 SQL Server Profiler 有关的几个存储过程和函数

---收藏,太有用了 这个月新系统正式上线,半个月来出现很多问题,麻烦!真是麻烦!麻烦不断啊!最要命的是公司的DBA因为需要参与新项目的设计居然把所有问题交我解决,自己一走了知。我本就是一懒人,什么...
  • leamonjxl
  • leamonjxl
  • 2012年03月29日 17:54
  • 924

C#编程中连接网络MySQL数据库,使用类似SQL Server的库函数,做简单修改即可

  • 2014年06月18日 22:42
  • 101KB
  • 下载

sql server 创建table表 及添加各种约束 查看约束 删除约束 新增表的字段 规则及规则使用方法及应用到表中 时间函数的类型

use Test1 go create table tb_person ( id int identity(1,1) primary key not null, --要把该列设置为主键列就用 prim...
  • WangBurn
  • WangBurn
  • 2015年08月13日 16:46
  • 796

SQL Server 聚集索引在函数中使用以至失效(案例)

最近在进行一期的优化,先把数据库中最耗时的前N个语句查询出来! SELECT TOP 10 qp.query_plan,qt.text,total_worker_time from sys.dm...
  • kk185800961
  • kk185800961
  • 2015年04月23日 03:08
  • 2274
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:几个非常有用SQL Server的函数(SQL Server的函数的使用)
举报原因:
原因补充:

(最多只允许输入30个字)