SAL

  博客园  :: 首页  :: 新随笔  :: 订阅 订阅  :: 管理

sql server 常用函数

Posted on 2007-08-16 17:28  SAL  阅读(595)  评论(0编辑  收藏  举报

1.DATEADD
在向指定日期加上一段时间的基础上,返回新的 datetime 值。

语法
DATEADD ( datepart , number, date )

参数
datepart

是规定应向日期的哪一部分返回新值的参数。下表列出了 Microsoft? SQL Server? 识别的日期部分和缩
写。

日期部分     缩写
Year         yy, yyyy
quarter      qq, q
Month        mm, m
dayofyear    dy, y
Day          dd, d
Week         wk, ww
Hour         hh
minute       mi, n
second       ss, s
millisecond  ms


number

是用来增加 datepart 的值。如果指定一个不是整数的值,则将废弃此值的小数部分。例如,如果为
datepart 指定 day,为 number 指定 1.75,则 date 将增加 1。

date

是返回 datetime 或 smalldatetime 值或日期格式字符串的表达式。有关指定日期的更多信息,请参见
datetime 和 smalldatetime。

如果您只指定年份的最后两位数字,则小于或等于"两位数年份截止期"配置选项的值的最后两位数字的数字所
在世纪与截止年所在世纪相同。大于该选项的值的最后两位数字的数字所在世纪为截止年所在世纪的前一个世
纪。例如,如果 two digit year cutoff 为 2049(默认),则 49 被解释为 2049,2050 被解释为
1950。为避免模糊,请使用四位数的年份。

返回类型
返回 datetime,但如果 date 参数是 smalldatetime,返回 smalldatetime。

示例
此示例打印出 pubs 数据库中标题的时间结构的列表。此时间结构表示当前发布日期加上 21 天。

USE pubs
GO
SELECT DATEADD(day, 21, pubdate) AS timeframe
FROM titles
GO

下面是结果集:

timeframe                  
---------------------------
Jul 3 1991 12:00AM         
Jun 30 1991 12:00AM        
Jul 21 1991 12:00AM        
Jul 13 1991 12:00AM        
Jun 30 1991 12:00AM        
Jul 9 1991 12:00AM         
Mar 14 1997  5:09PM        
Jul 21 1991 12:00AM        
Jul 3 1994 12:00AM         
Mar 14 1997  5:09PM        
Nov 11 1991 12:00AM        
Jul 6 1991 12:00AM         
Oct 26 1991 12:00AM        
Jul 3 1991 12:00AM         
Jul 3 1991 12:00AM         
Nov 11 1991 12:00AM        
Jul 3 1991 12:00AM         
Jul 3 1991 12:00AM         

(18 row(s) affected)

 

2.DATEDIFF
返回跨两个指定日期的日期和时间边界数。

语法
DATEDIFF ( datepart , startdate , enddate )

参数
datepart

是规定了应在日期的哪一部分计算差额的参数。下表列出了 Microsoft? SQL Server? 识别的日期部分和缩
写。

日期部分    缩写
year        yy, yyyy
quarter     qq, q
Month       mm, m
dayofyear   dy, y
Day         dd, d
Week        wk, ww
Hour        hh
minute      mi, n
second      ss, s
millisecond ms


startdate

是计算的开始日期。startdate 是返回 datetime 或 smalldatetime 值或日期格式字符串的表达式。

因为 smalldatetime 只精确到分钟,所以当用 smalldatetime 值时,秒和毫秒总是 0。

如果您只指定年份的最后两位数字,则小于或等于"两位数年份截止期"配置选项的值的最后两位数字的数字所
在世纪与截止年所在世纪相同。大于该选项的值的最后两位数字的数字所在世纪为截止年所在世纪的前一个世
纪。例如,如果 two digit year cutoff 为 2049(默认),则 49 被解释为 2049,2050 被解释为
1950。为避免模糊,请使用四位数的年份。

有关时间值指定的更多信息,请参见时间格式。有关日期指定的更多信息,请参见 datetime 和
smalldatetime。

enddate

是计算的终止日期。enddate 是返回 datetime 或 smalldatetime 值或日期格式字符串的表达式。

返回类型
integer

注释
startdate 是从 enddate 减去。如果 startdate 比 enddate 晚,返回负值。

当结果超出整数值范围,DATEDIFF 产生错误。对于毫秒,最大数是 24 天 20 小时 31 分钟零 23.647
秒。对于秒,最大数是 68 年。

计算跨分钟、秒和毫秒这些边界的方法,使得 DATEDIFF 给出的结果在全部数据类型中是一致的。结果是带正
负号的整数值,其等于跨第一个和第二个日期间的 datepart 边界数。例如,在 1 月 4 日(星期日)和 1
月 11 日(星期日)之间的星期数是 1。

示例
此示例确定在 pubs 数据库中标题发布日期和当前日期间的天数。

USE pubs
GO
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles
GO

 

3.DATENAME
返回代表指定日期的指定日期部分的字符串。

语法
DATENAME ( datepart , date )

参数
datepart

是指定应返回的日期部分的参数。下表列出了 Microsoft? SQL Server? 识别的日期部分和缩写。

日期部分      缩写
year          yy, yyyy
quarter       qq, q
month         mm, m
dayofyear     dy, y
day           dd, d
week          wk, ww
weekday       dw
Hour          hh
minute        mi, n
second        ss, s
millisecond   ms


weekday (dw) 日期部分返回星期几(星期天、星期一等)。

是返回 datetime 或 smalldatetime 值或日期格式字符串的表达式。对 1753 年 1 月 1 日之后的日期用
datetime 数据类型。更早的日期存储为字符数据。当输入 datetime 值时,始终将其放入引号中。因为
smalldatetime 只精确到分钟,所以当用 smalldatetime 值时,秒和毫秒总是 0。有关指定日期的更多信
息,请参见 datetime 和 smalldatetime。有关时间值指定的更多信息,请参见时间格式。

如果只指定年份的最后两位数字,则小于或等于 two digit year cutoff 配置选项的值的最后两位数字的值
所在世纪与截止年所在世纪相同。大于该选项的值的最后两位数字的数字所在世纪为截止年所在世纪的前一个
世纪。例如,如果 two digit year cutoff 为 2050(默认),则 49 被解释为 2049,50 被解释为
1950。为避免模糊,请使用四位数字的年份。

返回类型
nvarchar

注释
SQL Server 自动在字符和 datetime 值间按需要进行转换,例如,当将字符值与 datetime 值进行比较
时。

示例
此示例从 GETDATE 返回的日期中提取月份名。

SELECT DATENAME(month, getdate()) AS ’Month Name’

下面是结果集:

Month Name                    
------------------------------
February    

 

4.DATEPART
返回代表指定日期的指定日期部分的整数。

语法
DATEPART ( datepart , date )

参数
datepart

是指定应返回的日期部分的参数。下表列出了 Microsoft? SQL Server? 识别的日期部分和缩写。

日期部分     缩写
year         yy, yyyy
quarter      qq, q
month        mm, m
dayofyear    dy, y
day          dd, d
week         wk, ww
weekday      dw
Hour         hh
minute       mi, n
second       ss, s
millisecond  ms


week (wk, ww) 日期部分反映对 SET DATEFIRST 作的更改。任何一年的 1 月 1 日定义了 week 日期部分
的开始数字,例如:DATEPART(wk, ’Jan 1, xxxx’) = 1,此处 xxxx 代表任一年。

weekday (dw) 日期部分返回对应于星期中的某天的数,例如:Sunday = 1、Saturday = 7。weekday 日期
部分产生的数取决于 SET DATEFIRST 设定的值,此命令设定星期中的第一天。

date

是返回 datetime 或 smalldatetime 值或日期格式字符串的表达式。对 1753 年 1 月 1 日之后的日期用
datetime 数据类型。更早的日期存储为字符数据。当输入 datetime 值时,始终将其放入引号中。因为
smalldatetime 只精确到分钟,所以当用 smalldatetime 值时,秒和毫秒总是 0。

如果只指定年份的最后两位数字,则小于或等于"两位数年份截止期"配置选项的值的最后两位数字的数字所在
世纪与截止年所在世纪相同。大于该选项的值的最后两位数字的数字所在世纪为截止年所在世纪的前一个世
纪。例如,如果 two digit year cutoff 为 2049 (默认),则 49 被解释为 2049,2050 被解释为
1950。为避免模糊,请使用四位数的年份。

有关时间值指定的更多信息,请参见时间格式。有关日期指定的更多信息,请参见 datetime 和
smalldatetime。

返回类型
int

注释
DAY、MONTH、和 YEAR 函数分别是 DATEPART(dd, date)、DATEPART(mm, date)、和 DATEPART(yy,
date) 的同义词。

示例
GETDATE 函数返回当前日期;然而,比较时并不总是需要完整的日期信息(通常只是对日期的一部分进行比
较)。此示例显示 GETDATE 及 DATEPART 的输出。

SELECT GETDATE() AS ’Current Date’
GO

下面是结果集:

Current Date               
---------------------------
Feb 18 1998 11:46PM        

SELECT DATEPART(month, GETDATE()) AS ’Month Number’
GO

下面是结果集:

Month Number
------------
2           

此示例假设日期是 5 月 29 日。

SELECT DATEPART(month, GETDATE())
GO

下面是结果集:

-----------
5          

(1 row(s) affected)

在此示例中,以数字表示日期。注意:SQL Server 将 0 解释为 01/01/1900。

SELECT DATEPART(m, 0), DATEPART(d, 0), DATEPART(yy, 0)

下面是结果集:

----- ------ ------
1     1      1900

 

5.DAY
返回代表指定日期的天的日期部分的整数。

语法
DAY ( date )

参数
date

类型为 datetime 或 smalldatetime 的表达式。

返回类型
int

注释
此函数等价于 DATEPART(dd, date)。

 

6.GETDATE
按 datetime 值的 Microsoft? SQL Server? 标准内部格式返回当前系统日期和时间。

语法
GETDATE ( )

返回类型
datetime

注释
日期函数可用在 SELECT 语句的选择列表或用在查询的 WHERE 子句中。

在设计报表时,GETDATE 函数可用于在每次生成报表时打印当前日期和时间。GETDATE 对于跟踪活动也很有
用,诸如记录事务在某一帐户上发生的时间。

示例
A. 用 GET DATE 返回当前日期和时间
下面的示例得出当前系统日期和时间:

SELECT GETDATE()
GO

下面是结果集:

-------------------------
July 29 1998   2:50    PM

(1 row(s) affected)

B. 在 CREATE TABLE 语句中使用 GETDATE
下面的示例创建 employees 表并用 GETDATE 给出员工雇佣时间的默认值。

USE pubs
GO
CREATE TABLE employees
(
 emp_id char(11) NOT NULL,
 emp_lname varchar(40) NOT NULL,
 emp_fname varchar(20) NOT NULL,
 emp_hire_date datetime DEFAULT GETDATE(),
 emp_mgr varchar(30)
)
GO

 

7.GETUTCDATE
返回表示当前 UTC 时间(世界时间坐标或格林尼治标准时间)的 datetime 值。当前的 UTC 时间得自当前
的本地时间和运行 SQL Server 的计算机操作系统中的时区设置。

语法
GETUTCDATE()

返回类型
datetime

注释
GETUTCDATE 是非确定性函数。引用该列的视图和表达式无法进行索引。

GETUTCDATE 无法在用户定义的函数内调用。

 

8.MONTH
返回代表指定日期月份的整数。

语法
MONTH ( date )

参数
date

返回 datetime 或 smalldatetime 值或日期格式字符串的表达式。仅对 1753 年 1 月 1 日后的日期使用
datetime 数据类型。

返回类型
int

注释
MONTH 等价于 DATEPART(mm, date)。

将 datetime 值包含在引号中。对于早期日期,可将其存储为字符数据。

Microsoft? SQL Server? 认可各种日期样式。有关日期和时间数据的更多信息,请参见 CAST 和
CONVERT。

示例
下面的示例从日期 03/12/1998 中返回月份数。

SELECT "Month Number" = MONTH(’03/12/1998’)
GO

下面是结果集:

Month Number
------------
3           

下例用数字指定日期。注意:SQL Server 将 0 解释为 01/01/1900。

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

下面是结果集。

----- ------ ------
1     1      1900

 

9.YEAR
返回表示指定日期中的年份的整数。

语法
YEAR ( date )

参数
date

datetime 或 smalldatetime 类型的表达式。

返回类型
int

注释
此函数等价于 DATEPART(yy, date)。

示例
下例从日期 03/12/1998 中返回年份数。

SELECT "Year Number" = YEAR(’03/12/1998’)
GO

下面是结果集:

Year Number
------------
1998           

下例用数字指定日期。注意:Microsoft? SQL Server? 数据库将 0 解释为 1900 年 1 月 1 日。

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

下面是结果集:

----- ------ ------
1     1      1900


 

数学运算函数


1.ABS
返回给定数字表达式的绝对值。

语法
ABS ( numeric_expression )

参数
numeric_expression

精确数字或近似数字数据类型类别的表达式(bit 数据类型除外)。

返回类型
返回与 numeric_expression 相同的类型。

示例
下例显示了 ABS 函数对三个不同数字的效果。

SELECT ABS(-1.0), ABS(0.0), ABS(1.0)

下面是结果集:

---- ---- ----
1.0  .0   1.0

ABS 函数可能产生溢出错误,例如:

SELECT ABS(convert(int, -2147483648))

下面是错误信息:

Server: Msg 8115, Level 16, State 2
Arithmetic overflow error converting expression to type int.

 

2.ACOS
返回以弧度表示的角度值,该角度值的余弦为给定的 float 表达式;本函数亦称反余弦。

语法
ACOS ( float_expression )

参数
float_expression

是 float 或 real 类型的表达式,其取值范围从 -1 到 1。对超过此范围的参数值,函数将返回 NULL 并且
报告域错误。

返回类型
float

示例
下例返回给定角的 ACOS 值。

SET NOCOUNT OFF
DECLARE @angle float
SET @angle = -1
SELECT ’The ACOS of the angle is: ’ + CONVERT(varchar, ACOS(@angle))

下面是结果集:

---------------------------------
The ACOS of the angle is: 3.14159                       

(1 row(s) affected)

下例将参数 @angle 设置为超出有效范围的值。

SET NOCOUNT OFF
DECLARE @angle float
SET @angle = 1.01
SELECT ’The ACOS of the angle is: ’ + CONVERT(varchar, ACOS(@angle))

下面是结果集:

--------------------------------------------------------
NULL                       

(1 row(s) affected)

A domain error occurred.

 

3.ASIN
返回以弧度表示的角度值,该角度值的正弦为给定的 float 表达式;亦称反正弦。

语法
ASIN ( float_expression )

参数
float_expression

是 float 类型的表达式,其取值范围从 -1 到 1。对超过此范围的参数值,函数将返回 NULL 并且报告域错
误。

返回类型
float

示例
下例用 float 表达式返回给定角的 ASIN 值。

-- First value will be -1.01, which fails.
DECLARE @angle float
SET @angle = -1.01
SELECT ’The ASIN of the angle is: ’ + CONVERT(varchar, ASIN(@angle))
GO

-- Next value is -1.00.
DECLARE @angle float
SET @angle = -1.00
SELECT ’The ASIN of the angle is: ’ + CONVERT(varchar, ASIN(@angle))
GO

-- Next value is 0.1472738.
DECLARE @angle float
SET @angle = 0.1472738
SELECT ’The ASIN of the angle is: ’ + CONVERT(varchar, ASIN(@angle))
GO

下面是结果集:

-------------------------
The ASIN of the angle is:                               

(1 row(s) affected)

Domain error occurred.

                                                        
---------------------------------
The ASIN of the angle is: -1.5708                       

(1 row(s) affected)

                                                        
----------------------------------
The ASIN of the angle is: 0.147811

 

4.ATAN
返回以弧度表示的角度值,该角度值的正切为给定的 float 表达式;亦称反正切。

语法
ATAN ( float_expression )

参数
float_expression

是 float 类型的表达式。

返回类型
float

示例
下例用 float 表达式返回给定角的 ATAN 值。

SELECT ’The ATAN of -45.01 is: ’ + CONVERT(varchar, ATAN(-45.01))
SELECT ’The ATAN of -181.01 is: ’ + CONVERT(varchar, ATAN(-181.01))
SELECT ’The ATAN of 0 is: ’ + CONVERT(varchar, ATAN(0))
SELECT ’The ATAN of 0.1472738 is: ’ + CONVERT(varchar, ATAN(0.1472738))
SELECT ’The ATAN of 197.1099392 is: ’ + CONVERT(varchar, ATAN(197.1099392))
GO

下面是结果集:

                                                        
-------------------------------
The ATAN of -45.01 is: -1.54858                      

(1 row(s) affected)

--------------------------------
The ATAN of -181.01 is: -1.56527                      

(1 row(s) affected)

--------------------------------
The ATAN of 0 is: 0                             

(1 row(s) affected)

----------------------------------
The ATAN of 0.1472738 is: 0.146223                      

(1 row(s) affected)

                                                        
-----------------------------------
The ATAN of 197.1099392 is: 1.56572

 

5.ATN2
返回以弧度表示的角度值,该角度值的正切介于两个给定的 float 表达式之间;亦称反正切。

语法
ATN2 ( float_expression , float_expression )

参数
float_expression

float 数据类型的表达式。

返回类型
float

示例
下例计算给定角的 ATN2。

DECLARE @angle1 float
DECLARE @angle2 float
SET @angle1 = 35.175643
SET @angle2 = 129.44
SELECT ’The ATN2 of the angle is: ’ + CONVERT(varchar,ATN2(@angle1,@angle2 ))
GO

下面是结果集:

The ATN2 of the angle is: 0.265345

 

6.CEILING
返回大于或等于所给数字表达式的最小整数。

语法
CEILING ( numeric_expression )

参数
numeric_expression

是精确数字或近似数字数据类型类别的表达式(bit 数据类型除外)。

返回类型
返回与 numeric_expression 相同的类型。

示例
下面的示例显示使用 CEILING 函数的正数、负数和零值。

SELECT CEILING($123.45), CEILING($-123.45), CEILING($0.0)
GO

下面是结果集:

--------- --------- -------------------------
124.00    -123.00    0.00

 


7.COS
一个数学函数,返回给定表达式中给定角度(以弧度为单位)的三角余弦值。

语法
COS ( float_expression )

参数
float_expression

是 float 类型的 expression。

返回类型
float

示例
下面的示例返回给定角度的 COS 值。

DECLARE @angle float
SET @angle = 14.78
SELECT ’The COS of the angle is: ’ + CONVERT(varchar,COS(@angle))
GO

下面是结果集:

The COS of the angle is: -0.599465

 


8.COT
一个数学函数,返回给定 float 表达式中指定角度(以弧度为单位)的三角余切值。

语法
COT ( float_expression )

参数
float_expression

是 float 类型的 expression。

返回类型
float

示例
下面的示例返回给定角度的 COT 值。

DECLARE @angle float
SET @angle = 124.1332
SELECT ’The COT of the angle is: ’ + CONVERT(varchar,COT(@angle))
GO

下面是结果集:

The COT of the angle is: -0.040312             

 


9.DEGREES
当给出以弧度为单位的角度时,返回相应的以度数为单位的角度。

语法
DEGREES ( numeric_expression )

参数
numeric_expression

精确数字或近似数字数据类型类别的表达式(bit 数据类型除外)。

返回代码值
返回与 numeric_expression 相同的类型。

示例
下例以 PI/2 弧度的角度返回度数。

SELECT ’The number of degrees in PI/2 radians is: ’ +
CONVERT(varchar, DEGREES((PI()/2)))
GO

下面是结果集:

The number of degrees in PI/2 radians is 90      

 

10.EXP
返回所给的 float 表达式的指数值。

语法
EXP ( float_expression )

参数
float_expression

是 float 类型的表达式。

返回类型
float

示例
本示例声明一个变量,同时返回所给变量 (378.615345498) 的指数值,并附有文字说明。

DECLARE @var float
SET @var = 378.615345498
SELECT ’The EXP of the variable is: ’ + CONVERT(varchar,EXP(@var))
GO

下面是结果集:

The EXP of the variable is: 2.69498e+164  

 

11.FLOOR
返回小于或等于所给数字表达式的最大整数。

语法
FLOOR ( numeric_expression )

参数
numeric_expression

精确数字或近似数字数据类型类别的表达式(bit 数据类型除外)。

返回类型
返回与 numeric_expression 相同的类型。

示例
此示例说明正数、负数和货币值在 FLOOR 函数中的运用。

SELECT FLOOR(123.45), FLOOR(-123.45), FLOOR($123.45)

结果为与 numeric_expression 数据类型相同的计算值的整数部分。

---------      ---------     -----------
123            -124          123.0000  

 

12.LOG
返回给定 float 表达式的自然对数。

语法
LOG ( float_expression )

参数
float_expression

是 float 数据类型的表达式。

返回类型
float

示例
下例计算给定 float 表达式的 LOG。

DECLARE @var float
SET @var = 5.175643
SELECT ’The LOG of the variable is: ’ + CONVERT(varchar,LOG(@var))
GO

下面是结果集:

The LOG of the variable is: 1.64396                       

 


13.LOG10
返回给定 float 表达式的以 10 为底的对数。

语法
LOG10 ( float_expression )

参数
float_expression

是 float 数据类型的表达式。

返回类型
float

示例
下例计算给定变量的 LOG10。

DECLARE @var float
SET @var = 145.175643
SELECT ’The LOG10 of the variable is: ’ + CONVERT(varchar,LOG10(@var))
GO

下面是结果集:

The LOG10 of the variable is: 2.16189   

 

14.PI
返回 PI 的常量值。

语法
PI ( )

返回类型
float

示例
本示例返回 PI 值。

SELECT PI()
GO

下面是结果集:

------------------------
3.14159265358979

 

15.POWER
返回给定表达式乘指定次方的值。

语法
POWER ( numeric_expression , y )

参数
numeric_expression

是精确数字或近似数字数据类型类别的表达式(bit 数据类型除外)。

y

numeric_expression 的次方。y 可以是精确数字或近似数字数据类型类别的表达式(bit 数据类型除外)。

返回类型
与 numeric_expression 相同。

示例
A. 使用 POWER 显示结果 0.0
本示例显示返回结果 0.0 的浮点下溢。

SELECT POWER(2.0, -100.0)
GO

下面是结果集:

------------------------------------------
0.0

(1 row(s) affected)

B. 使用 POWER
本示例显示 21 到 24 的 POWER 结果。

DECLARE @value int, @counter int
SET @value = 2
SET @counter = 1

WHILE @counter < 5
   BEGIN
      SELECT POWER(@value, @counter)
      SET NOCOUNT ON
      SET @counter = @counter + 1
      SET NOCOUNT OFF
   END
GO

下面是结果集:

-----------
2          

(1 row(s) affected)

-----------
4          

(1 row(s) affected)

-----------
8          

(1 row(s) affected)

-----------
16         

 

16.RADIANS
对于在数字表达式中输入的度数值返回弧度值。

语法
RADIANS ( numeric_expression )

参数
numeric_expression

精确数字或近似数字数据类型类别的表达式(bit 数据类型除外)。

返回类型
返回与 numeric_expression 相同的类型。

示例
A.  使用 RADIANS 显示 0.0
下例返回结果 0.0,因为用于转换为弧度的数字表达式对于 RADIANS 函数太小。

SELECT RADIANS(1e-307)
GO

下面是结果集:

-------------------
0.0                     
(1 row(s) affected)

B. 使用 RADIANS
下例使用 float 表达式并返回给定角度的弧度 (RADIANS)。

-- First value is -45.01.
DECLARE @angle float
SET @angle = -45.01
SELECT ’The RADIANS of the angle is: ’ +
   CONVERT(varchar, RADIANS(@angle))
GO
-- Next value is -181.01.
DECLARE @angle float
SET @angle = -181.01
SELECT ’The RADIANS of the angle is: ’ +
   CONVERT(varchar, RADIANS(@angle))
GO
-- Next value is 0.00.
DECLARE @angle float
SET @angle = 0.00
SELECT ’The RADIANS of the angle is: ’ +
   CONVERT(varchar, RADIANS(@angle))
GO
-- Next value is 0.1472738.
DECLARE @angle float
SET @angle = 0.1472738
SELECT ’The RADIANS of the angle is: ’ +
    CONVERT(varchar, RADIANS(@angle))
GO
-- Last value is 197.1099392.
DECLARE @angle float
SET @angle = 197.1099392
SELECT ’The RADIANS of the angle is: ’ +
   CONVERT(varchar, RADIANS(@angle))
GO

下面是结果集:

---------------------------------------
The RADIANS of the angle is: -0.785573                     
(1 row(s) affected)
---------------------------------------
The RADIANS of the angle is: -3.15922                      
(1 row(s) affected)
---------------------------------------
The RADIANS of the angle is: 0                             
(1 row(s) affected)
---------------------------------------
The RADIANS of the angle is: 0.00257041                    
 (1 row(s) affected)
---------------------------------------
The RADIANS of the angle is: 3.44022                       
(1 row(s) affected)

 

17.RAND
返回 0 到1 之间的随机float 值。

语法
RAND ( [ seed ] )

参数
seed

是给出种子值或起始值的整型表达式(tinyint、smallint 或 int)。

返回类型
float

注释
在单个查询中反复调用 RAND() 将产生相同的值。

示例
下例产生 4 个通过 RAND 函数产生的不同的随机值。

DECLARE @counter smallint
SET @counter = 1
WHILE @counter < 5
   BEGIN
      SELECT RAND(@counter) Random_Number
      SET NOCOUNT ON
      SET @counter = @counter + 1
      SET NOCOUNT OFF
   END
GO

下面是结果集:

Random_Number                                        
-------------------
0.71359199321292355

(1 row(s) affected)

Random_Number                                        
-------------------
0.7136106261841817

(1 row(s) affected)

Random_Number                                        
-------------------
0.71362925915543995

(1 row(s) affected)

Random_Number                                        
-------------------
0.7136478921266981

(1 row(s) affected)

 

18.ROUND
返回数字表达式并四舍五入为指定的长度或精度。

语法
ROUND ( numeric_expression , length [ , function ] )

参数
numeric_expression

精确数字或近似数字数据类型类别的表达式(bit 数据类型除外)。

length

是 numeric_expression 将要四舍五入的精度。length 必须是 tinyint、smallint 或int。当 length
为正数时,numeric_expression 四舍五入为 length 所指定的小数位数。当 length 为负数时,
numeric_expression 则按 length 所指定的在小数点的左边四舍五入。

function

是要执行的操作类型。function 必须是 tinyint、smallint 或 int。如果省略 function 或 function
的值为 0(默认),numeric_expression 将四舍五入。当指定 0 以外的值时,将截断
numeric_expression。

返回类型
返回与 numeric_expression 相同的类型。

注释
ROUND 始终返回一个值。如果 length 是负数且大于小数点前的数字个数,ROUND 将返回 0。

示例 结果
ROUND(748.58, -4) 0


当 length 是负数时,无论什么数据类型,ROUND 都将返回一个四舍五入的 numeric_expression。

示例 结果
ROUND(748.58, -1) 750.00
ROUND(748.58, -2) 700.00
ROUND(748.58, -3) 1000.00


示例
A. 使用 ROUND 和估计值
下例显示两个表达式,说明使用 ROUND 函数且最后一个数字始终是估计值。

SELECT ROUND(123.9994, 3), ROUND(123.9995, 3)
GO

下面是结果集:

----------- -----------
123.9990    124.0000   

B. 使用 ROUND 和四舍五入的近似值
下例显示四舍五入和近似值。

语句 结果
SELECT ROUND(123.4545, 2)
 123.4500
 
SELECT ROUND(123.45, -2)
 100.00
 


C. 使用 ROUND 截断
下例使用两个 SELECT 语句说明四舍五入和截断之间的区别。第一个语句四舍五入结果。第二个语句截断结
果。

语句 结果
SELECT ROUND(150.75, 0)
 151.00
 
SELECT ROUND(150.75, 0, 1)
 150.00
 


19.SIGN
返回给定表达式的正 (+1)、零 (0) 或负 (-1) 号。

语法
SIGN ( numeric_expression )

参数
numeric_expression

精确数字或近似数字数据类型类别的表达式(bit 数据类型除外)。

返回类型
float

示例
下例返回从 -1 到 1 的 SIGN 数值。

DECLARE @value real
SET @value = -1
WHILE @value < 2
   BEGIN
      SELECT SIGN(@value)
      SET NOCOUNT ON
      SELECT @value = @value + 1
      SET NOCOUNT OFF
   END
SET NOCOUNT OFF
GO

下面是结果集:

(1 row(s) affected)

                        
------------------------
-1.0                    

(1 row(s) affected)

                        
------------------------
0.0                     

(1 row(s) affected)

                        
------------------------
1.0                     

(1 row(s) affected)

 

20.SIN
以近似数字 (float) 表达式返回给定角度(以弧度为单位)的三角正弦值。

语法
SIN ( float_expression )

参数
float_expression

是 float 类型的表达式。

返回类型
float

示例
下例计算给定角度的 SIN 值。

DECLARE @angle float
SET @angle = 45.175643
SELECT ’The SIN of the angle is: ’ + CONVERT(varchar,SIN(@angle))
GO

下面是结果集:

The SIN of the angle is: 0.929607                      

(1 row(s) affected)

 

21.SQUARE
返回给定表达式的平方。

语法
SQUARE ( float_expression )

参数
float_expression

是 float 类型的表达式。

返回类型
float

示例
下例返回半径为 1 英寸、高为 5 英寸的圆柱容积。

DECLARE @h float, @r float
SET @h = 5
SET @r = 1
SELECT PI()* SQUARE(@r)* @h AS ’Cyl Vol’

下面是结果:

Cyl Vol
--------------------------
15.707963267948966

 

22.SQRT
返回给定表达式的平方根。

语法
SQRT ( float_expression )

参数
float_expression

是 float 类型的表达式。

返回类型
float

示例
下例返回 1.00 到 10.00 之间的数字的平方根。

DECLARE @myvalue float
SET @myvalue = 1.00
WHILE @myvalue < 10.00
   BEGIN
      SELECT SQRT(@myvalue)
      SELECT @myvalue = @myvalue + 1
   END
GO

下面是结果集:

------------------------
1.0                     
------------------------
1.4142135623731         
------------------------
1.73205080756888        
------------------------
2.0                     
------------------------
2.23606797749979        
------------------------
2.44948974278318        
------------------------
2.64575131106459        
------------------------
2.82842712474619        
------------------------
3.0

 

23.TAN
返回输入表达式的正切值。

语法
TAN ( float_expression )

参数
float_expression

float 或 real 类型的表达式,解释为弧度数。

返回类型
float

示例
下例返回 PI()/2 的正切值。

SELECT TAN(PI()/2)

下面是结果集:

----------------------
1.6331778728383844E+16

 


 

字符串函数


1.ASCII
返回字符表达式最左端字符的 ASCII 代码值。

语法
ASCII ( character_expression )

参数
character_expression

是类型为 char 或 varchar的表达式。

返回类型
int

示例
下例假定在 ASCII 字符集环境下运行,它将返回字符串"Du Monde entier"中每一个字符的 ASCII 值和
char 字符。

SET TEXTSIZE 0
SET NOCOUNT ON
-- Create the variables for the current character string position
-- and for the character string.
DECLARE @position int, @string char(15)
-- Initialize the variables.
SET @position = 1
SET @string = ’Du monde entier’
WHILE @position <= DATALENGTH(@string)
   BEGIN
   SELECT ASCII(SUBSTRING(@string, @position, 1)),
      CHAR(ASCII(SUBSTRING(@string, @position, 1)))
    SET @position = @position + 1
   END
SET NOCOUNT OFF
GO

下面是结果集:

----------- -
68          D
             
----------- -
117         u
             
----------- -
32           
             
----------- -
109         m
             
----------- -
111         o
             
----------- -
110         n
             
----------- -
100         d
             
----------- -
101         e
             
----------- -
32           
             
----------- -
101         e
             
----------- -
110         n
             
----------- -
116         t
             
----------- -
105         i
             
----------- -
101         e
             
----------- -
114         r

 

2.CHAR
将 int ASCII 代码转换为字符的字符串函数。

语法
CHAR ( integer_expression )

参数
integer_expression

介于 0 和 255 之间的整数。如果整数表达式不在此范围内,将返回 NULL 值。

返回类型
char(1)

注释
CHAR 可用于将控制字符插入字符串中。下表显示了一些常用的控制字符。

控制字符 值
制表符 CHAR(9)
换行符 CHAR(10)
回车 CHAR(13)


示例
A. 使用 ASCII 和 CHAR 打印字符串的 ASCII 值
下面的示例将打印字符串"New Moon"中每个字符的 ASCII 值和字符。

SET TEXTSIZE 0
-- Create variables for the character string and for the current
-- position in the string.
DECLARE @position int, @string char(8)
-- Initialize the current position and the string variables.
SET @position = 1
SET @string = ’New Moon’
WHILE @position <= DATALENGTH(@string)
   BEGIN
   SELECT ASCII(SUBSTRING(@string, @position, 1)),
      CHAR(ASCII(SUBSTRING(@string, @position, 1)))
   SET @position = @position + 1
   END
GO

下面是结果集:

----------- -
78          N
             
----------- -
101         e
             
----------- -
119         w
             
----------- -
32           
             
----------- -
77          M
             
----------- -
111         o
             
----------- -
111         o
             
----------- -
110         n
             
----------- -

B. 使用 CHAR 插入控制字符
下例使用 CHAR(13) 在不同的行上打印名称、地址与城市信息,并以文本方式返回结果。

USE Northwind
SELECT FirstName + ’ ’ + LastName, + CHAR(13) + Address,
   + CHAR(13) + City, + Region
FROM Employees
WHERE EmployeeID = 1

下面是结果集:

Nancy Davolio
507 - 20th Ave. E.
Apt. 2A
Seattle            WA

 

3.CHARINDEX
返回字符串中指定表达式的起始位置。

语法
CHARINDEX ( expression1 , expression2 [ , start_location ] )

参数
expression1

一个表达式,其中包含要寻找的字符的次序。expression1 是一个短字符数据类型分类的表达式。

expression2

一个表达式,通常是一个用于搜索指定序列的列。expression2 属于字符串数据类型分类。

start_location

在 expression2 中搜索 expression1 时的起始字符位置。如果没有给定 start_location,而是一个负数
或零,则将从 expression2 的起始位置开始搜索。

返回类型
int

注释
如果 expression1 或 expression2 之一属于 Unicode 数据类型(nvarchar 或 nchar)而另一个不属
于,则将另一个转换为 Unicode 数据类型。

如果 expression1 或 expression2 之一为 NULL 值,则当数据库兼容级别为 70 或更大时,CHARINDEX
返回 NULL 值。当数据库兼容级别为 65 或更小时,CHARINDEX 仅在 expression1 和 expression2 都为
NULL 时返回 NULL 值。

如果在 expression2 内没有找到 expression1,则 CHARINDEX 返回 0。

示例
第一个代码示例返回序列"wonderful"在 titles 表的 notes 列中开始的位置。第二个示例使用可选的
start_location 参数从 notes 列的第五个字符开始寻找"wonderful"。第三个示例显示了当
expression2 内找不到 expression1 时的结果集。

USE pubs
GO
SELECT CHARINDEX(’wonderful’, notes)
FROM titles
WHERE title_id = ’TC3218’
GO

-- Use the optional start_location parameter to start searching
-- for wonderful starting with the fifth character in the notes
-- column.
USE pubs
GO
SELECT CHARINDEX(’wonderful’, notes, 5)
FROM titles
WHERE title_id = ’TC3218’
GO

下面是第一个查询和第二个查询的结果集:

-----------
46         

(1 row(s) affected)

USE pubs
GO
SELECT CHARINDEX(’wondrous’, notes)
FROM titles
WHERE title_id=’TC3218’
GO

下面是结果集。
-----------
0         

 

4.DIFFERENCE
以整数返回两个字符表达式的 SOUNDEX 值之差。

语法
DIFFERENCE ( character_expression , character_expression )

参数
character_expression

是类型 char 或 varchar 的表达式。

返回类型
int

注释
返回的整数是 SOUNDEX 值中相同字符的个数。返回的值从 0 到 4 不等,4 表示 SOUNDEX 值相同。

示例
在下例的第一部分,比较两个相差很小的字符串的 SOUNDEX 值,DIFFERENCE 返回的值是 4。在下例的第二
部分,比较两个相差很大的字符串的 SOUNDEX 值,DIFFERENCE 返回的值是 0。

USE pubs
GO
-- Returns a DIFFERENCE value of 4, the least possible difference.
SELECT SOUNDEX(’Green’),
  SOUNDEX(’Greene’), DIFFERENCE(’Green’,’Greene’)
GO
-- Returns a DIFFERENCE value of 0, the highest possible difference.
SELECT SOUNDEX(’Blotchet-Halls’),
  SOUNDEX(’Greene’), DIFFERENCE(’Blotchet-Halls’, ’Greene’)
GO

下面是结果集:

----- ----- -----------
G650  G650  4          

(1 row(s) affected)
                       
----- ----- -----------
B432  G650  0 

 

5.LEFT
返回从字符串左边开始指定个数的字符。

语法
LEFT ( character_expression , integer_expression )

参数
character_expression

字符或二进制数据表达式。character_expression 可以是常量、变量或列。character_expression 必须
是可以隐式地转换为 varchar 的数据类型。否则,请使用 CAST 函数显式转换 character_expression。

integer_expression

是正整数。如果 integer_expression 为负,则返回空字符串。

返回类型
varchar

注释
兼容级别可能影响返回值。有关兼容级别的更多信息,请参见 sp_dbcmptlevel。

示例
A. 对列使用 LEFT 函数
下面的示例返回每个书名最左边的 5 个字符。

USE pubs
GO
SELECT LEFT(title, 5)
FROM titles
ORDER BY title_id
GO

下面是结果集:

-----
The B
Cooki
You C
Strai
Silic
The G
The P
But I
Secre
Net E
Compu
Is An
Life 
Prolo
Emoti
Onion
Fifty
Sushi

(18 row(s) affected)

B. 对字符串使用 LEFT 函数
下面的示例使用 LEFT 函数返回字符串 abcdefg 最左边的 2 个字符。

SELECT LEFT(’abcdefg’,2)
GO

下面是结果集:

--
ab

 

6.LEN
返回给定字符串表达式的字符(而不是字节)个数,其中不包含尾随空格。

语法
LEN ( string_expression )

参数
string_expression

要计算的字符串表达式。

返回类型
int

示例
下面的示例选定字符个数和位于芬兰的公司的 CompanyName 数据。

USE Northwind
GO
SELECT LEN(CompanyName) AS ’Length’, CompanyName
FROM Customers
WHERE Country = ’Finland’

下面是结果集:

Length       CompanyName
-----------  ------------------------------
14           Wartian Herkku
11           Wilman Kala

 

7.LOWER
将大写字符数据转换为小写字符数据后返回字符表达式。

语法
LOWER ( character_expression )

参数
character_expression

是字符或二进制数据表达式。character_expression 可以是常量、变量或列。character_expression 必
须是可以隐性转换为 varchar 的数据类型。否则,使用 CAST 显式转换 character_expression。

返回类型
varchar

示例
下例在选择价格位于 $11 和 $20 间的书名时,使用 LOWER 函数、UPPER 函数并把 UPPER 函数嵌套在
LOWER 函数内。

USE pubs
GO
SELECT LOWER(SUBSTRING(title, 1, 20)) AS Lower,
   UPPER(SUBSTRING(title, 1, 20)) AS Upper,
   LOWER(UPPER(SUBSTRING(title, 1, 20))) As LowerUpper
FROM titles
WHERE price between 11.00 and 20.00
GO

下面是结果集:

Lower                   Upper                   LowerUpper          
--------------------    --------------------    --------------------
the busy executive’s    THE BUSY EXECUTIVE’S    the busy executive’s
cooking with compute    COOKING WITH COMPUTE    cooking with compute
straight talk about     STRAIGHT TALK ABOUT     straight talk about 
silicon valley gastr    SILICON VALLEY GASTR    silicon valley gastr
secrets of silicon v    SECRETS OF SILICON V    secrets of silicon v
prolonged data depri    PROLONGED DATA DEPRI    prolonged data depri
fifty years in bucki    FIFTY YEARS IN BUCKI    fifty years in bucki
sushi, anyone?          SUSHI, ANYONE?          sushi, anyone?      

(8 row(s) affected)

 

8.LTRIM
删除起始空格后返回字符表达式。

语法
LTRIM ( character_expression )

参数
character_expression

是字符或二进制数据表达式。character_expression 可以是常量、变量或列。character_expression 必
须是可以隐性转换为 varchar 的数据类型。否则,使用 CAST 显式转换 character_expression。

返回类型
varchar

注释
兼容级别可能影响返回值。有关兼容级别的更多信息,请参见 sp_dbcmptlevel。

示例
下例使用 LTRIM 字符删除字符变量中的起始空格。

DECLARE @string_to_trim varchar(60)
SET @string_to_trim = ’     Five spaces are at the beginning of this
   string.’
SELECT ’Here is the string without the leading spaces: ’ +
   LTRIM(@string_to_trim)
GO

下面是结果集:

------------------------------------------------------------------------
Here is the string without the leading spaces: Five spaces are at the beginning of this
string.

 

9.NCHAR
根据 Unicode 标准所进行的定义,用给定整数代码返回 Unicode 字符。

语法
NCHAR ( integer_expression )

参数
integer_expression

介于 0 与 65535 之间的所有正整数。如果指定了超出此范围的值,将返回 NULL。

返回类型
nchar(1)

示例
A. 使用 NCHAR 和 UNICODE
下面的示例使用 UNICODE 和 NCHAR 函数打印字符串 K?enhavn 第二个字符的 UNICODE 值和 NCHAR
(Unicode 字符),并打印实际的第二个字符?。

DECLARE @nstring nchar(8)
SET @nstring = N’K?benhavn’
SELECT UNICODE(SUBSTRING(@nstring, 2, 1)),
   NCHAR(UNICODE(SUBSTRING(@nstring, 2, 1)))
GO

下面是结果集:

----------- -
248         ?(1 row(s) affected)

B. 使用 SUBSTRING、UNICODE、CONVERT 和 NCHAR
下面的示例使用 SUBSTRING、UNICODE、CONVERT 和 NCHAR 函数打印字符串 K?enhavn 的字符数、
Unicode 字符以及每个字符的 UNICODE 值。

-- The @position variable holds the position of the character currently
-- being processed. The @nstring variable is the Unicode character
-- string to process.
DECLARE @position int, @nstring nchar(9)
-- Initialize the current position variable to the first character in
-- the string.
SET @position = 1
-- Initialize the character string variable to the string to process.
-- Notice that there is an N before the start of the string, which
-- indicates that the data following the N is Unicode data.
SET @nstring = N’K?benhavn’
-- Print the character number of the position of the string you’re at,
-- the actual Unicode character you’re processing, and the UNICODE value -- for this
particular character.
PRINT ’Character #’ + ’ ’ + ’Unicode Character’ + ’ ’ + ’UNICODE Value’
WHILE @position <= DATALENGTH(@nstring)
   BEGIN
   SELECT @position,
      NCHAR(UNICODE(SUBSTRING(@nstring, @position, 1))),
      CONVERT(NCHAR(17), SUBSTRING(@nstring, @position, 1)),
      UNICODE(SUBSTRING(@nstring, @position, 1))
   SELECT @position = @position + 1
   END
GO

下面是结果集:

Character # Unicode Character UNICODE Value
                                         
----------- ----------------- -----------
1           K                 75         
                                         
----------- ----------------- -----------
2           ?                 248        
                                         
----------- ----------------- -----------
3           b                 98         
                                         
----------- ----------------- -----------
4           e                 101        
                                         
----------- ----------------- -----------
5           n                 110        
                                         
----------- ----------------- -----------
6           h                 104        
                                         
----------- ----------------- -----------
7           a                 97         
                                         
----------- ----------------- -----------
8           v                 118        
                                         
----------- ----------------- -----------
9           n                 110        
                                         
----------- ----------------- -----------
10          (null)            (null)     
                                         
----------- ----------------- -----------
11          (null)            (null)     
                                         
----------- ----------------- -----------
12          (null)            (null)     
                                         
----------- ----------------- -----------
13          (null)            (null)     
                                         
----------- ----------------- -----------
14          (null)            (null)     
                                         
----------- ----------------- -----------
15          (null)            (null)     
                                         
----------- ----------------- -----------
16          (null)            (null)     
                                         
----------- ----------------- -----------
17          (null)            (null)     
                                         
----------- ----------------- -----------
18          (null)            (null)

 


10.PATINDEX
返回指定表达式中某模式第一次出现的起始位置;如果在全部有效的文本和字符数据类型中没有找到该模式,
则返回零。

语法
PATINDEX ( ’%pattern%’ , expression )

参数
pattern

一个字符串。可以使用通配符,但 pattern 之前和之后必须有 % 字符(搜索第一个和最后一个字符时除
外)。pattern 是短字符数据类型类别的表达式。

expression

一个表达式,通常为要在其中搜索指定模式的列,expression 为字符串数据类型类别。

返回类型
int

注释
PATINDEX 对 text 数据类型很有用;除 IS NULL、IS NOT NULL 和 LIKE(这些是 WHERE 子句中对
text 类型有效的仅有的其它比较运算)外,PATINDEX 也可用于 WHERE 子句中。

如果 pattern 或 expression 为 NULL,则当数据库的兼容级别为 70 时 PATINDEX 返回 NULL;如果数据
库兼容级别为 65 或更靠前,则仅当 pattern 和 expression 同时为 NULL 时,PATINDEX 返回 NULL。

示例
A. 在 PATINDEX 中使用模式
本示例查找模式"wonderful"在 titles 表中 notes 列的某一特定行中的开始位置。

USE pubs
GO
SELECT PATINDEX(’%wonderful%’, notes)
FROM titles
WHERE title_id = ’TC3218’
GO

下面是结果集:

-----------
46         

(1 row(s) affected)

如果未通过 WHERE 子句限制要搜索的行,查询将返回表中的所有行,对在其中找到该模式的所有行报告非零
值,对在其中未找到该模式的所有行报告零值。

B. 在 PATINDEX 中使用通配符
本示例使用通配符查找模式"won_erful"在 titles 表中 notes 列的某一特定行中的开始位置,其中下划线
为代表任何字符的通配符。

USE pubs
GO
SELECT PATINDEX(’%won_erful%’, notes)
FROM titles
WHERE title_id = ’TC3218’
GO

下面是结果集:

------------
46

(1 row(s) affected)

如果没有限制要搜索的行,查询将返回表中的所有行,对在其中找到该模式的所有行报告非零值。

 

11.REPLACE
用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式。

语法
REPLACE ( ’string_expression1’ , ’string_expression2’ , ’string_expression3’ )

参数
’string_expression1’

待搜索的字符串表达式。string_expression1 可以是字符数据或二进制数据。

’string_expression2’

待查找的字符串表达式。string_expression2 可以是字符数据或二进制数据。

’string_expression3’

替换用的字符串表达式。string_expression3 可以是字符数据或二进制数据。

返回类型
如果 string_expression(1、2 或 3)是支持的字符数据类型之一,则返回字符数据。如果
string_expression(1、2 或 3)是支持的 binary 数据类型之一,则返回二进制数据。

示例
下例用 xxx 替换 abcdefghi 中的字符串 cde。

SELECT REPLACE(’abcdefghicde’,’cde’,’xxx’)
GO

下面是结果集:

------------
abxxxfghixxx
(1 row(s) affected)

 

12.QUOTENAME
返回带有分隔符的 Unicode 字符串,分隔符的加入可使输入的字符串成为有效的 Microsoft? SQL
Server? 分隔标识符。

语法
QUOTENAME ( ’character_string’ [ , ’quote_character’ ] )

参数
’{character}[...n]’

Unicode 字符数据字符串。character_string 是 sysname 值。

’quote_character’

用作分隔符的单字符字符串。可以是单引号 (’)、左括号或右括号 ([]) 或者双引号 (")。如果未指定
quote_character,则使用括号。

返回类型
nvarchar(129)

示例
本示例采用字符串"abc[]def",并使用"["和"]"字符创建有效的 SQL Server 引用(分隔)标识符。

SELECT QUOTENAME(’abc[]def’)

下面是结果集:

[abc[]]def]

(1 row(s) affected)

注意,字符串"abc[]def"中的右括号有两个,用于表示转义符。

 

13.REPLICATE
以指定的次数重复字符表达式。

语法
REPLICATE ( character_expression , integer_expression )

参数
character_expression

由字符数据组成的字母数字表达式。character_expression 可以是常量或变量,也可以是字符列或二进制数
据列。

integer_expression

是正整数。如果 integer_expression 为负,则返回空字符串。

返回类型
varchar

character_expression 必须为可隐性转换为 varchar 的数据类型。否则,使用 CAST 函数显式转换
character_expression。

注释
兼容级别可能影响返回值。有关更多信息,请参见 sp_dbcmptlevel。

示例
A. 使用 REPLICATE
下例重复两遍每个作者的名字。

USE pubs
SELECT REPLICATE(au_fname, 2)
FROM authors
ORDER BY au_fname

下面是结果集:

----------------------
AbrahamAbraham                          
AkikoAkiko                              
AlbertAlbert                            
AnnAnn                                  
AnneAnne                                
BurtBurt                                
CharleneCharlene                        
CherylCheryl                            
DeanDean                                
DirkDirk                                
HeatherHeather                          
InnesInnes                              
JohnsonJohnson                          
LiviaLivia                              
MarjorieMarjorie                        
MeanderMeander                          
MichaelMichael                          
MichelMichel                            
MorningstarMorningstar                  
ReginaldReginald                        
SherylSheryl                            
StearnsStearns                          
SylviaSylvia                            
(23 row(s) affected)

B. 使用 REPLICATE、SUBSTRING 和 SPACE
下例使用 REPLICATE、SUBSTRING 和 SPACE 生成 authors 表中的所有作者的电话和传真列表。

-- Replicate phone number twice because the fax number is identical to
-- the author telephone number.
USE pubs
GO
SELECT SUBSTRING((UPPER(au_lname) + ’,’ + SPACE(1) + au_fname), 1, 35)
   AS Name, phone AS Phone, REPLICATE(phone,1) AS Fax
FROM authors
ORDER BY au_lname, au_fname
GO

下面是结果集:

Name                                Phone        Fax                   
----------------------------------- ------------ -----------------------
BENNET, Abraham                     415 658-9932 415 658-9932          
BLOTCHET-HALLS, Reginald            503 745-6402 503 745-6402          
CARSON, Cheryl                      415 548-7723 415 548-7723          
DEFRANCE, Michel                    219 547-9982 219 547-9982          
DEL CASTILLO, Innes                 615 996-8275 615 996-8275          
DULL, Ann                           415 836-7128 415 836-7128          
GREEN, Marjorie                     415 986-7020 415 986-7020          
GREENE, Morningstar                 615 297-2723 615 297-2723          
GRINGLESBY, Burt                    707 938-6445 707 938-6445          
HUNTER, Sheryl                      415 836-7128 415 836-7128          
KARSEN, Livia                       415 534-9219 415 534-9219          
LOCKSLEY, Charlene                  415 585-4620 415 585-4620          
MACFEATHER, Stearns                 415 354-7128 415 354-7128          
MCBADDEN, Heather                   707 448-4982 707 448-4982          
O’LEARY, Michael                    408 286-2428 408 286-2428          
PANTELEY, Sylvia                    301 946-8853 301 946-8853          
RINGER, Albert                      801 826-0752 801 826-0752          
RINGER, Anne                        801 826-0752 801 826-0752          
SMITH, Meander                      913 843-0462 913 843-0462          
STRAIGHT, Dean                      415 834-2919 415 834-2919          
STRINGER, Dirk                      415 843-2991 415 843-2991          
WHITE, Johnson                      408 496-7223 408 496-7223          
YOKOMOTO, Akiko                     415 935-4228 415 935-4228          
(23 row(s) affected)

C. 使用 REPLICATE 和 DATALENGTH
本例中,当数值从数字数据类型转换为字符型或 Unicode 型时,从左填充数字,使其达到指定的长度。

USE Northwind
GO
DROP TABLE t1
GO
CREATE TABLE t1
(
 c1 varchar(3),
 c2 char(3)
)
GO
INSERT INTO t1 VALUES (’2’, ’2’)
INSERT INTO t1 VALUES (’37’, ’37’)
INSERT INTO t1 VALUES (’597’, ’597’)
GO
SELECT REPLICATE(’0’, 3 - DATALENGTH(c1)) + c1 AS [Varchar Column],
       REPLICATE(’0’, 3 - DATALENGTH(c2)) + c2 AS [Char Column]
FROM t1
GO

 

14.REVERSE
返回字符表达式的反转。

语法
REVERSE ( character_expression )

参数
character_expression

由字符数据组成的表达式。character_expression 可以是常量、变量,也可以是字符或二进制数据的列。

返回类型
varchar

注释
character_expression 必须为可隐性转换为 varchar 的数据类型。否则,使用 CAST 显式转换
character_expression。

示例
下例返回所有名字中的字符被反转的作者。

USE pubs
GO
SELECT REVERSE(au_fname)
FROM authors
ORDER BY au_fname
GO

下面是结果集:

--------------------
maharbA             
okikA               
treblA              
nnA                 
ennA                
truB                
enelrahC            
lyrehC              
naeD                
kriD                
rehtaeH             
sennI               
nosnhoJ             
aiviL               
eirojraM            
rednaeM             
leahciM             
lehciM              
ratsgninroM         
dlanigeR            
lyrehS              
snraetS             
aivlyS              
(23 row(s) affected)

 

15.RIGHT
返回字符串中从右边开始指定个数的 integer_expression 字符。

语法
RIGHT ( character_expression , integer_expression )

参数
character_expression

由字符数据组成的表达式。character_expression 可以是常量、变量,也可以是字符或二进制数据的列。

integer_expression

是起始位置,用正整数表示。如果 integer_expression 是负数,则返回一个错误。

返回类型
varchar

character_expression 必须为可隐性转换为 varchar 的数据类型。否则,使用 CAST 显式转换
character_expression。

注释
兼容级别可能影响返回值。有关更多信息,请参见 sp_dbcmptlevel。

示例
下例返回每个作者名字中最右边的五个字符。

USE pubs
GO
SELECT RIGHT(au_fname, 5)
FROM authors
ORDER BY au_fname
GO

下面是结果集:

------------------
raham
Akiko
lbert
Ann  
Anne 
Burt 
rlene
heryl
Dean 
Dirk 
ather
Innes
hnson
Livia
jorie
ander
chael
ichel
gstar
inald
heryl
earns
ylvia
(23 row(s) affected)

 

16.RTRIM
截断所有尾随空格后返回一个字符串。

语法
RTRIM ( character_expression )

参数
character_expression

由字符数据组成的表达式。character_expression 可以是常量、变量,也可以是字符或二进制数据的列。

返回类型
varchar

注释
character_expression 必须为可隐性转换为 varchar 的数据类型。否则请使用 CAST 函数显式转换
character_expression。

 

说明  兼容级别可能影响返回值。有关更多信息,请参见 sp_dbcmptlevel。


示例
下例显示如何使用 RTRIM 删除字符变量中的尾随空格。

DECLARE @string_to_trim varchar(60)
SET @string_to_trim = ’Four spaces are after the period in this sentence.    ’
SELECT ’Here is the string without the leading spaces: ’ + CHAR(13) +
   RTRIM(@string_to_trim)
GO

下面是结果集:

(1 row(s) affected)
------------------------------------------------------------------------
Here is the string without the leading spaces: Four spaces are after the period in this
sentence.          
(1 row(s) affected)

 


17.SOUNDEX
返回由四个字符组成的代码 (SOUNDEX) 以评估两个字符串的相似性。

语法
SOUNDEX ( character_expression )

参数
character_expression

是字符数据的字母数字表达式。character_expression 可以是常数、变量或列。

返回类型
char

注释
SOUNDEX 将 alpha 字符串转换成由四个字符组成的代码,以查找相似的词或名称。代码的第一个字符是
character_expression 的第一个字符,代码的第二个字符到第四个字符是数字。将忽略
character_expression 中的元音,除非它们是字符串的第一个字母。可以嵌套字符串函数。

示例
下例显示 SOUNDEX 函数及相关的 DIFFERENCE 函数。在第一个示例中,返回所有辅音字母的标准 SOUNDEX
值。为 Smith 和 Smythe 返回的 SOUNDEX 结果相同,因为不包括所有元音、字母 y、连写字母和字母 h。

-- Using SOUNDEX
SELECT SOUNDEX (’Smith’), SOUNDEX (’Smythe’)

下面是结果集:

----- -----
S530  S530 

(1 row(s) affected)

DIFFERENCE 函数比较 SOUNDEX 模式结果的差。第一个示例显示两个仅元音不同的字符串。返回的差是 4
(可能的最小差)。

-- Using DIFFERENCE
SELECT DIFFERENCE(’Smithers’, ’Smythers’)
GO

下面是结果集:

-----------
4          

(1 row(s) affected)

在下例中,字符串的辅音不同,所以返回的差是 2(较高的差)。

SELECT DIFFERENCE(’Anothers’, ’Brothers’)
GO

下面是结果集:

-----------
2          

 

18.SPACE
返回由重复的空格组成的字符串。

语法
SPACE ( integer_expression )

参数
integer_expression

是表示空格个数的正整数。如果 integer_expression 为负,则返回空字符串。

返回类型
char

注释
若要在 Unicode 数据中包括空格,请使用 REPLICATE 而非 SPACE。

示例
下例剪裁作者的姓氏并串联一个逗号、两个空格和作者的名字。

USE pubs
GO
SELECT RTRIM(au_lname) + ’,’ + SPACE(2) +  LTRIM(au_fname)
FROM authors
ORDER BY au_lname, au_fname
GO

下面是结果集:

Name                                                           
---------------------------------------------------------------
Bennet,  Abraham                                               
Blotchet-Halls,  Reginald                                      
Carson,  Cheryl                                                
DeFrance,  Michel                                              
del Castillo,  Innes                                           
Dull,  Ann                                                     
Green,  Marjorie                                               
Greene,  Morningstar                                           
Gringlesby,  Burt                                              
Hunter,  Sheryl                                                
Karsen,  Livia                                                 
Locksley,  Charlene                                            
MacFeather,  Stearns                                           
McBadden,  Heather                                             
O’Leary,  Michael                                              
Panteley,  Sylvia                                              
Ringer,  Albert                                                
Ringer,  Anne                                                  
Smith,  Meander                                                
Straight,  Dean                                                
Stringer,  Dirk                                                
White,  Olivier                                                
Yokomoto,  Akiko                                               

(23 row(s) affected)

 

19.STR
由数字数据转换来的字符数据。

语法
STR ( float_expression [ , length [ , decimal ] ] )

参数
float_expression

是带小数点的近似数字 (float) 数据类型的表达式。不要在 STR 函数中将函数或子查询用作
float_expression。

length

是总长度,包括小数点、符号、数字或空格。默认值为 10。

decimal

是小数点右边的位数。

返回类型
char

注释
如果为 STR 提供 length 和 decimal 参数值,则这些值应该是正数。在默认情况下或者小数参数为 0 时,
数字四舍五入为整数。指定长度应该大于或等于小数点前面的数字加上数字符号(若有)的长度。短的
float_expression 在指定长度内右对齐,长的 float_expression 则截断为指定的小数位数。例如,STR
(12,10) 输出的结果是 12,在结果集内右对齐。而 STR(1223, 2) 则将结果集截断为 **。可以嵌套字符串
函数。

 

说明  若要转换为 Unicode 数据,请在 CONVERT 或 CAST 转换函数内使用 STR。


示例
A. 使用 STR
下例将包含五个数字和一个小数点的表达式转换为有六个位置的字符串。数字的小数部分四舍五入为一个小数
位。

SELECT STR(123.45, 6, 1)
GO

下面是结果集:

------
 123.5

(1 row(s) affected)

当表达式超出指定长度时,字符串为指定长度返回 **。

SELECT STR(123.45, 2, 2)
GO

下面是结果集:

--
**

(1 row(s) affected)

即使数字数据嵌套在 STR内,结果集也是带指定格式的字符数据。

SELECT STR (FLOOR (123.45), 8, 3)
GO

下面是结果集:

--------
 123.000

(1 row(s) affected)

B. 使用 STR 和 CONVERT 函数
下例比较 STR 和 CONVERT 的结果。

SELECT STR(3.147) AS ’STR’,
       STR(3.147, 5, 2) AS ’2 decimals’,
       STR(3.147, 5, 3) AS ’3 decimals’
GO

下面是结果集:

STR        2 decimals 3 decimals
---------- ---------- ----------
         3  3.15      3.147     

(1 row(s) affected)

-- Use CONVERT.
SELECT CONVERT(char(1), 3.147) AS ’CHAR(1)’,
       CONVERT(char(3), 3.147) AS ’CHAR(3)’,
       CONVERT(char(5), 3.147) AS ’CHAR(5)’
GO

下面是结果集:

CHAR(1) CHAR(3) CHAR(5)
------- ------- -------
(null)  (null)  3.147  

 


20.STUFF
删除指定长度的字符并在指定的起始点插入另一组字符。

语法
STUFF ( character_expression , start , length , character_expression )

参数
character_expression

由字符数据组成的表达式。character_expression 可以是常量、变量,也可以是字符或二进制数据的列。

start

是一个整形值,指定删除和插入的开始位置。如果 start 或 length 是负数,则返回空字符串。如果
start 比第一个 character_expression 长,则返回空字符串。

length

是一个整数,指定要删除的字符数。如果 length 比第一个 character_expression 长,则最多删除到最后
一个 character_expression 中的最后一个字符。

返回类型
如果 character_expression 是一个支持的字符数据类型,则返回字符数据。如果
character_expression 是一个支持的 binary 数据类型,则返回二进制数据。

注释
可以嵌套字符串函数。

示例
下例通过在第一个字符串 (abcdef) 中删除从第二个位置(字符 b)开始的三个字符,然后在删除的起始位置
插入第二个字符串,创建并返回一个字符串。

SELECT STUFF(’abcdef’, 2, 3, ’ijklmn’)
GO

下面是结果集:

---------
aijklmnef

 

21.STUFF
删除指定长度的字符并在指定的起始点插入另一组字符。

语法
STUFF ( character_expression , start , length , character_expression )

参数
character_expression

由字符数据组成的表达式。character_expression 可以是常量、变量,也可以是字符或二进制数据的列。

start

是一个整形值,指定删除和插入的开始位置。如果 start 或 length 是负数,则返回空字符串。如果
start 比第一个 character_expression 长,则返回空字符串。

length

是一个整数,指定要删除的字符数。如果 length 比第一个 character_expression 长,则最多删除到最后
一个 character_expression 中的最后一个字符。

返回类型
如果 character_expression 是一个支持的字符数据类型,则返回字符数据。如果
character_expression 是一个支持的 binary 数据类型,则返回二进制数据。

注释
可以嵌套字符串函数。

示例
下例通过在第一个字符串 (abcdef) 中删除从第二个位置(字符 b)开始的三个字符,然后在删除的起始位置
插入第二个字符串,创建并返回一个字符串。

SELECT STUFF(’abcdef’, 2, 3, ’ijklmn’)
GO

下面是结果集:

---------
aijklmnef

 

22.SUBSTRING
返回字符、binary、text 或 image 表达式的一部分。有关可与该函数一起使用的有效 Microsoft? SQL
Server? 数据类型的更多信息,请参见数据类型。

语法
SUBSTRING ( expression , start , length )

参数
expression

是字符串、二进制字符串、text、image、列或包含列的表达式。不要使用包含聚合函数的表达式。

start

是一个整数,指定子串的开始位置。

length

是一个整数,指定子串的长度(要返回的字符数或字节数)。

 

说明  由于在 text 数据上使用 SUBSTRING 时 start 和 length 指定字节数,因此 DBCS 数据(如日本
汉字)可能导致在结果的开始或结束位置拆分字符。此行为与 READTEXT 处理 DBCS 的方式一致。然而,由
于偶而会出现奇怪的结果,建议对 DBCS 字符使用 ntext 而非 text。


返回类型
如果 expression 是支持的字符数据类型,则返回字符数据。如果 expression 是支持的 binary 数据类
型,则返回二进制数据。

返回字符串的类型与给定表达式的类型相同(表中显示的除外)。

给定的表达式 返回类型
text varchar
image varbinary
ntext nvarchar


注释
在字符数中必须指定使用 ntext、char 或 varchar 数据类型的偏移量(start 和 length)。在字节数中
必须指定使用 text、image、binary 或 varbinary 数据类型的偏移量。

 

说明  兼容级别可能影响返回值。有关兼容级别的更多信息,请参见 sp_dbcmptlevel。


示例
A. 在字符串上使用 SUBSTRING
下例显示如何只返回字符串的一部分。该查询在一列中返回 authors 表中的姓氏,在另一列中返回
authors 表中的名字首字母。

USE pubs
SELECT au_lname, SUBSTRING(au_fname, 1, 1)
FROM authors
ORDER BY au_lname

下面是结果集:

au_lname                                  
---------------------------------------- -
Bennet                                   A
Blotchet-Halls                           R
Carson                                   C
DeFrance                                 M
del Castillo                             I
...
Yokomoto                                 A

(23 row(s) affected)

下例显示如何显示字符串常量 abcdef 中的第二个、第三个和第四个字符。

SELECT x = SUBSTRING(’abcdef’, 2, 3)

下面是结果集:

x
----------
bcd

(1 row(s) affected)

B. 在 text、ntext 和 image 数据上使用 SUBSTRING
下例显示如何从 pubs 数据库的 publishers 表内的每个 text 和 image 数据列中返回前 200 个字符。
text 数据以 varchar 的形式返回,image 数据则以 varbinary 的形式返回。

USE pubs
SELECT pub_id, SUBSTRING(logo, 1, 10) AS logo,
   SUBSTRING(pr_info, 1, 10) AS pr_info
FROM pub_info
WHERE pub_id = ’1756’

下面是结果集:

pub_id logo                   pr_info   
------ ---------------------- ----------
1756   0x474946383961E3002500 This is sa

(1 row(s) affected)

下例显示 SUBSTRING 在 text 和 ntext 数据上的效果。首先,下例在 pubs 数据库内创建一个名为
npr_info 的新表。然后,在 npr_info 表中用 pub_info.pr_info 列的前 80 个字符创建 pr_info 列,
并添加ü作为首字符。最后,INNER JOIN 检索所有出版商标识号以及 text 和 ntext 出版商信息列的
SUBSTRING。

IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES
      WHERE table_name = ’npub_info’)
   DROP TABLE npub_info
