七色彩虹

勤奋、务实、团结、创新

导航

TSQL常用内置函数

Posted on 2010-11-24 17:31  aadfe  阅读(1208)  评论(0)  编辑  收藏  举报

转自:http://wangtong40.javaeye.com/blog/356944

7.    内置函数
7.1.    标量函数
?    系统函数
1、    Case
常用CASE用法

 

   1. SELECT Title,'Price Range' =  
   2.     CASE  
   3.         WHEN price IS NULL THEN 'Unpriced'  
   4.         WHEN price < 10 THEN 'Bargain'  
   5.         WHEN price BETWEEN 10 and 20 THEN 'Average'  
   6.         ELSE 'Gift to impress relatives'  
   7.         END  
   8.     FROM titles  ORDER BY price

CASE语句在GROUP BY使用 

   1. SELECT 'Number of Titles', Count(*)  
   2.     FROM titles  
   3.     GROUP BY  
   4.     CASE  
   5.         WHEN price IS NULL THEN 'Unpriced'  
   6.         WHEN price < 10 THEN 'Bargain'  
   7.         WHEN price BETWEEN 10 and 20 THEN 'Average'  
   8.         ELSE 'Gift to impress relatives'  
   9.     END

CASE语句在ORDER BY 使用 

   1. SELECT  
   2.          CASE  
   3.         WHEN price IS NULL THEN 'Unpriced'  
   4.         WHEN price < 10 THEN 'Bargain'  
   5.         WHEN price BETWEEN 10 and 20 THEN 'Average'  
   6.         ELSE 'Gift to impress relatives'  
   7.         END AS Range, Title  
   8.     FROM titles  
   9.    GROUP BY  
  10.     CASE  WHEN price IS NULL THEN 'Unpriced'  
  11.              WHEN price < 10 THEN 'Bargain'  
  12.                 WHEN price BETWEEN 10 and 20 THEN 'Average'  
  13.           ELSE 'Gift to impress relatives'  
  14.      END, Title  
  15. ORDER BY  
  16.     CASE  
  17.  WHEN price IS NULL THEN 'Unpriced'  
  18. WHEN price < 10 THEN 'Bargain'  
  19. WHEN price BETWEEN 10 and 20 THEN 'Average'  
  20.  ELSE  'Gift to impress relatives'  
  21. END, Title

注:为了在 GROUP BY 块中使用 CASE，查询语句需要在 GROUP BY 块中重复 SELECT 块中的 CASE 块。

 

2、    判断数据类型函数 

   1. ISDATE(expression) -- 是否是合法日期函数  
   2. ISNUMERIC(expression) -- 是否是合法数字  
   3. DATALENGTH(expression) -- 返回字段的字节数

3、处理空值函数 

   1. NULLIF(expression,expression)  
   2. 如果函数的两个表达式相等，返回空值，如果不等返回第一个表达式的值，通常在统计平均值时非常有用。  
   3.   
   4. ISNULL(check_expression,replacement_value) --当列值为空时，用其他数字代替  
   5. SELECT ISNULL(STATE_ID,0) FROM SETTLE_ENTER_RECORD  
   6.   
   7. COALESCE(expression)

4、    转换函数

 

   1. CONVERT(data_type,expression,[style])  
   2. SELECT CONVERT(varchar,ENTER_TIME) as dateTime FROM SETTLE_ENTER_RECORD  
   3. CAST(express AS data_type)  
   4. SELECT CAST(ENTER_TIME AS datetime) as dateTime FROM SETTLE_ENTER_RECORD  
   5. 注：CONVERT()和CAST()区别在于CONVERT()函数允许指定返回结果的格式。  
   6.        在不使用格式化功能时推荐使用CAST()函数，因为他是SQL92标准函数  

?    日期/时间函数
1、    获取系统时间
   1. GETDATE() -- 返回系统时间  




2、    获取部分日期/时间





   1. DAY(date) --日期  
   2. MONTH(date) -- 月份  
   3. YEAR(date) --年  
   4. DATEPART(datepart,date) --获取部分时期  
   5. select DATEPART(yyyy,enter_time) from SETTLE_ENTER_RECORD  




3、    日期/时间计算





   1. DATEADD(datepart,number,date) -- 给指定日期添加一个间隔时间数  
   2. SELECT DATEADD(mm,10,enter_time) FROM SETTLE_ENTER_RECORD  
   3. DATEDIFF(datepart,startdate,enddate) --起始到终止日期的间隔时间  
   4. SELECT DATEDIFF(dd,enter_time,getDATE()) FROM SETTLE_ENTER_RECORD  




?    字符串函数
1、    字符串长度





   1. LEN(expression) -- 字符串长度  
   2. SELECT LEN(car_type_id) FROM SETTLE_ENTER_RECORD  




2、    取子串3、    字符串位置








Sql代码 





    

  1. CHARINDEX(expression1,expression2) -- expression1在expression2的位置  
    2. 

    

  2. SELECT CHARINDEX('2',car_type_id) FROM SETTLE_ENTER_RECORD  
    3. 





CHARINDEX(expression1,expression2) -- expression1在expression2的位置SELECT CHARINDEX('2',car_type_id) FROM SETTLE_ENTER_RECORD


 



4、    删除空格










Sql代码 





    

  1. LTRIM(expression) --删除左边的空格  
    2. 

    

  2. RTRIM(expression) --删除右边的空格  
    3. 





LTRIM(expression) --删除左边的空格RTRIM(expression) --删除右边的空格


 



5、    大小写转换










Sql代码 





    

  1. UPPER(expression) --大写转换  
    2. 

    

  2. LOWER(expression) --小写转换  
    3. 





UPPER(expression) --大写转换LOWER(expression) --小写转换


 



6、    数值类型转换










Sql代码 





    

  1. STR(expression[,length]) -- 将数字类型转换为字符串类型  
    2. 

    

  2. SELECT STR(car_type_id,5) FROM SETTLE_ENTER_RECORD  
    3. 





STR(expression[,length]) -- 将数字类型转换为字符串类型SELECT STR(car_type_id,5) FROM SETTLE_ENTER_RECORD


 



7、    字符类型同ASCII/UNICODE转换










Sql代码 





    

  1. CHAR(expression)  
    2. 

    

  2. ASCII(expression)  
    3. 

    

  3. NCHAR(expression)  
    4. 

    

  4. UNICODE(expression)  
    5. 





CHAR(expression)ASCII(expression)NCHAR(expression)UNICODE(expression)


 


 




 




7.2.    聚合函数










Sql代码 





    

  1. MAX(expression)    --最大值  
    2. 

    

  2. MIN(expression) --最小值  
    3. 

    

  3. AVG(expression) --平均值  
    4. 

    

  4. SUM(expression) --总计  
    5. 





MAX(expression)    --最大值MIN(expression) --最小值AVG(expression) --平均值SUM(expression) --总计