--字符串函数
--分别取左边个字符、右边个字符
Declare@S Varchar(200)='abcdefghijk'
SelectLeft(@S,3) as S1,Right(@S,3) as S2
Go
--从第个字符开始，取个字符
Declare@S Varchar(200)='abcdefghijk'
SelectSubString(@S,3,4) as S1
Go
--从第个字符开始，取个字符，中文字符当成个字对待
Declare@S Varchar(200)='a中国bcd'
SelectSubString(@S,3,4) as S1
Go
--返回首字符ASCII码值，仅英文字母有效
SelectASCII('a') as A1,ASCII('A') as A2
--返回ASCII码的字符
SelectCHAR(65) as A1,CHAR(97) as A2
--产生个空格，为便于查看显示结果，前后补上了字符x、y
Select'x'+Space(10)+'y'
--将数字转为固定宽度的字符串，总宽度，位小数，不足宽度前置空格
--若宽度不足，则返回* 号代替，若小数位不足，则四舍五入
SelectStr(1234,12,2) as A1,Str(1234.4,12,2) as A2,Str(12345.678,12,2) as A3,Str(12345.678,3,2)
--字符串替换，将字母bc 全部替换为xxx
SelectReplace('abcdefbc','bc','xxx')
--取子串位置，结果为
SelectCharIndex('bc','abcdef')
--取子串位置，从第个字符开始，结果为，表示未找到
SelectCharIndex('bc','abcdef',3)
--取子串位置，含通配符，%百分号表示任意字符，_下划线表示个字符，中文字也算个字符
SelectPatIndex('%cd%','abcdef') as A1,PatIndex('%c_e%','abcdef') as A2,PatIndex('%c_e%','abc中ef')as A3
--取字符串长度，Len将中文字当个字符，DataLength将个中文字当个字符
SelectLen('abc') as A1,Len('ab中')as A2,DataLength('abc') as A3,DataLength('ab中')as A4
--字符串反转
SelectReverse('abcdef')
 
--字符串大、小写
SelectUpper('abcdef'),Lower('aBCdEF')
--删除前后空格，字符串中间空格不受影响，为便于查看显示结果，前后补上了字符x、y
Select'x'+RTrim(' ab ')+'y' as A1,'x'+LTrim(' ab ')+'y' as A2,'x'+LTrim(RTrim(' ab '))+'y' as A3
--数值函数
--取绝对值
SelectABS(5) As A1,ABS(-5) as A2,ABS(-2.5) as A3
--四舍五入
SelectRound(2.34567,1) as A1,Round(2.34567,2) as A2,Round(2.34567,3) as A3
--较小整数
SelectFloor(2.0) as A1,Floor(2.3) as A2,Floor(2.8) as A3
--较大整数
SelectCeiling(2.0) as A1,Ceiling(2.3) as A2,Ceiling(2.8) as A3
--取随机数，不提供种子，返回值每次都变化
--理论上，叫伪随机数，实际应用中，理解为随机数就OK
SelectRand() as A1,Rand() as A2
--取随机数，给定种子，返回值始终相同
SelectRand(1000),Rand(100)
--取幂值，返回的次方，若返回值超过INT范围，则报错
SelectPower(2,8)
--判断是否为数字
SelectIsNumeric(2) as A1,IsNumeric('2.5') as A2,IsNumeric('2.5A') as A3
 
--日期型函数
--当前时间
SelectGetDate()
--往后天、往前天
SelectGetDate()+3 as A1,GetDate()-3 as A2
--往后秒、往前秒，用函数DateAdd
SelectDateAdd(Second,3,GetDate()) as A1,DateAdd(Second,-3,GetDate()) as A2
--往后分、往前分，用函数DateAdd
SelectDateAdd(Minute,3,GetDate()) as A1,DateAdd(Minute,-3,GetDate()) as A2
--往后小时、往前小时，用函数DateAdd
SelectDateAdd(Hour,3,GetDate()) as A1,DateAdd(Hour,-3,GetDate()) as A2
--往后天、往前天，用函数DateAdd
SelectDateAdd(Day,3,GetDate()) as A1,DateAdd(Day,-3,GetDate()) as A2
--往后月、往前月，用函数DateAdd，若碰到结果为月时，会自动处理好最大天数
SelectDateAdd(Month,3,'20140531') as A1,DateAdd(Month,-3,'20140531') as A2
--往后年、往前年，用函数DateAdd，若碰到结果为月时，会自动处理好最大天数
SelectDateAdd(Year,1,'20160229') as A1,DateAdd(Year,-1,'20160229') as A2
--取年、月、日
SelectYear(GetDate()) as A1,Month(GetDate()) as A2,Day(GetDate()) as A3
--取年、月、日，用函数DatePart
SelectDatePart(Year,GetDate()) As A1,DatePart(Month,GetDate()) As A2,
 
DatePart(Day,GetDate())As A3
--取月份最后一天，后月最后一天，前月最后一天**从SQL Server 2012开始支持
SelectEOMonth('20140603') as A1,EOMonth('20140603',2) as A2,EOMonth('20140603',-2) as A3
--取月份最后一天**SQL Server 2012之前版本计算，在当月号加个月，再减天就是
Declare@D DateTime='20140603'
SelectDateAdd(Day,-1,DateAdd(Month,1,Convert(Char(6),@D,112)+'01'))
--判断字符串是否是日期
SelectIsDate('2015/02/29') as A1,IsDate('20150228') as A2,
IsDate('20150228 25:00:00')as A3,IsDate('23:15') as A4
 
--数据类型转换
--SQL Server会自动根据数据类型优先级作隐式转换
--但有时可能并不是想要的效果
Declare@A1 Varchar(200),@A2 Int,@A3 Date,@A4 Varchar(200)
Select@A1=GetDate(),@A2='259',@A3=GetDate(),@A4=123.45
Select@A1 as A1,@A2 as A2,@A3 as A3,@A4 as A4
Go
--按指定格式将日期转换为字符
SelectConvert(VarChar(30),GetDate(),112) as A1 --YYYYMMDD
SelectConvert(VarChar(30),GetDate(),12) as A2 --YYMMDD
SelectConvert(VarChar(19),GetDate(),121) as A3 --YYYY-MM-DD HH:NN:SS
SelectConvert(VarChar(30),GetDate(),101) as A4 --MM/DD/YYYY
SelectConvert(VarChar(30),GetDate(),102) as A5 --YYYY.MM.DD
Go
--将数字、文字转换为日期
--数字就是1900-01-01，日期在SQL SERVER内部就是存储为数字的，小数部分对应小时:分:秒
Declare@A1 DateTime,@A2 DateTime,@A3 DateTime
Select@A1=10,@A2='20140603 08:05',@A3='2014-05-03 18:09:05'
Select@A1 as A1,@A2 as A2,@A3 as A3
Go
--将日期转换为数字
--注意A1、A2的值有点区别
Declare@A1 Int,@A2 Int,@A3 Numeric(12,2)
Select@A1=Cast(GetDate() as Int)
Select@A2=Cast(Cast(GetDate() as Numeric(12,2)) as Int)
Select@A3=Cast(GetDate() as Numeric(12,2))
Select@A1 as A1,@A2 as A2,@A3 as A3


转载于:https://blog.51cto.com/kinwar/1549080