GO
-- Create npub_info table in pubs database. Borrowed from instpubs.sql.
USE pubs
GO
CREATE TABLE npub_info
(
 pub_id         char(4)           NOT NULL
         REFERENCES publishers(pub_id)
         CONSTRAINT UPKCL_npubinfo PRIMARY KEY CLUSTERED,
 pr_info        ntext             NULL
)

GO

-- Fill the pr_info column in npub_info with international data.
RAISERROR(’Now at the inserts to pub_info...’,0,1)

GO

INSERT npub_info VALUES(’0736’, N’üThis is sample text data for New Moon Books, publisher
0736 in the pubs database’)
INSERT npub_info values(’0877’, N’üThis is sample text data for Binnet & Hardley,
publisher 0877 in the pubs databa’)
INSERT npub_info values(’1389’, N’üThis is sample text data for Algodata Infosystems,
publisher 1389 in the pubs da’)
INSERT npub_info values(’9952’, N’üThis is sample text data for Scootney Books, publisher
9952 in the pubs database’)
INSERT npub_info values(’1622’, N’üThis is sample text data for Five Lakes Publishing,
publisher 1622 in the pubs d’)
INSERT npub_info values(’1756’, N’üThis is sample text data for Ramona Publishers,
publisher 1756 in the pubs datab’)
INSERT npub_info values(’9901’, N’üThis is sample text data for GGG&G, publisher 9901 in
the pubs database. GGG&G i’)
INSERT npub_info values(’9999’, N’üThis is sample text data for Lucerne Publishing,
publisher 9999 in the pubs data’)
GO
-- Join between npub_info and pub_info on pub_id.
SELECT pr.pub_id, SUBSTRING(pr.pr_info, 1, 35) AS pr_info,
   SUBSTRING(npr.pr_info, 1, 35) AS npr_info
