6.1数学函数

6.2字符串函数

6.3日期函数

6.4转换函数

6.5判定函数

6.6用户自定义函数

第六章 使用函数辅助查询

6.1数学函数

1 、 ABS(numeric_expression)

该函数返回表达式值（ bit 型除外）的绝对值，返回值的数据类型与原数据类型一致。例如：SELECT ABS(-3.0), ABS(2.0),

ABS(0.0)

该语句执行后在查询窗口的结果子窗口中，点击网格标签将看到以下返回结果：3.0 2.0 .0

点击消息标签将看到以下消息：/*----SELECT ABS(-3.0), ABS(2.0),

ABS(0.0)--*/

（所影响的行数为 1 行，所影响的行数指输出结果的行数。）2 、 AVG([ALL | DISTINCT]numeri

c_expression)

该函数返回查询出的一组数据的平均值。例如：SELECT AVG( 积分 )

FROM jsy

WHERE 所学专业 =' 汽车指挥 '

返回结果为： 26.833333

3 、 COUNT([ALL | DISTINCT]exp

ression | * )

该函数返回查询出的表达式数据的个数。例如：

SELECT COUNT （ * ）FROM cl

WHERE 类别 =‘ 小轿车’返回结果为： 3

4 、 CEILING(numeric_expressio

n)

　　该函数返回最小的大于或等于表达式值的整数值。返回值的数据类型

与原数据类型一致。例如：SELECT CEILING($99.99),

CEILING($-99.99) ,CEILING($0.0)

返回结果为： 100.0000 -99.00

00 .0000

5 、 FLOOR(numeric_expression)

　　该函数返回最大的小于或等于表

达式值的整数值。返回值的数据类型与原数据类型一致。例如：

SELECT FLOOR($99.99),

FLOOR($-99.99) ,FLOOR($0.0)

返回结果为： 99.0000 -100.0

000 .0000

6 、 RAND([integer_expression])

　　该函数返回一个位于 0 与 1 之间的随机数。表达式值作为产生随机数的起始值，返回值为浮点型数。例如：

DECLARE @number smallint

SET @number=1

WHILE (@number<=3)

BEGIN

　　 SELECT

　　　　　　 RAND(@number)

SET

　　　　　 @number=@number+

1

END

GO

返回结果为0.71359199321292355

0.7136106261841817

0.71362925915543995

7 、 ROUND(numeric_expression,int

_

expression1,

[integer_expression2])

　　该函数无整数表达式 2 时，返回由整数表达式 1 表示的精度对数值型表达进行四舍五入后的值；有整数表达式 2 （非零）时，表示用整数表达式 1 表示的精度对数值型表达式进行截断。例如：

SELECT ROUND （ 2456.12

582,3 ）返回结果为 2456.12600 。下面语句：SELECT ROUND （ 2456.12582,3,

1 ）返回值为 2456.12500 。

下面语句：

SELECT ROUND （ 2456.12

582,-3 ）

返回值为 2000.00000 。

6.2字符串函数1 、 ASCII(chracter_expression)

　　该函数返回字符的 ASCII 码值，返回值为整型数据。例如：

SELECT ASCII(‘a’), ASCII(‘Z’)

返回结果为： 97 90

2 、 CHAR （ inter_expression ）

　　该函数返回 ASCII 码值代表的字

符。例如：SELECT CHAR(97), ASCII(90)

返回结果为： a Z

3 、 LEN(charater_expression)

　　该函数返回字符串的长度，即字符的个数， 1 个汉字计为一个字符。

例如：SELECT LEN （姓名）

FROM jsy

WHERE 驾照号 =‘002011’

返回结果为： 2

该例计算驾照号为‘ 002011’ 的驾驶员

的姓名字段值的长度，注意 LEN 函数

计算字符长度时不包括字符尾部的空格。4 、 DATALENGTH(expression)

　　该函数返回表达式值所占用的字节数，常用于查看变长数据类型的长度。例如：

SELECT

DATALENGTH(‘100’),DATALENG

TH(100)

返回结果为： 3 4

而下面语句：SELECT DATALENGTH( 姓

名 )

FROM jsy

WHERE 驾照号 ='002011'

返回结果为： 4

　　这是因为‘ 100’ 为字符，长度为 3

字节， 100 为数值，数据类型默认为

int 型，占用为 4 字节。表 jsy 中驾照

号为‘ 002011’ 的驾驶员姓名列的长度

为 4 字节。

5 、 LEFT(chracter_expression,in

teger_expression)

　　该函数返回字符串从左边开始指定个数的字符。例如：

SELECT LEFT( 姓名， )+LEFT

