sql server常用函数

1. 关于NULL的函数

• ISNULL函数和NULLIF函数

ISNULL(check_expression, replacement_value)
•check_expression 与 replacement_value 数据类型必须一致
•如果 check_expression 为 NULL，则返回 replacement_value
•如果 check_expression 不为 NULL，则返回 check_expression

NULLIF 用于检查两个表达式，语法：
NULLIF(expression, expression)

•如果两个 expression 相等，则返回 NULL，该 NULL 为第一个 expression 的数据类型
•如果两个 expression 不相等，则返回第一个 expression

• 除法的结果为0

• round

  1. 使用 Round() 函数，如 Round(@num,2) 参数2表示保留两位有效数字。

  2. 更好的方法是使用 Convert(decimal(18,2),@num) 实现转换，decimal(18,2) 指定要保留的有效数字。

这两个方法有一点不同：使用 Round() 函数，如果 @num 是常数，如 Round(2.3344,2) 则 会在把有效数字后面的 变为0 ，成 2.3300。但 Convert() 函数就不会。

1.日期时间函数

• GETDATE函数：返回当前系统日期和时间
• DAY，MONTH，YEAR函数：返回指定日期的日，月，年
• DATEDIFF函数：返回日期和时间的边界数
  语法格式：DATEDIFF（datepart，startdate，enddate）
• DATEADD函数：添加日期时间函数，

2.类型转换函数

• cast
  cast ( expression as data_type [ (length ) ])
• convert
  convert ( data_type [ ( length ) ] , expression [ , style ] )

length默认为30
style:数据格式的样式，用于将 datetime 或 smalldatetime 数据转换成字符数据（nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型），或将已知日期或时间格式的字符数据转换成 datetime 或 smalldatetime 数据；或者是字符串格式，用于将 float、real、money 或 smallmoney 数据转换成字符数据（nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型）。 如果 style 为 NULL，则返回的结果也为 NULL。

SELECT cast(GetDate() as nvarchar)      /*05 13 2016  2:47PM*/SELECT convert(nvarchar ,GetDate(),120) /*2016-05-13 14:47:50*/

3.元数据函数

元数据函数就是返回与数据库相关信息的函数，其描述了数据的结构和意义，
1. COL_LENGTH函数：返回列的定义长度
语法格式：COL_LENGTH（’table_name’,’column_name’）
2. COL_NAME函数：返回数据库列的名称
语法格式：COL_NAME（table_id，column_id）
3. DB_NAME函数：返回数据库名称
语法格式：DB_NAM（database_id）

OpenDataSource函数

语法：

OPENDATASOURCE ( provider_name, init_string )

provider_name
注册为用于访问数据源的 OLE DB 访问接口的 PROGID 的名称。 provider_name 的数据类型为 char，无默认值。
init_string
连接字符串，该字符串将要传递给目标提供程序的 IDataInitialize 接口。 提供程序字符串语法是以关键字值对为基础的，这些关键字值对由分号隔开，
例如：“keyword1=value;keyword2=value”。
下表列出了 init_string 参数中最常用的关键字。

示例：使用OpenDataSource函数读取excel的数据

接口类型
有两种接口可供选择：Microsoft.Jet.OLEDB.4.0（以下简称 Jet 引擎）和Microsoft.ACE.OLEDB.12.0（以下简称 ACE 引擎）。

Jet 引擎大家都很熟悉，可以访问 Office 97-2003，但不能访问 Office 2007。

ACE 引擎是随 Office 2007 一起发布的数据库连接组件，既可以访问 Office 2007，也可以访问 Office 97-2003。

另外：Microsoft.ACE.OLEDB.12.0 可以访问正在打开的 Excel 文件，而 Microsoft.Jet.OLEDB.4.0 是不可以的。

select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\2007.xlsx', 'select * from [Sheet1$]')select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:\97-2003.xls;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[Sheet1$]

REVERSE函数

REVERSE ( string_expression )
string_expression must be of a data type that is implicitly convertible to varchar.

DECLARE @a_name nvarchar(50)SET @a_name='guojing and huangrong'SELECT REVERSE(@a_name)return:gnorgnauh dna gnijoug

SUBSTRING函数

SUBSTRING ( expression ,start , length )

DECLARE @a_name nvarchar(50)SET @a_name='guojing and huangrong'SELECT SUBSTRING(@a_name,4,6)return:jing a

TAPINDEX函数

PATINDEX ( ‘%pattern%’ , expression )
返回模式在指定表达式中第一次出现的起始位置；如果在所有有效的文本和字符数据类型中都找不到该模式，则返回零。
pattern：包含要查找的序列的字符表达式。 可以使用通配符；但 pattern 之前和之后必须有 % 字符（搜索第一个或最后一个字符时除外）。 pattern 是字符串数据类型类别的表达式。
expression：是一个表达式，通常是针对指定模式搜索的列。 expression 属于字符串数据类型类别。

dense_RANK