FROM pub_info pr INNER JOIN npub_info npr
   ON pr.pub_id = npr.pub_id
ORDER BY pr.pub_id ASC

 

22.UNICODE
按照 Unicode 标准的定义,返回输入表达式的第一个字符的整数值。

语法
UNICODE ( ’ncharacter_expression’ )

参数
’ncharacter_expression’

是 nchar 或 nvarchar 表达式。

返回类型
int

示例
A. 使用 UNICODE 和 NCHAR
下面的示例使用 UNICODE 和 NCHAR 函数打印 ?kergatan 24 字符串中第一个字符的 UNICODE 值,并打印
实际的第一个字符 ?。

DECLARE @nstring nchar(12)
SET @nstring = N’?kergatan 24’
SELECT UNICODE(@nstring), NCHAR(UNICODE(@nstring))

下面是结果集:

----------- -
197         ?
B. 使用 SUBSTRING、UNICODE 和 CONVERT 函数
下面的示例使用 SUBSTRING、UNICODE 和 CONVERT 函数输出字符串 ?kergatan 24 中每个字符的字符号、
Unicode 字符和 UNICODE 值。

-- The @position variable holds the position of the character currently
-- being processed. The @nstring variable is the Unicode character
-- string to process.
DECLARE @position int, @nstring nchar(12)
-- Initialize the current position variable to the first character in
-- the string.
SET @position = 1
-- Initialize the character string variable to the string to process.
-- Notice that there is an N before the start of the string, which
-- indicates that the data following the N is Unicode data.
SET @nstring = N’?kergatan 24’
-- Print the character number of the position of the string you are at,
-- the actual Unicode character you are processing, and the UNICODE
-- value for this particular character.
PRINT ’Character #’ + ’ ’ + ’Unicode Character’ + ’ ’ + ’UNICODE Value’
WHILE @position <= DATALENGTH(@nstring)
-- While these are still characters in the character string,
   BEGIN
   SELECT @position,
      CONVERT(char(17), SUBSTRING(@nstring, @position, 1)),
      UNICODE(SUBSTRING(@nstring, @position, 1))
   SELECT @position = @position + 1
   END

