sql server 2008以上版本常用函数简要说明

/**************************************************************************

一, 字符串函数

***************************************************************************/

--1,ascii(字符串)
--  说明：返回字符串中最左侧的字符的ASCII 码,如果是多字符，返回字符串中最左侧的字符的ASCII 码。
--  例：
    select ascii('abc') 
--  返回 97

--2,char(整数)
--  说明：把ASCII 码转换为字符。介于0 和 255 之间的整数。如果该整数表达式不在此范围内，将返回NULL 值.
--  例：
    select char(99) 
--  返回 c

--3,charindex(字符串1,字符串2,[整数])
--  说明：在字符串2 中查找字符串 1，如果存在返回第一个匹配的位置，如果不存在返回0。如果字符串 1 和字符串 2 中有一个是null 则返回 null。可以指定在字符串2 中查找的起始位置。
--  例：
    select charindex('ov','iloveyou')
--  返回 3
    select charindex('ov','iloveyou',3)
--  返回 3
    select charindex('ov','iloveyou',4)
--  返回 0
--  注：可用于字符串查询，与like功能相似

--4,difference(字符串1,字符串2)
--  说明：返回一个0 到 4 的整数值，指示两个字符表达式的之间的相似程度。
--        0 表示几乎不同或完全不同，
--        4 表示几乎相同或完全相同。
--        注意相似并不代表相等
--  例：
    select difference('iloveyou','iloveyoutoo')
--  返回 4  
--  注：感觉用于进行模拟匹配或是分词检索的时候会用到

--5,left(字符串,整数)
--  说明：返回字符串中从左边开始指定个数的字符。
--  例：
    select left('iloveyou',6)
--  返回 ilovey 

--6,right(字符串,整数)
--  说明：返回字符串中从右边开始指定个数的字符。
--  例：
    select right('iloveyou',6)
--  返回 oveyou 

--7,len(字符串)
--  说明：返回指定字符串表达式的字符数，其中不包含尾随空格。
--  例：
    select len('iloveyou')
--  返回 8 

--8,lower(字符串)
--  说明：返回大写字符数据转换为小写的字符表达式。
--  例：
    select lower('I LOVE YOU')
--  返回 i love you

--9,upper(字符串)
--  说明：返回小写字符数据转换为大写的字符表达式。
--  例：
    select upper('i love you')
--  返回 I LOVE YOU

--10,ltrim(字符串)
--  说明：返回删除了前导空格之后的字符表达式。
--  例：
    select ltrim('  i love you')
--  返回 i love you

--11,rtrim(字符串)
--  说明：返回删除了尾随空格之后的字符表达式。
--  例：
    select rtrim('i love you  ')
--  返回 i love you

--12,patindex(字符串1,字符串2)
--  说明：在字符串1 中可以使用通配符，此字符串的第一个字符和最后一个字符通常是%。
--        %表示任意多个字符，_表示任意字符
--        返回字符串2中字符串1 所指定模式第一次出现的起始位置。没有找到返回0
--  例：
    select patindex('%ey%','i loveyou')
--  返回 6  空格算一位
    select patindex('il%','i loveyou')
--	返回0
    select patindex('___ov%','i loveyou')
--	返回1
    select patindex('___ov_','i loveyou')
--	返回0
--  注：可用于字符串查询，与like,charindex功能相似

--13,reverse(字符串)
--  说明：返回指定字符串反转后的新字符串
--  例：
    select reverse('i love you')
--  返回 uoy evol i

--14,space(整数)
--  说明：返回由指定数目的空格组成的字符串。
--  例：
    select 'i' + SPACE(1) + 'love' + SPACE(2) + 'you'
--  返回 i love  you

--15,str(float型小数(数值型字符串)[,总长度[,小数点后保留的位数]])
--  说明：返回由数字转换成的字符串。返回字符数不到总长度的前面补空格，超过总长度的截断小数位。如果需要截断整数位则返回**。
--  例：
    select str('3.1415926',3,2)
	select str(3.1415926,3,2)
--	返回 3.1
    select str('3.1415926',7,3)
	select str(3.1415926,7,3)
--	返回  3.142  因为小数点前面位数不够，用二个空格补
    select str(314.15926,7,1)
