sqlserver函数大全

一旦成功地从表中检索出数据，就需要进一步操纵这些数据，以获得有用或有意义的结果。这些要求包括：执行计算与数学运算、转换数据、解析数值、组合值和聚合一个范围内的值等。 下表给出了 T-SQL 函数的类别和描述。 函数类别 作用 聚合函数 执行的操作是将

一旦成功地从表中检索出数据，就需要进一步操纵这些数据，以获得有用或有意义的结果。这些要求包括：执行计算与数学运算、转换数据、解析数值、组合值和聚合一个范围内的值等。

下表给出了T-SQL函数的类别和描述。

函数的组成

函数的目标是返回一个值。大多数函数都返回一个标量值(scalar value)，标量值代表一个数据单元或一个简单值。实际上，函数可以返回任何数据类型，包括表、游标等可返回完整的多行结果集的类型。本章不准备讨论到这个深度，第12章将讲解如何创建和使用用户自定义函数，以返回更复杂的数据。

函数己经存在很长时间了，它的历史比SQL还要长。在几乎所有的编程语言中，函数调用的方式都是相同的：

Result=Function()

在T-SQL中，一般用SELECT语句来返回值。如果需要从查询中返回一个值，就可以把SELECT当成输出运算符，而不用使用等号：

SELECT Function()

一个论点

对于SQL函数而言，参数表示输入变量或者值的占位符。函数可以有任意个参数，有些参数是必须的，而有些参数是可选的。可选参数通常被置于以逗号隔开的参数表的末尾，以便于在函数调用中去除不需要的参数。

在SQL Server在线图书或者在线帮助系统中，函数的可选参数用方括号表示。在下列的CONVERT()函数例子中，数据类型的length和style参数是可选的:

CONVERT (data-type [(length)], expression[,style])

可将它简化为如下形式，因为现在不讨论如何使用数据类型：

CONVERT(date_type, expression[,style])

根据上面的定义，CONVERT()函数可接受2个或3个参数。因此，下列两个例子都是正确的：

这个函数的第一个参数是数据类型Varchar(20)，第2个参数是另一个函数GETDATE()。GETDATE()函数用datetime数据类型将返回当前的系统日期和时间。第2条语句中的第3个参数决定了日期的样式。这个例子中的101指以mm/dd/yyyy格式返回日期。本章后面将详细介绍GETDATE()函数。即使函数不带参数或者不需要参数，调用这个函数时也需要写上一对括号，例如GETDATE()函数。注意在书中使用函数名引用函数时，一定要包含括号，因为这是一种标准形式。

确定性函数

由于数据库引擎的内部工作机制，SQL Server必须根据所谓的确定性，将函数分成两个不同的组。这不是一种新时代的信仰，只和能否根据其输入参数或执行对函数输出结果进行预测有关。如果函数的输出只与输入参数的值相关，而与其他外部因素无关，这个函数就是确定性函数。如果函数的输出基于环境条件，或者产生随机或者依赖结果的算法，这个函数就是非确定性的。例如，GETDATE()函数是非确定性函数，因为它不会两次返回相同的值。为什么要把看起来简单的事弄得如此复杂呢？主要原因是非确定性函数与全局变量不能在一些数据库编程对象中使用(如用户自定义函数)。部分原因是SQL Server缓存与预编译可执行对象的方式。例如，即席查询可以使用任何函数，不过如果打算构建先进的、可重用的编程对象，理解这种区别很重要。

以下这些函数是确定性的：

l AVG()(所有的聚合函数都是确定性的)

l CAST()

l CONVERT()

l DATEADD()

l DATEDIFF()

l ASCII()

l CHAR()

l SUBSTRING()

以下这些函数与变量是非确定性的：

l GETDATE()

l @@ERROR

l @@SERVICENAME

l CURSORSTATUS()

l RAND()

在函数中使用用户变量

变量既可用于输入，也可用于输出。在T-SQL中，用户变量以@符号开头，用于声明为特定的数据类型。可以使用SET或者SELECT语句给变量赋值。以下的例子用于将一个int类型的变量@MyNumber传递给SQRT()函数：

结果是12，即144的平方根。

用SET给变量赋值

以下例子使用另一个int型的变量@MyResult，来捕获该函数的返回值。这个技术类似于过程式编程语言中的函数调用样式，即把SET语句和一个表达式结合起来，给参数赋值：

用SELECT给变量赋值