下面是结果集:

Character # Unicode Character UNICODE Value
                                         
----------- ----------------- -----------
1           ?                 197        
                                         
----------- ----------------- -----------
2           k                 107        
                                         
----------- ----------------- -----------
3           e                 101        
                                         
----------- ----------------- -----------
4           r                 114        
                                         
----------- ----------------- -----------
5           g                 103        
                                         
----------- ----------------- -----------
6           a                 97         
                                         
----------- ----------------- -----------
7           t                 116        
                                         
----------- ----------------- -----------
8           a                 97         
                                         
----------- ----------------- -----------
9           n                 110        
                                         
----------- ----------------- -----------
10                            32         
                                         
----------- ----------------- -----------
11          2                 50         
                                         
----------- ----------------- -----------
12          4                 52

 

23.UPPER
返回将小写字符数据转换为大写的字符表达式。

语法
UPPER ( character_expression )

参数
character_expression

由字符数据组成的表达式。character_expression 可以是常量、变量,也可以是字符或二进制数据的列。

返回类型
varchar

注释
character_expression 必须为可隐性转换为 varchar 的数据类型。否则请使用 CAST 函数显式转换
character_expression。

示例
本示例使用 UPPER 函数和 RTRIM 函数来返回整理过的大写的作者姓名。

USE pubs
GO
SELECT UPPER(RTRIM(au_lname)) + ’, ’ + au_fname AS Name
FROM authors
ORDER BY au_lname
GO

