SQLSever--2005 函数大全

表达式：是常量、变量、列或函数等与运算符的任意组合。以下参数中表达式类型是指表达式经运算后返回的值的类型

字符串函数

函数名称

参数

示例

说明

ascii

(字符串表达式)

selectascii('abc') 返回 97

返回字符串中最左侧的字符的ASCII码。

char

(整数表达式)

selectchar(100) 返回 d

把ASCII码转换为字符。

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

charindex

(字符串表达式1,字符串表达式2[,整数表达式])

selectcharindex('ab','BCabTabD')返回3

selectcharindex('ab','BCabTabD',4)返回6

在字符串2中查找字符串1，如果存在返回第一个匹配的位置，如果不存在返回0。如果字符串1和字符串2中有一个是null则返回null。

可以指定在字符串2中查找的起始位置。

difference

(字符串表达式1，字符串表达式2)

selectdifference('Green','Greene')返回4

返回一个0到4的整数值，指示两个字符表达式的之间的相似程度。0表示几乎不同或完全不同，4表示几乎相同或完全相同。注意相似并不代表相等

left

(字符串表达式，整数表达式)

selectleft('abcdefg',2) 返回 ab

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

right

(字符串表达式，整数表达式)

selectright('abcdefg',2) 返回fg

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

len

(字符串表达式)

selectlen('abcdefg')返回 7

selectlen('abcdefg     ') 返回7

返回指定字符串表达式的字符数，其中不包含尾随空格。

lower

(字符串表达式)

selectlower('ABCDEF')返回 abcdef

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

upper

(字符串表达式)

selectupper('abcdef')返回 ABCDEF

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

ltrim

(字符串表达式)

selectltrim('    abc')返回 abc

返回删除了前导空格之后的字符表达式。

rtrim

(字符串表达式)

selectrtrim('abc    ')返回 abc

返回删除了尾随空格之后的字符表达式。

patindex

(字符串表达式1，字符串表达式2)

selectpatindex('%ab%','123ab456')返回4

selectpatindex('ab%','123ab456')返回0

selectpatindex('___ab%','123ab456')返回1

selectpatindex('___ab_','123ab456')返回0

在字符串表达式1中可以使用通配符，此字符串的第一个字符和最后一个字符通常是%。

%表示任意多个字符，_表示任意字符

返回字符串表达式2中字符串表达式1所指定模式第一次出现的起始位置。没有找到返回0

reverse

(字符串表达式)

selectreverse('abcde')返回 edcba

返回指定字符串反转后的新字符串

space

(整数表达式)

select'a'+space(2)+'b' 返回 a  b

返回由指定数目的空格组成的字符串。

str

（float型小数[,总长度[,小数点后保留的位数]]）

selectstr(123.451)返回   123(123前面有空格)

selectstr(123.451,3)返回123

selectstr(123.451,7,3)返回123.451

selectstr(123.451,7,1)返回123.5

selectstr(123.451,5,3)返回123.5

selectstr(123.651,2)返回**

返回由数字转换成的字符串。返回字符数不到总长度的前面补空格，超过总长度的截断小数位。如果需要截断整数位则返回**。

注意在截断时遵循四舍五入

总长度。它包括小数点、符号、数字以及空格。默认值为10。

小数点后最多保留16位。默认不保留小数点后面的数字

stuff

(字符串表达式1，开始位置，长度，字符串表达式2)

selectstuff('abcdef',2,2,'123')

返回 a123def

在字符串表达式1中在指定的开始位置删除指定长度的字符，并在指定的开始位置处插入字符串表达式2。返回新字符串

substring

(字符串表达式，开始位置，长度)

selectsubstring('abcdef',2,2)返回bc

select substring('85.008',1,case when charindex('.','85.008')=0 then len('85.008') else charindex('.','85.008')+2 end)

返回子字符串

replace

(字符串表达式1，字符串表达式2，字符串表达式3)

selectreplace('abcttabchhabc','abc','123')

返回123tt123hh123

用字符串表达式3替换字符串表达式1中出现的所有字符串表达式2的匹配项。返回新的字符串

日期和时间函数

函数名称

参数

示例

说明

dateadd

(日期部分，数字，日期)

selectdateadd(year,45,'1990-12-11')

返回2035-12-11 00:00:00.000

selectdateadd(month,45,'1990-12-11')