使用SELECT的另一种形式也可以获得同样的结果。对变量要在赋值前要先声明。使用SELECT语句来替代SET命令的主要优点是，可以在一个操作内同时给多个变量赋值。执行下面的SELECT语句，通过SELECT语句赋值的变量就可以用于任何操作了。

上面的例子首先声明了4个变量，然后用两个SELECT语句给这些变量赋值，而不是用4个SELECT语句给变量赋值。虽然这些技术在功能上是相同的，但是在服务器的资源耗费上，用一个SELECT语句给多个变量赋值一般比用多个SET命令的效率要高。将一个甚至多个值选进参数的限制是，对变量的赋值不能和数据检索操作同时进行。这就是上面的例子使用SELECT语句来填充变量，而用另外一个SELECT语句来检索变量中数据的原因。例如，下面的脚本就不能工作:

这个脚本会产生如下错误:

在查询中使用函数

函数经常和查询表达式结合使用来修改列值。这只需将列名作为参数传递给函数即可，随后函数将引用插入到SELECT查询的列的列表中，如下所示：

在这个例子中，BirthDate列的值被作为参数传递给YEAR()函数。函数的结果是别名为BirthYear的列。

嵌套函数

我们需要的功能常常不能仅由一个函数来实现。根据设计，函数应尽量简单，用于提供特定的功能。如果一个函数要执行许多不同的操作，就变得复杂和难以使用。因此，每个函数通常仅执行一个操作，要实现所有的功能，可以将一个函数的返回值传递给另一个函数，这称为嵌套函数调用。

以下是一个简单的例子：GETDATE()函数的作用是返回当前的日期与时间，但不能返回经过格式化的数据，因为这是CONVERT()函数的功能。要想同时使用这两个函数，可以把GETDATE()函数的输出作为CONVERT()函数的输入参数。

聚合函数

报表的典型用途是从全部数据中提取出代表一种趋势的值或者汇总值，这就是聚合的意义。聚合函数回答数据使用者的如下问题:

上个月鸡雏的总销售量是多少?

19～24岁之间的巴西男性在食品调味品上的平均支出是多少?

上季度所有订单中从订购到运输的最长时间是多少?

收发室里仍在工作的最老的员工是谁?

聚合函数应用特定的聚合操作并返回一个标量值(单一值)。返回的数据类型对应于该列或者传递到函数中的值。聚合经常和分组、累积以及透视等表运算一起使用，生成数据分析结果。第7章将详细介绍这个主题，这里仅讨论简单SELECT查询中的一些常用函数。

聚合函数不仅可用在SELECT查询中，还可以和标量输入值一起使用。那么，这样做的意义是什么呢？在下列代码中，将值15传递给下列聚合函数，每个函数的返回值都相同：

它们都返回15。虽然，对同一个值求平均、求和、求最小值、求最大值，所得的结果还是那个值。如果对一个值计数，又会产生什么结果呢？

SELECT COUNT(15)

得到的值是1，因为函数只计数了一个值。

现在做一些有意义的事。聚合函数只有在处理结果集合中的一组数据时才有意义。每个函数都处理某列的非空值。除非使用分组操作(详见第7章)，否则不能在同一个SELECT语句中既返回聚合的值，又返回常规的列值。

AVG()函数

AVG()函数用于返回一组数值中所有非空数值的平均值。例如，表6-2包含了体操成绩。

表 6-2

对这些数据执行以下查询:

SELECT AVG(Score)

结果是8.965。

如果有三个女孩没有完成一些项目，在表中没有记录成绩，则可用NULL来表示(见表6-3)。

表 6-3

脚本：

在这种情况下，计算平均值时只考虑实际的数值，NULL不参与运算，结果是8.921429。 但是，如果把缺少的成绩也算在内，即用数值0代替NULL，则会严重影响最终成绩(6.245)，她们能不能进入国家级的比赛就难说了。

COUNT()函数

COUNT()函数用于返回一个列内所有非空值的个数，这是一个整型值。比如，在上一个例子中，体操数据被保存在#GymEvent表中，要确定Sammi参加的项目数，则可以执行下列查询：

SELECT COUNT(Score) FROM #GymEvent WHERE Player='Sammi'

结果是1，因为Sammi只参加了跳马比赛，她的平衡木成绩是NULL。

如果需要确定表中的行数，无论这些行是不是NULL值，都可以使用以下语法：

SELECT COUNT (*) FROM #GymEvent

以Sammi为例，COUNT(*)查询如下所示:

SELECT COUNT(*) FROM #GymEvent WHERE Player='Sammi'

由于COUNT(*)函数会忽略NULL值，所以这个查询的结果是2。