下面是结果集:

Name                                                          
--------------------------------------------------------------
BENNET, Abraham                                               
BLOTCHET-HALLS, Reginald                                      
CARSON, Cheryl                                                
DEFRANCE, Michel                                              
DEL CASTILLO, Innes                                           
DULL, Ann                                                     
GREEN, Marjorie                                               
GREENE, Morningstar                                           
GRINGLESBY, Burt                                              
HUNTER, Sheryl                                                
KARSEN, Livia                                                 
LOCKSLEY, Charlene                                            
MACFEATHER, Stearns                                           
MCBADDEN, Heather                                             
O’LEARY, Michael                                              
PANTELEY, Sylvia                                              
RINGER, Albert                                                
RINGER, Anne                                                  
SMITH, Meander                                                
STRAIGHT, Dean                                                
STRINGER, Dirk                                                
WHITE, Johnson                                                
YOKOMOTO, Akiko

 


 

系统函数


1.APP_NAME
返回当前会话的应用程序名称(如果应用程序进行了设置)。

语法
APP_NAME ( )

返回类型
nvarchar(128)

示例
下例检查启动此进程的客户端应用程序是否为 SQL 查询分析器会话。

DECLARE @CurrentApp varchar(35)
SET @CurrentApp = APP_NAME()
IF @CurrentApp <> ’MS SQL Query Analyzer’
PRINT ’This process was not started by a SQL Query Analyzer query session.’

 