返回1994-09-11 00:00:00.000

selectdateadd(mm,45,'1990-12-11')

返回1994-09-11 00:00:00.000

selectdateadd(qq,12,'1990-12-11')

返回1993-12-11 00:00:00.000

selectdateadd(hh,12,'1990-12-11')

返回1990-12-11 12:00:00.000

selectdateadd(yy,-12,'1990-12-11')

返回1978-12-11 00:00:00.000

返回给指定日期加上一个时间间隔后的新的日期值。

数字：用于与指定的日期部分相加的值。如果指定了非整数值，则将舍弃该值的小数部分，舍弃时不遵循四舍五入。

日期：指定的原日期

在此函数中dw，dy，dd效果一样都表示天

datediff

(日期部分，开始日期，结束日期)

selectdatediff(yy,'1990-12-11','2008-9-10')

返回 18

selectdatediff(mm,'2007-12-11','2008-9-10')

返回 9

返回两个指定日期的指定日期部分的差的整数值。

在计算时由结束日期减去开始日期

在此函数中dw，dy，dd效果一样都表示天

datename

(日期部分，日期)

selectdatename(mm,'2007-12-11')返回12

selectdatename(dw,'2007-12-11')返回星期二

selectdatename(dd,'2007-12-11')返回11

Select datename (hh,'2006-01-19 21:12:30.343') 返回21

selectdatepart(n,'2006-01-19 21:12:30.343')返回12

返回表示指定日期的指定日期部分的字符串。

dw表示一星期中星期几，wk表示一年中的第几个星期

dy表示一年中的第几天

Hh 表示时间中的小时数

N 表示时间中的分钟数

datepart

(日期部分，日期)

selectdatepart(mm,'2007-12-11')返回12

selectdatepart(dw,'2007-12-11')返回3

selectdatepart(dd,'2007-12-11')返回11

Select datepart(hh,'2006-01-19 21:12:30.343') 返回21

selectdatepart(n,'2006-01-19 21:12:30.343')返回12

返回表示指定日期的指定日期部分的整数。

wk表示一年中的第几个星期

dy表示一年中的第几天,

dw表示一星期中星期几，返回整数默认1为星期天

Hh 表示时间中的小时数

N 表示时间中的分钟数

getdate

无参数

selectgetdate()

返回2009-04-28 18:57:24.153

返回当前系统日期和时间。

day

(日期)

selectday('2007-12-11')返回11

返回一个整数，表示指定日期的天的部分。

等价于datepart(dd,日期)

month

(日期)

selectmonth('2007-12-11')返回12

返回一个整数，表示指定日期的月的部分。

等价于datepart(mm,日期)

year

(日期)

selectyear('2007-12-11')返回2007

返回一个整数，表示指定日期的年的部分。

等价于datepart(yy,日期)

getutcdate

无参数

selectgetutcdate()

返回2009-04-28 10:57:24.153

返回表示当前的UTC(世界标准时间)时间。即格林尼治时间（GMT）

日期部分（指定要返回新值的日期的组成部分。下表列出了Microsoft SQL Server 2005可识别的日期部分及其缩写。）

日期部分

含义

缩写

year

年

yy, yyyy

quarter

季

qq, q

month

月

mm, m

dayofyear

天（请看函数中的说明）

dy, y

day

天（请看函数中的说明）

dd, d

week

星期

wk, ww

weekday

天（请看函数中的说明）

dw, w

hour

小时

hh

minute

分钟

mi, n

second

秒

ss, s

millisecond

毫秒

ms

数学函数

函数名称

参数

示例

说明

abs

(数值表达式)

selectabs(-23.4)返回23.4

返回指定数值表达式的绝对值（正值）

pi

无参数

selectpi()返回3.14159265358979

返回π的值

cos

(浮点表达式)

select  cos(pi()/3)返回0.5

返回指定弧度的余弦值

sin

(浮点表达式)

select  sin(pi()/6)返回0.5

返回指定弧度的正弦值

cot

(浮点表达式)

selectcot(pi()/4)返回1

返回指定弧度的余切值

tan

(浮点表达式)

selecttan(pi()/4)返回1

返回指定弧度的正切值

acos

(浮点表达式)

selectacos(0.5)返回1.0471975511966

返回其余弦是所指定的数值表达式的弧度，求反余弦

asin

(浮点表达式)