--	返回  314.2
    select str(314.15926,2)
--	返回**

--16,stuff(字符串1,开始位置，长度,字符串2)
--  说明：在字符串1中在指定的开始位置删除指定长度的字符，并在指定的开始位置处插入字符串2。返回新字符串
--  例：
    select stuff('i very love you',3,4,'not')
--  返回 i not love you

--17,substring(字符串,开始位置，长度)
--  说明：从字符串指定的开始位置截取指定长度的字符
--  例：
    select SUBSTRING('i love you',3,8)
--  返回 love you

--18,replace(字符串1,字符串2,字符串3)
--  说明：从字符串中找到与字符串2相同的字符用字符串3替换
--  例：
    select replace('i love you','love','hate')
--  返回 i hate you

--19,Concat(字符串1,字符串2(整形，小数型) [,字符串n])
--  说明：将多个字符串相连接
--  例：
    select Concat('i love you','love','hate')
--  返回 i love youlovehate
	select Concat('i love you',520,'hate')
--  返回 i love you520hate
	select Concat('i love you',5.20,'hate')
--  返回 i love you5.20hate
	select Concat('i love you',null,'hate')
--  返回 i love youhate
--  注： sql server2012新增


/**************************************************************************

二, 日期与时间函数
    注：日期部分含义
	   年 	        yy, yyyy
	   季度 	    qq, q
       月 	        mm, m
       年中的日 	dy, y
       日 	        dd, d
       周 	        wk, ww
       星期 	    dw, w
       小时 	    hh
       分钟 	    mi, n
       秒 	        ss, s
       毫秒 	    ms
       微妙 	    mcs
       纳秒 	    ns

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

***************************************************************************/

--1,dateadd(日期部分,数字,日期)
--  说明：返回给指定日期加上一个时间间隔后的新的日期值。
--        数字：用于与指定的日期部分相加的值。如果指定了非整数值，则将舍弃该值的小数部分，舍弃时不遵循四舍五入。
--        日期：指定的原日期
--  例：
    select dateadd(year,15,'2015-07-03')
--  返回 2030-07-03 00:00:00.000
    select dateadd(year,15.4,'2015-07-03')
--  返回 2030-07-03 00:00:00.000
    select dateadd(year,15.8,'2015-07-03')
--  返回 2030-07-03 00:00:00.000
    select dateadd(month,15,'2015-07-03')
--  返回 2016-10-03 00:00:00.000
    select dateadd(mm,15,'2015-07-03')
--  返回 2016-10-03 00:00:00.000
    select dateadd(qq,2,'2015-07-03')
--  返回 2016-01-03 00:00:00.000
    select dateadd(hh,3,'2015-07-03 14:00:00')
--  2015-07-03 17:00:00.000
    select dateadd(yy,-12,'2015-07-03')
--  返回 2003-07-03 00:00:00.000

--2,datediff(日期部分,开始日期,结束日期)
--  说明：返回两个指定日期的指定日期部分的差的整数值。在计算时由结束日期减去开始日期
--  例：
    select datediff(year,'2014-08-02','2015-07-03')
--  返回 1
    select datediff(m,'2014-08-02','2015-07-03')
--  返回 11
    select datediff(d,'2014-08-02','2015-07-03')
--  返回 335

--3,datepart(日期部分,日期)
--  说明：返回表示指定日期的指定日期部分的整数。
--  例：
    select datepart(year,'2015-07-03')
--  返回 2015
    select datepart(m,'2015-07-03')
--  返回 7
    select datepart(d,'2015-07-03')
--  返回 3
    select datepart(wk,'2015-07-03')
--  返回 27

--4,getdate()
--  说明：返回当前系统日期和时间。
--  例：
    select getdate()
--  返回 2015-07-03 14:12:34.540

--5,day(日期)
--  说明：返回一个整数，表示指定日期的天的部分。
--  例：
    select day('2015-07-03')
--  返回 3
--  注：等价于datepart(dd, 日期)

--6,month(日期)
--  说明：返回一个整数，表示指定日期的月的部分。
--  例：
    select month('2015-07-03')
--  返回 7
--  注：等价于datepart(mm, 日期)