MIN()与MAX()函数

MIN()函数用于返回一个列范围内的最小非空值；MAX()函数用于返回最大值。这两个函数可以用于大多数的数据类型，返回的值根据对不同数据类型的排序规则而定。为了说明这两个函数，假设有一个表包含了两列值，一列是整型值，另一列是字符型值，如表6-4所示。

表 6-4

脚本：

如果分别调用MIN()与MAX()函数将会返回什么值呢？

因为VarCharColumn中值的存储类型为字符类型，而不是数字，所以结果以每个字符的ASCII值为顺序从左到右排序。这就是12比其他值小、而4比其他值大的原因。

SUM()函数

SUM()函数是最常用的聚合函数之一，它的功能很容易理解：和AVG()函数一样，它用于数值数据类型，返回一个列范围内所有非空值的总和。

配置变量

配置变量不是函数，不过它们的用法和系统函数相同。每个全局变量都能够返回SQL Server执行环境的标量信息。以下是一些常见的例子。

@@ERROR变量

这个变量包含当前连接发生的最后一次错误的代码。在执行的语句没有错误时，@@ERROR变量的值是0。出现标准错误时，错误是由数据库引擎引发的。所有的标准错误代码与消息都保存在sys.messages系统视图中，可以使用如下脚本查询：

SELECT * FROM sys.messages

定制错误可以通过调用RAISERROR语句来手动引发，并调用sp_addmessage系统存储过程将其添加到sysmessages表中。

以下是一个@@ERROR变量的简单例子。先试着将一个数除以0，数据库引擎会引发标准错误号为8134的错误。注意查看Results选项卡中的查询结果。在发生错误时，Management Studio的Messages选项卡将默认显示在Results选项卡的上面：

在成功检索@@ERROR的值后，@@ERROR的值将返回0，因为@@ERROR只保存了上次执行的语句的错误代码。如果希望检索更多的错误信息，可以使用如下脚本从sysmessages视图中得到：

本节的后面部分内容将说明如何通过使用错误函数来更高效地返回错误数据。

除了美国英语之外，SQL Server还默认安装了其他语言。每种语言专用的错误消息都有一个语言标识符(mslangid)，对应于syslanguages表中的一种语言，如下图所示。

属性名mslangid被非正式地定义为Microsoft Global Language Identifier。微软公司用这个标识符来标识一种语言或语言和国家的组合，微软公司把语言和国家的组合定义为地区。例如，在随SQL Server安装的英语中，美国英语的mslangid是1033，英国英语的mslangid是2057。要检索出所有已安装的、支持的语言，可以执行下面的查询：

@@SERVICENAME变量

这个变量是用于执行和维护当前SQL Server实例的Windows服务名。它通常返回SQL Server默认实例MSSQLSERVER，但SQL Server的指定实例有唯一的服务名。例如在名为WoodVista的计算机上有两个SQL Server实例：默认实例和指定实例AughtEight。如在默认实例上检索@@SERVICENAME全局变量的内容，将返回MSSQLSERVER，但在指定实例上检索，会返回AUGHTEIGHT。

@@TOTAL_ERRORS变量

这个变量用于记录从打开当前连接开始发生的总错误次数。和@@ERROR变量一样，它对每个用户会话是唯一的，并将在连接关闭时被重置。

@@TOTAL_READ变量

这个变量记录从打开当前连接时开始计算的磁盘读取总数。DBA使用这个变量查看磁盘读取活动的情况。

@@VERSION变量

这个变量包含当前SQL Server实例的完整版本信息。

SELECT @@VERSION

比如，对于运行在Windows 7上的SQL Server 2008开发版实例，以上脚本能够返回如下信息:

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition on Windows NT 6.1 (Build 7600: )

实际的版本号是一个简单的整型值，它在微软公司内部使用。而发行的产品可能有其他的商标名。在本例中，SQL Server 2005的版本是9，SQL Server 2008的版本是10。Windows XP Professional显示为Windows NT 5.l版，而Vista显示为6.0版。构建号用于内部控制，反映beta版和预览版以及正式发行后的补丁包的变化。

错误函数

前面学习了如何使用@@ERROR全局变量来检索错误信息。而返回所有错误数据的更好方法是使用错误函数。这些函数返回的信息可以存储在错误跟踪表中，以供错误审核。错误函数嵌套在错误处理例程中。第11章将详细讨论错误处理，其实通过使用嵌套在TRY和END TRY语句中的代码块，后跟一个放在CATCH和END CATCH语句中的代码块就可以实现错误处理。