　　　　　　　　（职务， 3 ）FROM ddy

返回结果为： 林队长 　　　　　　孙副队长　　　　　　刘调度员

6 、 RIGHT(chracter_expression,

integer_expression)

　　该函数返回字符串从右边开始指定个数的字符。例如：SELECT RIGHT( 驾照号， 4), 姓名

FROM jsy

WHERE 籍贯 = ‘ 北京’返回结果为： 姓名

2012 高兵0114 林水强

7 、 SUBSTRING(chracter_express

ion,

begin_integer_expression,

lenth_integer_expression )

　　该函数返回字符串在起始位置开

始的指定长度的子串。例如：SELECT SUBSTRING('traffic',3,4)

返回结果为： affi

8 、 UPPER(chracter_expression)

　　该函数返回字符的大写形式。9 、 LOWER(chracter_expressio

n)

　　该函数返回字符的小写形式。

10 、 SPACE(integer_expression)

　　该函数返回指定长度的空格字符串。例如：SELECT 姓名 +SPACE(6)+ 职务

FROM ddy

WHERE 调度号 =’0111’

返回结果为：林强 队长

11 、 REPLICATE(chracter_expre

ssio

n,integer_expression)

　　该函数将字符串复制指定的遍数。例如：

SELECT REPLICATE(‘SQL’,3)

返回结果为： SQLSQLSQL

12 、 STUFF(chracter_expression

1, begin_integer_expre

ssion, end_integer_ex

pression,

chracter_expression2)

　　该函数将字符串 1 从开始位置到结束位置中的字符删去然后将字符串2 填充进去。例如：

SELECT STUFF(‘SQlver’,2,2, ‘L

Ser’)

返回结果为： SQL Server

13 、 REVERSE(chracter_express

ion)

　　该函数返回字符串的反序字符串。例如：

SELECT REVERSE(‘SQL’)

返回结果为： LQS

14 、 LTRIM(chracter_expressio

n)

　　该函数返回删除字符串左端空格后的字符串。例如：

SELECT LEN(‘SQL‘) AS ‘ 原字符长’ , LEN(LTRIM(‘SQL‘)) AS ‘

现字符长’返回结果为：原字符长 现字符长

6 3

15 、 RTRIM(chracter_expressio

n)

　　该函数返回删除字符串右端空格后的字符串。例如：SELECT

'SQL',RTRIM('SQL'),'SQL'

返回结果为： SQL SQL

SQL

16 、 STR(float_expression[,

integer_expression1[,

integer_expression2]])

　　该函数返回浮点表达式值的字符

串形式。整数表达式 1 为字符串长度，整数表达式 2 为小数位数。若无整数表达式 2 ，默认为 0 ；若无整数表达式 1 ，默认为浮点数值的整数部分长度。例如：

SELECT STR(123.456),

STR(123.456,4,1),STR(123.456,6,

4)

返回结果为： 123 123123.46

6.3日期串函数

1 、 GETDATE()

　　该函数返回当前系统日期时间。例如：

SELECT GETDATE()

返回结果： 2003-05-27

02:56:33.120

2 、 DATEPART(datepart,date_ex

pression)

　　该函数返回日期表达式值的指定

部分，返回值为数值型数据。表 6-1

为 date 型数据日期部分的取值。

datepart 　缩 写 　 　说 明year yy, yyyy 年quarter qq, q 季度month mm, m 月dayof year dy, y 一年中的第几天day dd, d 一月中的第几天week wk, ww 一个星期中的第几天hour hh 小时minute mi, n 分second ss, s 秒millisecond

ms 千分之一秒

例如：SELECT 姓名 , DATEPART(yy, 出生年月 ) AS’ 出生年’ ,

DATEPART(mm, 出生年月 ) AS

‘ 出生月’

FROM jsy

WHERE 驾照号 =’002011’

返回结果：姓名 出生年 出生月王明 1983 12

3 、 DATENAME(datepart,date_expr

es

sion)

　　该函数返回日期表达式值的指定部分的名称，返回值为字符型数据。

例如：SELECT 姓名 , DATENAME(ww,

GETDATE())

返回结果： Turday

4 、 DATEADD(datepart,

interge_expression,

date_expression )

　　该函数返回日期表达式值的指定部分加上整数表达式值后的日期时间。例如：SELECT DATEADD(day, 10,

GETDATE())

返回结果： 2004-01-04 12:41:51.56

0

5 、 DATEDIFF(datepart,date_exp

re

ssion1, date_expression2)

　　该函数返回日期表达式 1 的值和日期表达式 2 的值在指定部分的差值。例如：