selectasin(0.5)返回0.523598775598299

返回其正弦是所指定的数值表达式的弧度，求反正弦

atan

(浮点表达式)

selectatan(1)返回0.785398163397448

返回其正切是所指定的数值表达式的弧度，求反正切

degrees

(数值表达式)

selectdegrees(pi()/4) 返回45

返回以弧度指定的角的相应角度。

radians

(数值表达式)

selectradians(180.0)

返回3.1415926535897931

返回指定度数的弧度值。注意如果传入整数值则返回的结果将会省略小数部分

exp

(浮点表达式)

selectexp(4)返回54.5981500331442

返回求e的指定次幂，e=2.718281…

log

(浮点表达式)

selectlog(6)返回1.79175946922805

返回以e为底的对数，求自然对数。

Log10

(浮点表达式)

selectlog10(100)返回2

返回以10为底的对数

ceiling

(数值表达式)

selectceiling(5.44)返回6

selectceiling(-8.44)返回-8

返回大于或等于指定数值表达式的最小整数。

floor

(数值表达式)

selectfloor(5.44)返回5

selectfloor(-8.44)返回-9

返回小于或等于指定数值表达式的最大整数。

power

(数值表达式1，数值表达式2)

selectpower(5,2)返回25

返回数值表达式1的数值表达式2次幂

sqrt

(数值表达式)

selectsqrt(25)返回5

返回数值表达式的平方根

sign

(数值表达式)

selectsign(6)返回1

selectsign(-6)返回-1

selectsign(0)返回0

表达式为正返回+1

表达式为负返回-1

表达式为零返回0

rand

([整数表达式])

selectrand(100)返回0.715436657367485

selectrand()返回0.28463380767982

selectrand()返回0.0131039082850364

--

declare   @max   float,@min   float

set   @min=10.11

set   @max=10.13

selectcast(cast((rand()*(@max-@min)+@min)asnumeric(9,2))asvarchar(50))

--

返回从0到1之间的随机float值。

整数表达式为种子，使用相同的种子产生的随机数相同。即使用同一个种子值重复调用RAND()会返回相同的结果。

不指定种子则系统会随机生成种子。

--

一定范围内的随机数

--

round

(数值表达式[,长度[,操作方式]])

selectround(1236.555,2)返回1236.560

selectround(1236.555,2,1)返回1236.550

selectround(1236.555,0)返回1237.000

selectround(1236.555,-1)返回1240.000

selectround(1236.555,-1,1)返回1230.000

selectround(1236.555,-2)返回1200.000

selectround(1236.555,-3)返回1000.000

selectround(1236.555,-4)返回0.000

selectround(5236.555,-4)出现错误

selectround(5236.555,-4,1)返回0.000

返回一个数值，舍入到指定的长度。注意返回的数值和原数值的总位数没有变化。

长度：舍入精度。如果长度为正数，则将数值舍入到长度指定的小数位数。如果长度为负数，则将数值小数点左边部分舍入到长度指定的长度。注意如果长度为负数，并且大于小数点前的数字个数，则将返回0。如果长度为负数并且等于小数点前的数字个数且操作方式为四舍五入时，最前面的一位小于5返回0，大于等于5会导致错误出现，如果操作方法不是四舍五入时则不会出现错误，返回结果一律为0。

操作方式：默认为0遵循四舍五入，指定其他整数值则直接截断。

数据类型转换函数

（以下两种函数功能类似,但是convert在进行日期转换时还提供了丰富的样式，cast只能进行普通的日期转换）

函数名称

参数

示例

描述

convert