所谓的错误捕获，其实就是这个意思。如果运行上面的示例，将不会出现可识别的错误，因为错误将被捕获并在CATCH语句块中进行处理。在编写错误处理代码时，SQL程序员必须把这些代码放在会引发系统错误的catch代码块中。

下列几个错误函数用于返回错误的特定信息：

下表简要描述了严重级别。

下面脚本使用T-SQL的内置错误处理功能，来捕获和输出遇到除0错误时返回的错误数据。SELECT命令的结果将显示在Management Studio的消息选项卡中。

可以看出，执行这个脚本会在消息选项卡中返回有关错误的更多详细信息，而不仅仅是错误号本身。

ERROR_PROCEDURE()函数不能返回过程名，因为错误是在ad-hoc查询中生成的。

转换函数

数据类型转换可以通过CAST()和CONVERT()函数来实现。大多数情况下，这两个函数是重叠的，它们反映了SQL语言的演化历史。这两个函数的功能相似，不过它们的语法不同。虽然并非所有类型的值都能转变为其他数据类型，但总的来说，任何可以转换的值都可以用简单的函数实现转换。

CAST()函数

CAST()函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型。以下例子用于将文本字符串'123'转换为整型:

SELECT CAST('123' AS int)

返回值是整型值123。如果试图将一个代表小数的字符串转换为整型值，又会出现什么情况呢？

SELECT CAST('123.4' AS int)

CAST()函数和CONVERT()函数都不能执行四舍五入或截断操作。由于123.4不能用int数据类型来表示，所以对这个函数调用将产生一个错误:

Server: Msg 245, Level 16, State 1, Line 1

Syntax error converting the varchar value

'123.4' to a column of data type int.

在将varchar 值'123.4' 转换成数据类型int 时失败。

要返回一个合法的数值，就必须使用能处理这个值的数据类型。对于这个例子，存在多个可用的数据类型。如果通过CAST()函数将这个值转换为decimal类型，需要首先定义decimal值的精度与小数位数。在本例中，精度与小数位数分别为9与2。精度是总的数字位数，包括小数点左边和右边位数的总和。而小数位数是小数点右边的位数。这表示本例能够支持的最大的整数值是9999999，而最小的小数是0.01。

SELECT CAST('123.4' AS decimal(9,2))

decimal数据类型在结果网格中将显示有效小数位:123.40

精度和小数位数的默认值分别是18与0。如果在decimal类型中不提供这两个值，SQL Server将截断数字的小数部分，而不会产生错误。

SELECT CAST('123.4' AS decimal)

结果是一个整数值：123

在表的数据中转换数据类型是很简单的。下面的例子使用Product表，首先执行如下查询：

假定产品经理已经创建了一个系统，用于唯一地标识生产出来的每辆自行车，以便跟踪其型号、类型和类别。他决定合并产品号、产品生产线标识符、产品型号标识符和一个顺序号，为生产出来的每辆自行车创建一个唯一的序列号。在这个过程的第一步，他要求提供包括除顺序号之外的所有属性的所有可能产品的根标识符。

如果使用下面的表达式，就不能得到希望的结果，如图6-2所示。

我们没有得到希望的结果，而得到了有点奇怪的错误消息：请把nvarchar值转换为int。因为之前我们没有要求进行任何转换，所以这个错误很奇怪。这个查询的问题在于我们试图利用第一个连接符来连接字符值ProductNumber，利用第二个连接符连接另一个字符值ProductLine，最后连接的是ProductModelID字符值(它是一个整数)。

查询引擎会把连接符当成一个数学运算符，而不是一个字符。不管结果是什么，都需要更正这个表达式，以确保使用正确的数据类型。以下表达式执行了必要的类型转换，返回如图6-3所示的结果：

如果把整型值转换为字符类型就不会增加多余的空格了。查询引擎将把这些值用加号和连接符组合在一起，进行字符串连接运算，而不是和前面的数值进行加法或者减法运算了。

CONVERT()函数

对于简单类型转换，CONVERT()函数和CAST()函数的功能相同，只是语法不同。CAST()函数一般更容易使用，其功能也更简单。CONVERT()函数的优点是可以格式化日期和数值，它需要两个参数：第1个是目标数据类型，第2个是源数据。以下的两个例子和上一节的例子类似：

CONVERT()函数还具有一些改进的功能，它可以返回经过格式化的字符串值，且可以把日期值格式化成很多形式。有28种预定义的符合各种国际和特殊要求的日期与时间输出格式。下表列出了这些日期格式。