--7,year(日期)
--  说明：返回一个整数，表示指定日期的年的部分。
--  例：
    select year('2015-07-03')
--  返回 2015
--  注：等价于datepart(yy, 日期)

--8,datefromparts(表示年的数值,表示月的数值,表示日的数值)
--  说明：返回一个日期，用输入的年，月，日数。如有小数，则进行舍去
--  例：
    select datefromparts(2015,7,3)
--  返回 2015-07-03
    select datefromparts(2015.2,7.1,3.2)
--  返回 2015-07-03
--  注：sql server 2012 新增

--9,datetimefromparts(表示年的数值,表示月的数值,表示日的数值,表示小时的数值,表示分钟的数值,表示秒的数值,表示毫秒的数值)
--  说明：返回一个日期带时间，用输入的年，月，日,小时，分钟，秒，毫秒数。如有小数，则进行舍去
--  例：
    select datetimefromparts(2015,7,3,14,26,34,234)
--  返回 2015-07-03 14:26:34.233
--  注：sql server 2012 新增

--10,datetime2fromparts(表示年的数值,表示月的数值,表示日的数值,表示小时的数值,表示分钟的数值,表示秒的数值,分数,精度)
--  说明：返回一个日期带时间，用输入的年，月，日,小时，分钟，秒数。如有小数，则进行舍去。分数表示显示的豪秒值，精度表示位数
--  例：
    select datetime2fromparts(2015,7,3,14,26,34,5,1)
--  返回 2015-07-03 14:26:34.5
    select datetime2fromparts(2015,7,3,14,26,34,5,2)
--  返回 2015-07-03 14:26:34.05
    select datetime2fromparts(2015,7,3,14,26,34,50,3)
--  返回 2015-07-03 14:26:34.050
--  注：sql server 2012 新增

--11,timefromparts(表示小时的数值,表示分钟的数值,表示秒的数值,分数,精度)
--  说明：返回一个时间，用输入的小时，分钟，秒数。如有小数，则进行舍去。分数表示显示的豪秒值，精度表示位数
--  例：
    select timefromparts(14,26,34,5,1)
--  返回 14:26:34.5
    select timefromparts(14,26,34,5,2)
--  返回 14:26:34.05
    select timefromparts(14,26,34,50,3)
--  返回 14:26:34.050
--  注：sql server 2012 新增

--12,eomonth(开始日期[,添加的月数值])
--  说明：可以根据指定日期取得日期当月的最后一天,可以根据添加的月份进行增减
--  例：
    select eomonth('2015-07-03')
--  返回 2015-07-31
    select eomonth('2015-07-03',1)
--  返回 2015-08-31
    select eomonth('2015-07-03',-1)
--  返回 2015-06-30
--  注：sql server 2012 新增

/**************************************************************************

三, 数学函数

***************************************************************************/

--1,abs(数值)
--  说明：返回指定数值表达式的绝对值（正值）
--  例：
    select abs(3.14)
--  返回 3.14
    select abs(-3.14)
--  返回 3.14

--2,pi()
--  说明：返回π的值
--  例：
    select pi()
--  返回 3.14159265358979

--3,cos(浮点值)
--  说明：返回指定弧度的余弦值
--  例：
    select cos(pi()/3)
--  返回 0.5

--4,sin(浮点值)
--  说明：返回指定弧度的正弦值
--  例：
    select sin(pi()/5)
--  返回 0.587785252292473

--5,tan(浮点值)
--  说明：返回指定弧度的正切值
--  例：
    select tan(pi()/4)
--  返回 1

--6,acos(浮点值)
--  说明：返回其余弦是所指定的数值表达式的弧度，求反余弦
--  例：
    select acos(0.5)
--  返回 1.0471975511966

--7,asin(浮点值)
--  说明：返回其正弦是所指定的数值表达式的弧度，求反正弦
--  例：
    select asin(0.5)
--  返回 0.523598775598299

--8,atan(浮点值)
--  说明：返回其正切是所指定的数值表达式的弧度，求反正切
--  例：
    select atan(1)
--  返回 0.785398163397448

--9,degrees(数值)
--  说明：返回以弧度指定的角的相应角度
--  例：
    select degrees(pi()/4)
--  返回 45