(数据类型[(长度)]，表达式[，样式]）

selectconvert(nvarchar,123) 返回123

select N'年龄：'+convert(nvarchar,23)

返回 年龄：23（注意：如果想要在结果中正确显示中文需要在给定的字符串前面加上N，加N是为了使数据库识别Unicode字符）

select  convert(nvarchar,getdate())

返回04 28 2009 10:21PM

select  convert(nvarchar,getdate(),101)

返回04/28/2009

select  convert(nvarchar,getdate(),120)

返回2009-04-28 12:22:21

select  convert(nvarchar(10),getdate(),120)

返回2009-04-28

 

将一种数据类型的表达式显式转换为另一种数据类型的表达式。

长度：如果数据类型允许设置长度，可以设置长度，例如

varchar(10)

样式：用于将日期类型数据转换为字符数据类型的日期格式的样式。见下表

cast

(表达式as数据类型[(长度)])

selectcast(123 asnvarchar)返回123

select N'年龄:'+cast(23 asnvarchar)

返回 年龄：23

将一种数据类型的表达式显式转换为另一种数据类型的表达式。

 

系统函数

函数名称

参数

示例

描述

newid

无参数

selectnewid()

返回2E6861EF-F4DB-4FFE-85EA-638242F2E5F2

selectnewid()

返回09BBDE6F-47C2-4E2E-81E8-AFC50592280C

返回一个GUID（全局唯一表示符）值

isnumeric

(任意表达式)

selectisnumeric(1111)返回1

selectisnumeric('123rr')返回0

selectisnumeric('123')返回1

判断表达式是否为数值类型或者是否可以转换成数值。

是返回1，不是返回0

isnull

(任意表达式1，任意表达式2)

selectisnull(null,N'没有值')返回 没有值

selectisnull(N'具体的值',N'没有值')

返回 具体的值

如果任意表达式1不为NULL，则返回它的值；否则，在将任意表达式2的类型转换为任意表达式1的类型（如果这两个类型不同）后，返回任意表达式2的值。

isdate

（任意表达式）

selectisdate(getdate()) 返回1

selectisdate('1988-1-1')返回1

selectisdate('198')返回0

确定输入表达式是否为有效日期或可转成有效的日期。

是返回1，不是返回0

排名函数

排名函数的常用使用格式：函数名() over (order by列名[asc|desc][,列名……])

注意：返回结果集会根据使用排名函数时指定的列进行排序，因此不要在from子句后面再次使用order by，会导致排名混乱。

函数名称

参数

示例

描述

row_number

无参数

select roductID,[Name],ListPrice,row_number()over(orderby ListPrice desc)as rank

from Production.Product

为结果集内每一行进行编号，从1开始后面行依次加1。

rank

无参数

select ProductID,[Name],ListPrice,rank()over(orderby ListPrice desc)as rank

from Production.Product

如果两个或多个行与一个排名关联，则每个关联行将得到相同的排名，排名不连续。例如，如果有价格最高的两种产品的价格相同，它们将并列第一。由于已有两行排名在前，所以具有下一个最高价格的产品将排名第三。该排名等于该行之前的所有行数加一。因此，RANK函数并不总返回连续整数。

dense_rank

无参数

select ProductID,[Name],ListPrice,dense_rank()over(orderby ListPrice desc)as rank

from Production.Product

如果两个或多个行与一个排名关联，则每个关联行将得到相同的排名，排名连续。例如，如果有价格最高的两种产品的价格相同，它们将并列第一，下一个最高价格的产品将排名第二。因此，DENSE_RANK函数返回的数字没有间断，并且始终具有连续的排名。

聚合函数

聚合函数对一组值执行计算,并返回单个值。除了COUNT以外，聚合函数都会忽略空值。聚合函数经常与SELECT语句的GROUP BY子句一起使用。

聚合函数的常用使用格式：函数名([all|distinct]表达式)  

all:默认值，对所有的值进行聚合函数运算包含重复值。

distinct:消除重复值后进行聚合函数运算。

 

函数名称

示例

描述

avg

SELECTavg(VacationHours)as'平均休假小时数'

FROM HumanResources.Employee

WHERE Title LIKE'Vice President%'

返回25

返回组中各值的平均值。空值将被忽略。表达式为数值表达式。

count

SELECTcount(*)FROM Production.Product 返回504

SELECTcount(Color)FROM Production.Product 返回256

SELECTcount(distinct Color)FROM Production.Product

返回9 

返回组中的项数。COUNT(*)返回组中的项数。包括 NULL 值和重复项。如果指定表达式则忽略空值。表达式为任意表达式。

min

selectmin(ListPrice)from Production.Product返回0

返回组中的最小值。空值将被忽略。表达式为数值表达式，字符串表达式，日期。

max

selectmax(ListPrice)from Production.Product

返回3578.27

返回组中的最大值。空值将被忽略。表达式为数值表达式，字符串表达式，日期。

sum

SELECTsum(SickLeaveHours)as'总病假小时数'

FROM HumanResources.Employee

WHERE Title LIKE'Vice President%';

返回97

返回组中所有值的和。空值将被忽略。表达式为数值表达式