SQLSERVER常用函数.docx
- 文档编号:13321646
- 上传时间:2023-06-13
- 格式:DOCX
- 页数:178
- 大小:85.41KB
SQLSERVER常用函数.docx
《SQLSERVER常用函数.docx》由会员分享,可在线阅读,更多相关《SQLSERVER常用函数.docx(178页珍藏版)》请在冰点文库上搜索。
SQLSERVER常用函数
SQLSERVER常用函数
SQL字符串函数
一、字符转换函数
4、STR()把数值型数据转换为字符型数据。
STR(
length指定返回的字符串的长度,decimal指定返回的小数位数。
如果没有指定长度,缺省的length值为10,decimal缺省值为0。
当length或者decimal为负值时,返回NULL;当length小于小数点左边(包括符号位)的位数时,返回length个*;先服从length,再取decimal;当返回的字符串位数小于length,左边补足空格。
三、取子串函数
3、SUBSTRING()
SUBSTRING(
四、字符串比较函数
1、CHARINDEX()返回字符串中某个指定的子串出现的开始位置。
CHARINDEX(<’substring_expression’>,
其中substring_expression是所要查找的字符表达式,expression可为字符串也可为列名表达式。
如果没有发现子串,则返回0值。
此函数不能用于TEXT和IMAGE数据类型。
2、PATINDEX()返回字符串中某个指定的子串出现的开始位置。
PATINDEX(<’%substring_expression%’>,
与CHARINDEX函数不同的是,PATINDEX函数的子串中可以使用通配符,且此函数可用于CHAR、VARCHAR和TEXT数据类型。
五、字符串操作函数
1、QUOTENAME()返回被特定字符括起来的字符串。
QUOTENAME(<’character_expression’>[,quote_character])其中quote_character
标明括字符串所用的字符,缺省值为“[]”。
2、REPLICATE()返回一个重复character_expression指定次数的字符串。
REPLICATE(character_expressioninteger_expression)如果integer_expression值为负值,则返回NULL。
3、REVERSE()将指定的字符串的字符排列顺序颠倒。
REVERSE(
4、REPLACE()返回被替换了指定子串的字符串。
REPLACE(
用string_expression3替换在string_expression1中的子串string_expression2。
4、SPACE()返回一个有指定长度的空白字符串。
SPACE(
5、STUFF()用另一子串替换字符串指定位置、长度的子串。
STUFF(
如果起始位置为负或长度值为负,或者起始位置大于character_expression1的长度,则返回NULL值。
如果length长度大于character_expression1中start_position以右的长度,则character_expression1只保留首字符。
六、数据类型转换函数
1、CAST()
CAST(
2、CONVERT()
CONVERT(
1)data_type为SQLServer系统定义的数据类型,用户自定义的数据类型不能在此使用。
2)length用于指定数据的长度,缺省值为30。
3)把CHAR或VARCHAR类型转换为诸如INT或SAMLLINT这样的INTEGER类型、结果必须是带正号或负号的数值。
4)TEXT类型到CHAR或VARCHAR类型转换最多为8000个字符,即CHAR或VARCHAR数据类型是最大长度。
5)IMAGE类型存储的数据转换到BINARY或VARBINARY类型,最多为8000个字符。
6)把整数值转换为MONEY或SMALLMONEY类型,按定义的国家的货币单位来处理,如人民币、美元、英镑等。
7)BIT类型的转换把非零值转换为1,并仍以BIT类型存储。
8)试图转换到不同长度的数据类型,会截短转换值并在转换值后显示“+”,以标识发生了这种截断。
9)用CONVERT()函数的style选项能以不同的格式显示日期和时间。
style是将DATATIME和SMALLDATETIME
数据转换为字符串时所选用的由SQLServer系统提供的转换样式编号,不同的样式编号有不同的输出格式。
七、日期函数
1、day(date_expression)
返回date_expression中的日期值
2、month(date_expression)
返回date_expression中的月份值
3、year(date_expression)
返回date_expression中的年份值
4、DATEADD()
DATEADD(
返回指定日期date加上指定的额外日期间隔number产生的新日期。
5、DATEDIFF()
DATEDIFF(
返回两个指定日期在datepart方面的不同之处,即date2超过date1的差距值,其结果值是一个带有正负号的整数值。
6、DATENAME()
DATENAME(
以字符串的形式返回日期的指定部分此部分。
由datepart来指定。
7、DATEPART()
DATEPART(
以整数值的形式返回日期的指定部分。
此部分由datepart来指定。
DATEPART(dd,date)等同于DAY(date)
DATEPART(mm,date)等同于MONTH(date)
DATEPART(yy,date)等同于YEAR(date)
8、GETDATE()
以DATETIME的缺省格式返回系统当前的日期和时间
1.当前系统日期、时间
selectgetdate()
2.dateadd在向指定日期加上一段时间的基础上,返回新的datetime值
例如:
向日期加上2天
selectdateadd(day,2,'2004-10-15')--返回:
2004-10-1700:
00:
00.000
3.datediff返回跨两个指定日期的日期和时间边界数。
selectdatediff(day,'2004-09-01','2004-09-18')--返回:
17
4.datepart返回代表指定日期的指定日期部分的整数。
SELECTDATEPART(month,'2004-10-15')--返回10
5.datename返回代表指定日期的指定日期部分的字符串
SELECTdatename(weekday,'2004-10-15')--返回:
星期五
6.day(),month(),year()--可以与datepart对照一下
select当前日期=convert(varchar(10),getdate(),120),当前时间=convert(varchar(8),getdate(),114)
selectdatename(dw,'2004-10-15')
select本年第多少周=datename(week,'2004-10-15'),今天是周几=datename(weekday,'2004-10-15')
GetDate()返回系统目前的日期与时间
DateDiff(interval,date1,date2)以interval指定的方式,返回date2与date1两个日期之间的差值
date2-date1
DateAdd(interval,number,date)以interval指定的方式,加上number之后的日期
DatePart(interval,date)返回日期date中,interval指定部分所对应的整数值
DateName(interval,date)返回日期date中,interval指定部分所对应的字符串名称
举例:
1.GetDate()用于sqlserver:
selectGetDate()
2.DateDiff('s','2005-07-20','2005-7-2522:
56:
32')返回值为514592秒
DateDiff('d','2005-07-20','2005-7-2522:
56:
32')返回值为5天
3.DatePart('w','2005-7-2522:
56:
32')返回值为2即星期一(周日为1,周六为7)
DatePart('d','2005-7-2522:
56:
32')返回值为25即25号
DatePart('y','2005-7-2522:
56:
32')返回值为206即这一年中第206天
DatePart('yyyy','2005-7-2522:
56:
32')返回值为2005即2005年
2.查询某个日期之间的记录数据:
select*fromt_business_productwherehit_count>1000andlast_date>'2005-02-01'
andlast_date<='2005-06-05'orderbyiddesc
go
查询最近一个月内的点击率大于100的记录数据:
select*fromt_business_productwherehit_count>100and
datediff(Dd,last_date,getdate())<=30orderbyiddesc
go
查询最近一周内的点击率大于100的记录数据:
select*fromt_business_productwherehit_count>100and
datediff(Dw,last_date,getdate())<=7orderbyiddesc
go
查询某一年(如2006年)点击率大于300的记录数据:
select*fromt_business_productwherehit_count>300and
DatePart(Yy,last_date)=2006orderbyiddesc
go
或
select*fromt_business_productwherehit_count>300and
DatePart(Year,last_date)=2006orderbyiddesc
go
3.SQLServer中文版的默认的日期字段datetime格式是yyyy-mm-ddThh:
mm:
ss.mmm
4.CAST和CONVERT
将某种数据类型的表达式显式转换为另一种数据类型。
CAST和CONVERT提供相似的功能。
语法
使用CAST:
CAST(expressionASdata_type)
使用CONVERT:
CONVERT(data_type[(length)],expression[,style])
参数
expression是任何有效的MicrosoftSQLServer表达式。
有关更多信息,请参见表达式。
data_type目标系统所提供的数据类型,包括bigint和sql_variant。
不能使用用户定义的数据类型。
有关可用的数据类型的更多信息,请参见数据类型。
Length:
nchar、nvarchar、char、varchar、binary或varbinary数据类型的可选参数。
Style:
日期格式样式,借以将datetime或smalldatetime数据转换为字符数据(nchar、nvarchar、char、varchar、nchar或nvarchar数据类型);或者字符串格式样式,借以将float、real、money或smallmoney
数据转换为字符数据(nchar、nvarchar、char、varchar、nchar或nvarchar数据类型)。
SQLServer支持使用科威特算法的阿拉伯样式中的数据格式。
SQLServer和Oracle常用函数对比
1.绝对值
S:
selectabs(-1)value
O:
selectabs(-1)valuefromdual
2.取整(大)
S:
selectceiling(-1.001)value
O:
selectceil(-1.001)valuefromdual
3.取整(小)
S:
selectfloor(-1.001)value
O:
selectfloor(-1.001)valuefromdual
4.取整(截取)
S:
selectcast(-1.002asint)value
O:
selecttrunc(-1.002)valuefromdual
5.四舍五入
S:
selectround(1.23456,4)value1.23460
O:
selectround(1.23456,4)valuefromdual1.2346
6.e为底的幂
S:
selectExp
(1)value2.7182818284590451
O:
selectExp
(1)valuefromdual2.71828182
7.取e为底的对数
S:
selectlog(2.7182818284590451)value1
O:
selectln(2.7182818284590451)valuefromdual;1
8.取10为底对数
S:
selectlog10(10)value1
O:
selectlog(10,10)valuefromdual;1
9.取平方
S:
selectSQUARE(4)value16
O:
selectpower(4,2)valuefromdual16
10.取平方根
S:
selectSQRT(4)value2
O:
selectSQRT(4)valuefromdual2
11.求任意数为底的幂
S:
selectpower(3,4)value81
O:
selectpower(3,4)valuefromdual81
12.取随机数
S:
selectrand()value
O:
selectsys.dbms_random.value(0,1)valuefromdual;
13.取符号
S:
selectsign(-8)value-1
O:
selectsign(-8)valuefromdual-1
14.圆周率
S:
SELECTPI()value3.1415926535897931
O:
不知道
15.sin,cos,tan参数都以弧度为单位
例如:
selectsin(PI()/2)value得到1(SQLServer)
16.Asin,Acos,Atan,Atan2返回弧度
17.弧度角度互换(SQLServer,Oracle不知道)
DEGREES:
弧度-〉角度
RADIANS:
角度-〉弧度
数值间比较
18.求集合最大值
S:
selectmax(value)valuefrom
(select1value
union
select-2value
union
select4value
union
select3value)a
O:
selectgreatest(1,-2,4,3)valuefromdual
19.求集合最小值
S:
selectmin(value)valuefrom
(select1value
union
select-2value
union
select4value
union
select3value)a
O:
selectleast(1,-2,4,3)valuefromdual
20.如何处理null值(F2中的null以10代替)
S:
selectF1,IsNull(F2,10)valuefromTbl
O:
selectF1,nvl(F2,10)valuefromTbl
21.求字符序号
S:
selectascii('a')value
O:
selectascii('a')valuefromdual
22.从序号求字符
S:
selectchar(97)value
O:
selectchr(97)valuefromdual
23.连接
S:
select'11'+'22'+'33'value
O:
selectCONCAT('11','22') 33valuefromdual
23.子串位置--返回3
S:
selectCHARINDEX('s','sdsq',2)value
O:
selectINSTR('sdsq','s',2)valuefromdual
23.模糊子串的位置--返回2,参数去掉中间%则返回7
S:
selectpatindex('%d%q%','sdsfasdqe')value
O:
oracle没发现,但是instr可以通过第四个参数控制出现次数
selectINSTR('sdsfasdqe','sd',1,2)valuefromdual返回6
24.求子串
S:
selectsubstring('abcd',2,2)value
O:
selectsubstr('abcd',2,2)valuefromdual
25.子串代替返回aijklmnef
S:
SELECTSTUFF('abcdef',2,3,'ijklmn')value
O:
SELECTReplace('abcdef','bcd','ijklmn')valuefromdual
26.子串全部替换
S:
没发现
O:
selectTranslate('fasdbfasegas','fa','我')valuefromdual
27.长度
S:
len,datalength
O:
length
28.大小写转换lower,upper
29.单词首字母大写
S:
没发现
O:
selectINITCAP('abcddsafdf')valuefromdual
30.左补空格(LPAD的第一个参数为空格则同space函数)
S:
selectspace(10)+'abcd'value
O:
selectLPAD('abcd',14)valuefromdual
31.右补空格(RPAD的第一个参数为空格则同space函数)
S:
select'abcd'+space(10)value
O:
selectRPAD('abcd',14)valuefromdual
32.删除空格
S:
ltrim,rtrim
O:
ltrim,rtrim,trim
33.重复字符串
S:
selectREPLICATE('abcd',2)value
O:
没发现
34.发音相似性比较(这两个单词返回值一样,发音相同)
S:
SELECTSOUNDEX('Smith'),SOUNDEX('Smythe')
O:
SELECTSOUNDEX('Smith'),SOUNDEX('Smythe')fromdual
SQLServer中用SELECTDIFFERENCE('Smithers','Smythers')比较soundex的差
返回0-4,4为同音,1最高
日期函数
35.系统时间
S:
selectgetdate()value
O:
selectsysdatevaluefromdual
36.前后几日
直接与整数相加减
37.求日期
S:
selectconvert(char(10),getdate(),20)value
O:
selecttrunc(sysdate)valuefromdual
selectto_char(sysdate,'yyyy-mm-dd')valuefromdual
38.求时间
S:
selectconvert(char(8),getdate(),108)value
O:
selectto_char(sysdate,'hh24:
mm:
ss')valuefromdual
39.取日期时间的其他部分
S:
DATEPART和DATENAME函数(第一个参数决定)
O:
to_char函数第二个参数决定
参数---------------------------------下表需要补充
yearyy,yyyy
quarterqq,q(季度)
monthmm,m(mO无效)
dayofyeardy,y(O表星期)
daydd,d(dO无效)
weekwk,ww(wkO无效)
weekdaydw(O不清楚)
Hourhh,hh12,hh24(hh12,hh24S无效)
minutemi,n(nO无效)
secondss,s(sO无效)
millisecondms(O无效)
----------------------------------------------
40.当月最后一天
S:
不知道
O:
selectLAST_DAY(sysdate)valuefromdual
41.本星期的某一天(比如星期日)
S:
不知道
O:
SELECTNext_day(sysdate,7)vauleFROMDUAL;
42.字符串转时间
S:
可以直接转或者selectcast('2004-09-08'asdatetime)value
O:
SELE
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQLSERVER 常用 函数