如果 expression 为 date 或 time 数据类型，则 style 可以为下表中显示的值之一。其他值作为 0 进行处理。SQL Server 使用科威特算法来支持阿拉伯样式的日期格式。

1. 这些样式值将返回不确定的结果。包括所有 (yy)（不带世纪数位）样式和一部分 (yyyy)（带世纪数位）样式。

2. 默认值（style 0 或 100、9 或 109、13 或 113、20 或 120 以及 21 或 121）始终返回世纪数位 (yyyy)。

3. 转换为 datetime 时输入；转换为字符数据时输出。

4. 为用于 XML 而设计。对于从 datetime 或 smalldatetime 到字符数据的转换，其输出格式如上一个表所述。

5. 回历是有多种变体的日历系统。SQL Server 使用科威特算法。

a) 默认情况下，SQL Server 基于截止年份 2049 年来解释两位数的年份。换言之，就是将两位数的年份 49 解释为 2049，将两位数的年份 50 解释为 1950。许多客户端应用程序（如基于自动化对象的应用程序）都使用截止年份 2030 年。SQL Server 提供了“两位数年份截止”配置选项，可通过此选项更改 SQL Server 使用的截止年份，从而对日期进行一致处理。建议您指定四位数年份。

6. 仅支持从字符数据转换为 datetime 或 smalldatetime。仅表示日期或时间成分的字符数据转换为 datetime 或 smalldatetime 数据类型时，未指定的时间成分设置为 00:00:00.000，未指定的日期成分设置为 1900-01-01。

7. 使用可选的时间区域指示符 (Z) 更便于将具有时区信息的 XML datetime 值映射到没有时区的 SQL Server datetime 值。Z 是时区 UTC-0 的指示符。其他时区则以 + 或 - 方向的 HH:MM 偏移量来指示。例如：2006-12-12T23:45:12-08:00。

从 smalldatetime 转换为字符数据时，包含秒或毫秒的样式将在这些位置上显示零。使用相应的 char 或 varchar 数据类型长度从 datetime 或 smalldatetime 值转换时，可截断不需要的日期部分。

从样式包含时间的字符数据转换为 datetimeoffset 时，将在结果末尾追加时区偏移量。

这个函数的第三个参数是可选的，该参数用于接收格式代码整型值。表中的例子用于对DateTime数据类型进行转换。在转换SmallDateTime数据类型时，格式不变，但一些元素会显示为0，因为该数据类型不支持毫秒。以下的脚本例子将输出格式化的日期：

SELECT 'Default Date:' + CONVERT(Varchar(50), GETDATE(), 100)

Default Date: Apr 25 2005 1:05PM

SELECT 'US Date:' + CONVERT(Varchar(50), GETDATE(), 101)

US Date: 04/25/2005

SELECT 'ANSI Date:' + CONVERT(Varchar(50), GETDATE(), 103)

ANSI Date: 2005.04.25

SELECT 'UK/French Date:' +CONVERT (Varchar(50), GETDATE(), 103)

UK/French Date: 25/04/2OO5

SELECT 'German Date:' + CONVERT(Varchar(50), GETDATE(), 104)

German Date: 25.04.2005

格式代码0，1和2也可用于数字类型，它们对小数与千位分隔符格式产生影响。而不同的数据类型所受的影响是不一样的。一般来说，使用格式代码0(或者不指定这个参数的值)，将返回该数据类型最惯用的格式。使用1或者2通常显示更为详细或者更精确的值。以下例子使用格式代码0：

返回结果如下:

1234.56

使用值1则返回如下结果:

1,234.56

使用值2则返回如下结果:

1234.5600

以下例子和上例相同，但是使用Float类型:

使用值0不会改变所提供的格式，但是使用值1或2将返回以科学计数法表示的数字，后者使用了15位小数：

1.23456000000000e+003

STR()函数

这是一个将数字转换为字符串的快捷函数。这个函数有3个参数：数值、总长度和小数位数。如果数字的整数位数和小数位数(要加上小数点占用的一个字符)的总和小于总长度，对结果中左边的字符将用空格填充。在下面第1个例子中，包括小数点在内一共是5个字符。结果显示在网格中，显然左边的空格被填充了。这个调用指定，总长度为8个字符，小数位为4位：

SELECT STR(123.4, 8, 4)

结果值的右边以0填充：123.4000。

下面给函数传递了一个10字符的值，并指定结果包含8个字符，有4个小数位：

SELECT STR(123.456789, 8,