--10,radians(数值)
--  说明：返回指定度数的弧度值。注意如果传入整数值则返回的结果将
--  例：
    select radians(90)
--  返回 1

--11,exp(浮点值)
--  说明：e 的指定次幂，e=2.718281…
--  例：
    select exp(5)
--  返回 148.413159102577

--12,log(浮点值)
--  说明：返回以 e 为底的对数，求自然对数。
--  例：
    select log(5)
--  返回 1.6094379124341

--13,Log10(浮点值)
--  说明：返回以 10 为底的对数
--  例：
    select log10(5)
--  返回 0.698970004336019

--14,ceiling(数值)
--  说明：返回大于或等于指定数值表达式的最小整数
--  例：
    select ceiling(5.41)
--  返回 6
    select ceiling(-5.41)
--  返回 -5

--15,floor(数值)
--  说明：返回小于或等于指定数值表达式的最小整数
--  例：
    select floor(5.41)
--  返回 5
    select floor(-5.41)
--  返回 -6

--16,power(数值1,数值2)
--  说明：返回数值1 的数值表 2 次幂
--  例：
    select power(5.41,2)
--  返回 29.27

--17,sqrt(数值)
--  说明：返回数值表达式的平方根
--  例：
    select sqrt(36)
--  返回 6

--18,sign(数值)
--  说明：判断数值正负
--  例：
    select sign(36)
--  返回 1
    select sign(-36)
--  返回 -1
    select sign(0)
--  返回 0

--19,rand([整数])
--  说明：返回从0 到 1 之间的随机 float 值。整数表达式为种子，使用相同的种子产生随机数相同。即使用同一个种子值重复调用RAND() 会返回相同的结果。不指定种子则系统会随机生成种子。
--  例：
    select rand()
--  返回 0.0405279031825291
    select rand()
--  返回0.3503520565606
    select rand(10)
--  返回 0.713759689954247
    select rand(10)
--  返回 0.713759689954247

--20,round(数值[,长度[,操作方式]])
--  说明：返回一个数值，舍入到指定的长度。注意返回的数值和原数值的总位数没有变化。
--        长度：舍入精度。如果长度为正数，则将数值舍入到长度指定的小数位数。如果长度为负数，则将数值小数点左边部分舍入到长度指定的长度。
--        注意如果长度为负数并且大于小数点前的数字个数，则将返回0。如果长度为负数并且等于小数点前的数字个数且操作方式为四舍五入时，最前面的一位小于5 返回 0，大于等于 5 导致错误出现，如果操作方法不是四舍五入时则不会出现错误，返回结果一律为0。
--         操作方式：默认为 0 遵循四舍五入，指定其他整数值则直接截断。
--  例：
    select round(314.15926,2)
--  返回 314.16000
    select round(314.15926,2,1)
--  返回 314.15000
    select round(314.15926,-2)
--  返回 300.00000
    select round(314.15926,-3)
--  返回 0.00000


/**************************************************************************

四, 数据类型转换函数

    日期样式：
	不带世纪数位 (yy)
    带世纪数位 (yyyy)
    
    默认设置mm dd yyyy hh:miAM（或 PM）             标准输入/输出-0 或 100

            mm/dd/yyyy                         101  美国
			
			yy.mm.dd                           102  ANSI

            dd/mm/yy                           103  英国/法国

            dd.mm.yy                           104  德国

            dd-mm-yy                           105  意大利

            yyyy-mm-ddhh:mi:ss(24h)            120  ODBC 规范

***************************************************************************/

--1,convert(数据类型[(长度)],表达式[,样式])
--  说明：将一种数据类型的表达式显式转换为另一种数据类型的表达式。
--        长度：如果数据类型允许设置长度，可以设置长度，例如varchar(10)
--        样式：用于将日期类型数据转换为字符数据类型的日期格式的样式。
--  例：
    select convert(nvarchar,123) 
--	返回 123
	select N'年龄：'+convert(nvarchar,34)
--  返回 年龄：34（注意：如果想要在结果中正确显示中文需要在给定的字符串前面加上 N，加 N 是为了使数据库识别Unicode 字符）
    select convert(nvarchar ,getdate())
--  返回 07  4 2015  9:44AM
	select convert(nvarchar ,getdate(),101)