SELECT 姓名 ,

DATEDIFF(year, 出生年月 ,GETDATE()) AS ‘ 年龄’

FROM jsy

WHERE 驾照号 =’002011’

返回结果：姓名 年龄王明 20

6 、 DAY(date_expression)

　　该函数返回日期表达式值的“日”

部分。例如：SELECT DAY(GETDATE())

返回结果： 4

7 、 MONTH(date_expression)

　　该函数返回日期表达式值的“月”

部分。例如：

SELECT MONTH(GETDATE())

返回结果： 1

8 、 YEAR(date_expression)

　　该函数返回日期表达式值的“年”

部分。例如：SELECT YEAR(GETDATE())

返回结果： 2004

6.4转换串函数

1 、 CAST(expression, AS

date_type)

　　将表达式值转换为指定的数据类型。例如：SELECT CAST('2002-5-27' AS

datetime)

返回结果： 2002-05-27 00:00:00.00

0

语句SELECT CAST(GETDATE() AS

char)

返回结果为： 12 25 2003 12:47PM

语句

SELECT CAST('123' AS real)

返回结果为： 123.0

　　再如，车辆表中启用年代字段的数据由两部分组成如‘ 89_2’ ，其中‘89’ 表示 89 年，‘ 2’ 表示第二季度。

现需将‘ 89_2’ 形式用‘ 89 年’表示，

下面语句可实现该数据转换。

SELECT 车牌号 , 类别 ,‘ 启用年'=CAST(LEFT( 启用年代 , 2) AS

char(2))+' 年 '

FROM cl

2 、 CONVERT(date_type[(lengt

h)],

expression[,style])

与 CAST 函数相似，只是 CONVER

T

中可以设定数据类型的长度和格式。表 6-2 列出日期型与字符型转换时style 的取值含义。例如语句

SELECT 　 CONVERT(char,

GETDATE(),101)

返回结果： 12/25/2003

style取值（无世纪值）

style取值（有世纪值）

标准 输入 /输出

0或 100 默认值 mm dd yyyy hh:miAM(或 )PM

1 101 美国 mm/dd/yyyy2 102 ANSI mm dd yyyy h

h:miAM(或 )PM

9或 109 默认值＋毫秒

mm-dd-yy

10 110 美国 yymmdd12 112 ISO

3 、 CASE 函数　　 CASE 函数有两种即简单 CAS

E

函数和搜索 CASE 函数，其函数格式

分别为：CASE 　 input_expression

WHEN when_expression

THEN

result_expression

[…n]

[ELSE else_result_expression]

END

和CASE

WHEN boolean_expression

THEN

result_expression

[…n]

[ELSE else_resultexpression]

END

　　该函数功能是根据判定条件返回相应的结果表达式值，使用该函数可将数据值转换成其他形式。

6.5判定函数

1 、 ISDATE(expression)

　　该函数判断表达式是否为一个合法的日期型数据，是则返回 1 ，否则返回 0 。2 、 ISNUMERIC(expression)

　　该函数判断表达式是否为一个合

法的数值型数据（包括整数型、数值型和浮点型），是则返回 1 ，否则返回 0 。3 、 SNULL(expression1,expressi

o

n2)

　　该函数判断表达式 1 的值是否为NULL ，是则返回表达式 2 的值，不

是则返回表达式 1 的值。例如：　　　 SELECT 主驾，出车单号，ISNULL( 副驾，’未配’ )

FROM xc

返回结果：主驾 出车单号 副驾

002011 7003 010111

002011 7012 未配

002012 7013 未配 …… …… ……

4 、 NULLIF(expression1,expression

2)

　　该函数判断表达式 1 的值是否与表达式 2 的值相等，是则返回 NULL ，否则返回表达式 1 的值。例如：

SELECT NULLIF(‘AB’,’AB’)

返回结果为： NULL

语句SELECT NULLIF( 启用年代 ,2001)

FROM cl

WHERE 车牌号 =’AX1320’

返回结果： 1989

6.6用户自定义函数

1 、标量函数的定义标量函数定义的基本格式为：CREATE FUNCTION [ 所有者名 .]

函数名 ([{ 形式参数 [AS] 类型 [= 默认值 ]}[,…n]])RETURNS 返回值类型

[AS]

一、用户函数的定义

BEGIN

函数体 RETURN 标量表达式

END

参数说明：　　形式参数的数据类型为系统的基本标量类型，不能为 timestamp 类

型、用户定义数据类型和非标量类型（如 cursor 和 table ）。返回值类型为系统的基本标量类型，但 text 、 ntext 、 image 和timestamp 除外。函数体由 T-SQL 语句序列构成。函数返回标量表达式的值。

