[学习总结]SQL SERVER 2005常用系统函数

要高效的写出sql语句，经常需要用到系统函数，由于一直没有系统的学习过，编写复杂的sql时常会遇到困难，所以今天下午决定把sql server 2005的常用系统函数过了一边，顺便摘录下来:

SQL SERVER 2005常用系统函数

一、日期和时间函数

1. DATEADD (datepart , number, date )
返回给指定日期加上一个时间间隔后的新 datetime 值。
SELECT DATEADD(day, 21, OrderDate)AS TimeFrame
FROM Sales.SalesOrderHeader;

2. DATEDIFF (datepart , startdate , enddate )
返回跨两个指定日期的日期边界数和时间边界数。
SELECT DATEDIFF(day, OrderDate, GETDATE()) AS NumberOfDays
FROM Sales.SalesOrderHeader;

3. DATENAME ( datepart ,date ) 返回nvarchar
返回表示指定日期的指定日期部分的字符串。
SELECT DATENAME(month, GETDATE()) AS 'Month Name'

4. DATEPART ( datepart , date ) 返回int
返回表示指定日期的指定日期部分的整数。
SELECT DATEPART(month, GETDATE()) AS 'Month Number'

5. SELECT MONTH(0), DAY(0), YEAR(0)

二、字符串函数
1、SPACE ( integer_expression )
返回由重复的空格组成的字符串。

2、CHARINDEX ( expression1 ,expression2 [ , start_location ] )
返回字符串中指定表达式的开始位置。如果在 expression1 内找不到 expression2，则 CHARINDEX 返回 0。

3、REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' )
用第三个表达式替换第一个字符串表达式中出现的所有第二个指定字符串表达式的匹配项。
以下示例使用 xxx 替换 abcdefghi 中的字符串 cde。
SELECT REPLACE('abcdefghicde','cde','xxx');

4、STUFF ( character_expression , start , length ,character_expression )
删除指定长度的字符，并在指定的起点处插入另一组字符。
以下示例在第一个字符串 abcdef 中删除从第 2 个位置（字符 b）开始的三个字符，然后在删除的起始位置插入第二个字符串，从而创建并返回

一个字符串。
SELECT STUFF('abcdef', 2, 3, 'ijklmn')

5、LEFT ( character_expression , integer_expression )
返回字符串中从左边开始指定个数的字符。

6、RIGHT ( character_expression , integer_expression )
返回字符串中从右边开始指定个数的字符。

7、SUBSTRING ( expression ,start , length )
返回字符表达式、二进制表达式、文本表达式或图像表达式的一部分

8、LEN ( string_expression )
返回指定字符串表达式的字符（而不是字节）数，其中不包含尾随空格。

9、REVERSE ( character_expression )
返回字符表达式的逆向表达式。

10、LOWER ( character_expression )
将大写字符数据转换为小写字符数据后返回字符表达式。

11、UPPER ( character_expression )
返回小写字符数据转换为大写的字符表达式。

12、LTRIM ( character_expression )
返回删除了前导空格之后的字符表达式。

13、RTRIM ( character_expression )
截断所有尾随空格后返回一个字符串。

三、系统函数
1、case的两种用法
例1：
SELECT   ProductNumber, Category =
      CASE ProductLine
         WHEN 'R' THEN 'Road'
         WHEN 'M' THEN 'Mountain'
         WHEN 'T' THEN 'Touring'
         WHEN 'S' THEN 'Other sale items'
         ELSE 'Not for sale'
      END,
   Name
FROM Production.Product
ORDER BY ProductNumber;
例2：
SELECT   ProductNumber, Name, 'Price Range' =
      CASE
         WHEN ListPrice =  0 THEN 'Mfg item - not for resale'
         WHEN ListPrice < 50 THEN 'Under $50'
         WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
         WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
         ELSE 'Over $1000'
      END
FROM Production.Product
ORDER BY ProductNumber ;

2、@@ERROR
返回执行的上一个 Transact-SQL 语句的错误号。

3、@@IDENTITY
返回最后插入的标识值的系统函数。

4、IDENTITY (data_type [ , seed , increment ] ) AS column_name
只用于在带有 INTO table 子句的 SELECT 语句中将标识列插入到新表中。

5、ISNULL ( check_expression , replacement_value )
使用指定的替换值替换 NULL。

6、row_number() over(order by postid desc)
返回结果集分区内行的序列号，每个分区的第一行从 1 开始。

7、OBJECT_ID (
    '[ database_name . [ schema_name ] . | schema_name . ]     object_name' [ ,'object_type' ] )
返回架构范围内对象的数据库对象标识号。
查询表的标识：select object_id('dbo.tableName','u')
查询存储过程的标识:select object_id('dbo.procName','p')
查询视图的标识:select object_id('dbo.procName','v')