--  返回 07/04/2015
	select convert(nvarchar ,getdate(),102)
--  返回 2015.07.04
	select convert(nvarchar ,getdate(),103)
--  返回 04/07/2015
	select convert(nvarchar ,getdate(),104)
--  返回 04.07.2015
	select convert(nvarchar ,getdate(),105)
--  返回 04-07-2015
	select convert(nvarchar ,getdate(),120)
--  返回 2015-07-04 09:53:34

--2,cast(表达式 as 数据类型[(长度)])
--  说明：将一种数据类型的表达式显式转换为另一种数据类型的表达式。
--        长度：如果数据类型允许设置长度，可以设置长度，例如varchar(10)
--  例：
    select cast('2015' as int)
--  返回 2015

--  注：convert 在进行日期转换时还提供了丰富的样式，cast 只能进行普通的日期转换

--3,parse(字符串 as 数据类型[可选字符串])
--  说明：返回转换为所请求的数据类型的表达式的结果。
--        长度：如果数据类型允许设置长度，可以设置长度，例如varchar(10)
--  例：
    select parse('2015' as int)
--  返回 2015
    SELECT PARSE('€345,98' AS money USING 'de-DE')
--  返回 345.98
--  注： sql server 2012新增


/**************************************************************************

五, 系统函数

***************************************************************************/

--1,newid()
--  说明：返回一个GUID（全局唯一表示符）值
--  例：
    select newid()
--  返回 B2FF08EB-BEA1-4825-9C43-E0C403EB886B
    select newid()
--  返回 ABBC41DF-3FEB-4DB1-8438-BD11A67FD790

--2,isnumeric(任意表达式)
--  说明：判断表达式是否为数值类型或者是否可以转换成数值。是返回1，不是返回 0
--  例：
    select isnumeric(123)
--  返回 1
    select isnumeric(123.45)
--  返回 1
    select isnumeric(0.45)
--  返回 1
    select isnumeric('12br')
--  返回 0
    select isnumeric(null)
--  返回 0
    select isnumeric('12.45')
--  返回 1

--3,isnull(任意表达式1,任意表达式2)
--  说明：返回具体的值如果任意表达式1 不为 NULL，则返回它的值；否则，在将任意表达式2 的类型转换为任意表达式 1 的类型（如果这两个类型不同）后，返回任意表式2 的值。
--  例：
    select isnull(123,'abc')
--  返回 123
    select isnull(null,'abc')
--  返回 abc

--4,isdate（任意表达式）
--  说明：确定输入表达式是否为有效日期或可转成有效的日期。是返回1，不是返回 0
--  例：
    select isdate(GETDATE())
--  返回 1
    select isdate('2015-7-6')
--  返回 1
    select isdate('2015-7')
--  返回 0

/**************************************************************************

六, 排名函数
     
	排名函数的常用使用格式：函数名() over (order by 列名 [asc|desc][,列名……])
	注意：返回结果集会根据使用排名函数时指定的列进行排序，因此不要在 from 子句后面再次使用 order by，会导致排名混乱。
***************************************************************************/

--1,row_number()
--  说明：为结果集内每一行进行编号，从1开始后面行依次加 1。
--  例：
    select ROW_NUMBER() over (order by areaname desc) as newName,id,areaName,areaparent from areas
--  注：此方法多用于分页处理

--2,rank()
--  说明：如果两个或多个行与一个排名关联，则每个关联行将得到相同的排名，排名不连续。例如，如果有价格最高的两种产品的价格相同，它们将列第一。由于已有两行排名在前，所以具有下一个最高价格的产品将排名第三。该排名等于该行之前的所有行数加一。因此，RANK 函数并不总返回连续整数。
--  例：
    select rank() over (order by areaParent desc) as newName,id,areaName,areaparent from areas

--3,dense_rank()
--  说明：如果两个或多个行与一个排名关联，则每个关联行将得到相同的排名，排名连续。例如，如果有价格最高的两种产品的价格相同，它们将并列第一，下一个最高价格的产品将排名第二。因此，DENSE_RANK 函数返回的数字没有间断，并且始终具有连续的排名
--  例：
    select dense_rank() over (order by areaParent desc) as newName,id,areaName,areaparent from areas