下面举例说明标量函数的定义。

〖例 6-1〗定义一个函数用于计

算指定专业的平均积分。

CREATE FUNCTION aver

age

(@spec char(10)) RETURNS int

AS

BEGIN

DECLARE @aver int

SELECT @aver=

(SELECT avg( 积分 )

　 FROM 　 jsy

　 WHERE 所学专业＝@spec

　 GROUP BY 所学专业 )

RETURN 　 @aver

END

GO

　　在分析查询器查询窗口中输入上面语句，并执行查询，在对象浏览器窗口中对应函数对象上单击鼠标右键在快捷菜单上选择“刷新”，可看到

函数 average() 对象的图标，如图 6-1 。

2 、表型函数的定义表型函数定义的语法格式为：CREATE FUNCTION [ 所有者名 .]

函数名 ([{ 形式参数 [AS] 类型 [= 默认值 ]}[,…n]])

RETURNS TABLE

[AS]

RETURN [(select 语句 )]

参数说明：　　形式参数的数据类型为系统的基本标量类型，不能为 timestamp 类型、用户定义数据类型和非标量类型（如 cursor 和 table ）。TABLE关健字指定此函数返回一个

表。函数返回 select 语句的结果。下面创建一个表型函数 havejsy ，它

输入变量为城市名称，其返回值为来自该城市的所有驾驶员姓名和驾照号。〖例 6-2〗定义一个函数，用于查询

来自指定城市驾驶员姓名和驾照号。CREATE FUNCTION havejs

y (@come char(10)) RETURNS T

ABLE

AS

RETURN

( SELECT 姓名，驾照号

FROM 　 jsy

　　 WHERE 籍贯＝@come ） GO

　　在分析查询器的查询窗口中输入上面语句，并执行查询，在对象浏览器窗口中对应的函数对象上单击鼠标右键，在快捷菜单上选择“刷新”，

可看到函数 havejsy() 对象的图标。

3 、企业定理器中创建自定义函数

　　第 1步　打开企业管理器，层层

展开树状结构，选择“用户自定义函

数”对象，单击鼠标右键，在快捷菜

单上选择“新建用户定义函数”，如

图

6-2 。

　　第 2步　在打开的用户函数定义

对话框中输入相应内容，单击“检查

语法”按钮，如有错误则修改，直至

语法检查成功，单击“确定”，如图

6-3 和图 6-4 。

　　在企业管理器的右边窗口出现新

建的用户定义函数 average()图标，

如图 6-5 。

二、用户函数的调用　　当调用用户自定义函数时，必须提供函数名和参数，标量函数可以在SELECT 语句中调用，或用 EXEC

语句执行调用，调用形式分别为：所有者名 . 函数名（实参 1, 实参 2,

…

实参 n ）

EXEC 所有者名 . 函数名 形参 1= 实

参

1, 形参 2= 实参 2 ,… 形参 n= 实参

n

　　其中用 EXEC 语句调用时参数

次

序可与定义时不同，表型函数只能通

过 SELECT 语句调用。

　　例如调用 average() 函数查询

‘汽

车管理’专业的平均积分，在分析查

询器的查询窗口输入下面语句：

SELECT dbo.average (‘ 汽车

管理’ )

执行结果如图 6-6 。

　　例如调用 havejsy() 函数查询所

有来自天津的驾驶员姓名和驾照号，

在分析查询器中输入下面语句：

SELECT 　 *

FROM dbo.havejsy(‘天津’ )

执行结果如图 6-7 。

三、用户函数的修改和删除　　可有两种方法删除用户已定义的函数即用命令方式和界面方式，命令格式为：

DROP 　 FUNCTION{[owner

_name]functio

n_name}[,…n]

参数说明：owner_name 为所有者名。function_name 为用户自定义函数

名。n 表示可一次删除多个用户函数。如删除用户函数 average() 和havejsy() ，输入下面语句：

DROP 　 FUNCTION 　 dbo.aver

age,dbo.havejsy

　　在企业管理器中删除用户函数的

方法很简单，请自已练习。

本　章　小　结　　函数用以扩展数据查询功能，简化程序设计结构。函数作为语法元素，可以用在表达式中，也可以直接调用。使用函数要注意函数的参数类型、个数和返回值的特点。本章主要介绍了常用的 SQL 　 Server 系统

函

数，如数学函数、字符函数、日期函

数、转换函数和判定函数等不同函数

的功能，本章还介绍了用户自定义函

数的建立和调用方法，举例说明了标

量函数和表型函数的定义和应用。