2.CASE
计算条件列表并返回多个可能结果表达式之一。

CASE 具有两种格式:

简单 CASE 函数将某个表达式与一组简单表达式进行比较以确定结果。


CASE 搜索函数计算一组布尔表达式以确定结果。
两种格式都支持可选的 ELSE 参数。

语法
简单 CASE 函数:

CASE input_expression
    WHEN when_expression THEN result_expression
        [ ...n ]
    [
        ELSE else_result_expression
    END

CASE 搜索函数:

CASE
    WHEN Boolean_expression THEN result_expression
        [ ...n ]
    [
        ELSE else_result_expression
    END

参数
input_expression

是使用简单 CASE 格式时所计算的表达式。Input_expression 是任何有效的 Microsoft? SQL Server? 表
达式。

WHEN when_expression

使用简单 CASE 格式时 input_expression 所比较的简单表达式。When_expression 是任意有效的 SQL
Server 表达式。Input_expression 和每个 when_expression 的数据类型必须相同,或者是隐性转换。

n

占位符,表明可以使用多个 WHEN when_expression THEN result_expression 子句或 WHEN
Boolean_expression THEN result_expression 子句。

THEN result_expression

当 input_expression = when_expression 取值为 TRUE,或者 Boolean_expression 取值为 TRUE 时返
回的表达式。result expression 是任意有效的 SQL Server 表达式。

ELSE else_result_expression

当比较运算取值不为 TRUE 时返回的表达式。如果省略此参数并且比较运算取值不为 TRUE,CASE 将返回
NULL 值。Else_result_expression 是任意有效的 SQL Server 表达式。Else_result_expression 和所
有 result_expression 的数据类型必须相同,或者必须是隐性转换。

WHEN Boolean_expression

使用 CASE 搜索格式时所计算的布尔表达式。Boolean_expression 是任意有效的布尔表达式。

结果类型
从 result_expressions 和可选 else_result_expression 的类型集合中返回最高的优先规则类型。有关
更多信息,请参见数据类型的优先顺序。

结果值
简单 CASE 函数:
计算 input_expression,然后按指定顺序对每个 WHEN 子句的 input_expression = when_expression
进行计算。


返回第一个取值为 TRUE 的 (input_expression = when_expression) 的 result_expression。


如果没有取值为 TRUE 的 input_expression = when_expression,则当指定 ELSE 子句时 SQL Server
将返回 else_result_expression;若没有指定 ELSE 子句,则返回 NULL 值。
CASE 搜索函数:
按指定顺序为每个 WHEN 子句的 Boolean_expression 求值。


返回第一个取值为 TRUE 的 Boolean_expression 的 result_expression。


如果没有取值为 TRUE 的 Boolean_expression,则当指定 ELSE 子句时 SQL Server 将返回
else_result_expression;若没有指定 ELSE 子句,则返回 NULL 值。
示例
A. 使用带有简单 CASE 函数的 SELECT 语句
在 SELECT 语句中,简单 CASE 函数仅检查是否相等,而不进行其它比较。下面的示例使用 CASE 函数更改
图书分类显示,以使其更易于理解。

USE pubs
GO
SELECT   Category =
      CASE type
         WHEN ’popular_comp’ THEN ’Popular Computing’
         WHEN ’mod_cook’ THEN ’Modern Cooking’
         WHEN ’business’ THEN ’Business’
         WHEN ’psychology’ THEN ’Psychology’
         WHEN ’trad_cook’ THEN ’Traditional Cooking’
         ELSE ’Not yet categorized’
      END,
   CAST(title AS varchar(25)) AS ’Shortened Title’,
   price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type
GO

下面是结果集:

Category            Shortened Title           Price                     
------------------- ------------------------- --------------------------
Business            You Can Combat Computer S 2.99                      
Business            Cooking with Computers: S 11.95                     
Business            The Busy Executive’s Data 19.99                     
Business            Straight Talk About Compu 19.99                     

                                              avg
                                              ==========================
                                              13.73                     

Category            Shortened Title           Price                     
------------------- ------------------------- --------------------------
Modern Cooking      The Gourmet Microwave     2.99                      
Modern Cooking      Silicon Valley Gastronomi 19.99                     

                                              avg
                                              ==========================
                                              11.49                     

Category            Shortened Title           Price                     
------------------- ------------------------- --------------------------
Popular Computing   Secrets of Silicon Valley 20.00                     
Popular Computing   But Is It User Friendly?  22.95                     

                                              avg
                                              ==========================
                                              21.48                     

Category            Shortened Title           Price                     
------------------- ------------------------- --------------------------
Psychology          Life Without Fear         7.00                      
Psychology          Emotional Security: A New 7.99                      
Psychology          Is Anger the Enemy?       10.95                     
Psychology          Prolonged Data Deprivatio 19.99                     
Psychology          Computer Phobic AND Non-P 21.59                     

                                              avg
                                              ==========================
                                              13.50                     

Category            Shortened Title           Price                     
------------------- ------------------------- --------------------------
Traditional Cooking Fifty Years in Buckingham 11.95                     
Traditional Cooking Sushi, Anyone?            14.99                     
Traditional Cooking Onions, Leeks, and Garlic 20.95                     

                                              avg
                                              ==========================
                                              15.96                     

(21 row(s) affected)

B. 使用带有简单 CASE 函数和 CASE 搜索函数的 SELECT 语句
在 SELECT 语句中,CASE 搜索函数允许根据比较值在结果集内对值进行替换。下面的示例根据图书的价格范
围将价格(money 列)显示为文本注释。

USE pubs
GO
SELECT    ’Price Category’ =
      CASE
         WHEN price IS NULL THEN ’Not yet priced’
         WHEN price < 10 THEN ’Very Reasonable Title’
         WHEN price >= 10 and price < 20 THEN ’Coffee Table Title’
         ELSE ’Expensive book!’
      END,
   CAST(title AS varchar(20)) AS ’Shortened Title’
FROM titles
ORDER BY price
GO

下面是结果集:

Price Category        Shortened Title     
--------------------- --------------------
Not yet priced        Net Etiquette       
Not yet priced        The Psychology of Co
Very Reasonable Title The Gourmet Microwav
Very Reasonable Title You Can Combat Compu
Very Reasonable Title Life Without Fear   
Very Reasonable Title Emotional Security: 
Coffee Table Title    Is Anger the Enemy? 
Coffee Table Title    Cooking with Compute
Coffee Table Title    Fifty Years in Bucki
Coffee Table Title    Sushi, Anyone?      
Coffee Table Title    Prolonged Data Depri
Coffee Table Title    Silicon Valley Gastr
Coffee Table Title    Straight Talk About 
Coffee Table Title    The Busy Executive’s
Expensive book!       Secrets of Silicon V
Expensive book!       Onions, Leeks, and G
Expensive book!       Computer Phobic And 
Expensive book!       But Is It User Frien

(18 row(s) affected)

C. 使用带有 SUBSTRING 和 SELECT 的 CASE 函数
下面的示例使用 CASE 和 THEN 生成一个有关作者、图书标识号和每个作者所著图书类型的列表。

USE pubs
SELECT SUBSTRING((RTRIM(a.au_fname) + ’ ’+
   RTRIM(a.au_lname) + ’ ’), 1, 25) AS Name, a.au_id, ta.title_id,
   Type =
  CASE
    WHEN SUBSTRING(ta.title_id, 1, 2) = ’BU’ THEN ’Business’
    WHEN SUBSTRING(ta.title_id, 1, 2) = ’MC’ THEN ’Modern Cooking’
    WHEN SUBSTRING(ta.title_id, 1, 2) = ’PC’ THEN ’Popular Computing’
    WHEN SUBSTRING(ta.title_id, 1, 2) = ’PS’ THEN ’Psychology’
    WHEN SUBSTRING(ta.title_id, 1, 2) = ’TC’ THEN ’Traditional Cooking’
  END
FROM titleauthor ta JOIN authors a ON ta.au_id = a.au_id

下面是结果集:

Name                      au_id       title_id Type               
------------------------- ----------- -------- -------------------
Johnson White             172-32-1176 PS3333   Psychology         
Marjorie Green            213-46-8915 BU1032   Business           
Marjorie Green            213-46-8915 BU2075   Business           
Cheryl Carson             238-95-7766 PC1035   Popular Computing  
Michael O’Leary           267-41-2394 BU1111   Business           
Michael O’Leary           267-41-2394 TC7777   Traditional Cooking
Dean Straight             274-80-9391 BU7832   Business           
Abraham Bennet            409-56-7008 BU1032   Business           
Ann Dull                  427-17-2319 PC8888   Popular Computing  
Burt Gringlesby           472-27-2349 TC7777   Traditional Cooking
Charlene Locksley         486-29-1786 PC9999   Popular Computing  
Charlene Locksley         486-29-1786 PS7777   Psychology         
Reginald Blotchet-Halls   648-92-1872 TC4203   Traditional Cooking
Akiko Yokomoto            672-71-3249 TC7777   Traditional Cooking
Innes del Castillo        712-45-1867 MC2222   Modern Cooking     
Michel DeFrance           722-51-5454 MC3021   Modern Cooking     
Stearns MacFeather        724-80-9391 BU1111   Business           
Stearns MacFeather        724-80-9391 PS1372   Psychology         
Livia Karsen              756-30-7391 PS1372   Psychology         
Sylvia Panteley           807-91-6654 TC3218   Traditional Cooking
Sheryl Hunter             846-92-7186 PC8888   Popular Computing  
Anne Ringer               899-46-2035 MC3021   Modern Cooking     
Anne Ringer               899-46-2035 PS2091   Psychology         
Albert Ringer             998-72-3567 PS2091   Psychology         
Albert Ringer             998-72-3567 PS2106   Psychology         

(25 row(s) affected)

 

3.COALESCE
返回其参数中第一个非空表达式。

语法
COALESCE ( expression [ ,...n ] )

参数
expression

任何类型的表达式。

n

表示可以指定多个表达式的占位符。所有表达式必须是相同类型,或者可以隐性转换为相同的类型。

返回类型
将相同的值作为 expression 返回。

注释
如果所有自变量均为 NULL,则 COALESCE 返回 NULL 值。

COALESCE(expression1,...n) 与此 CASE 函数等价:

CASE
   WHEN (expression1 IS NOT NULL) THEN expression1
   ...
   WHEN (expressionN IS NOT NULL) THEN expressionN
   ELSE NULL

示例
在下面的示例中,显示包含三列有关某个雇员每年工资收入信息的 wages 表:hourly_wage、salary 和
commission。但是,每个雇员只能接受一种付款方式。若要确定支付给所有雇员的工资总额,请使用
COALESCE 函数接受在 hourly_wage、salary 和 commission 中找到的非空值。

SET NOCOUNT ON
GO
USE master
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = ’wages’)
   DROP TABLE wages
GO
CREATE TABLE wages
(
   emp_id      tinyint    identity,
   hourly_wage   decimal   NULL,
   salary      decimal    NULL,
   commission   decimal   NULL,
   num_sales   tinyint   NULL
)
GO
INSERT wages VALUES(10.00, NULL, NULL, NULL)
INSERT wages VALUES(20.00, NULL, NULL, NULL)
INSERT wages VALUES(30.00, NULL, NULL, NULL)
INSERT wages VALUES(40.00, NULL, NULL, NULL)
INSERT wages VALUES(NULL, 10000.00, NULL, NULL)
INSERT wages VALUES(NULL, 20000.00, NULL, NULL)
INSERT wages VALUES(NULL, 30000.00, NULL, NULL)
INSERT wages VALUES(NULL, 40000.00, NULL, NULL)
INSERT wages VALUES(NULL, NULL, 15000, 3)
INSERT wages VALUES(NULL, NULL, 25000, 2)
INSERT wages VALUES(NULL, NULL, 20000, 6)
INSERT wages VALUES(NULL, NULL, 14000, 4)
GO
SET NOCOUNT OFF
GO
SELECT CAST(COALESCE(hourly_wage * 40 * 52,
   salary,
   commission * num_sales) AS money) AS ’Total Salary’
FROM wages
GO

下面是结果集:

Total Salary
------------
20800.0000
41600.0000
62400.0000
83200.0000
10000.0000
20000.0000
30000.0000
40000.0000
45000.0000
50000.0000
120000.0000
56000.0000

(12 row(s) affected)

 


4.COLLATIONPROPERTY
返回给定排序规则的属性。

语法
COLLATIONPROPERTY( collation_name, property )

参数
collation_name

是排序规则的名称。collation_name 的数据类型为 nvarchar(128),且无默认值。

property

是排序规则的属性。property 的数据类型为 varchar(128),并且可以为下表中的任何值:

属性名称 描述
CodePage 排序规则的 nonUnicode 代码页