常用函数
数字函数
SELECT ABS(-5) AS [ABS] -- ABS(x):返回x的绝对值 SELECT ROUND(5.161,1) AS [ROUND] -- ROUND(x,y):返回参数x的四舍五入的有y位小数的值,精度未变 SELECT CEILING(5.6) AS [CEILING] -- CEILING(x): 返回大于x的最小整数值 SELECT FLOOR(5.6) AS [FLOOR] --FLOOR(x): 返回小于x的最大整数值 SELECT RAND() AS [RAND] --RAND(): 返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值 SELECT SIGN(-5) AS [-SIGN] -- 返回代表数字x的符号的值:大于0的返回1，小于0的返回-1。0返回0 SELECT SIGN(-5) AS [+SIGN] SELECT SIGN(0) AS [SIGN(0)] SELECT EXP(2) AS [EXP] --EXP(x): 返回值e（自然对数的底）的x次方 SELECT SQRT(4) AS SQRT -- SQRT(x): 返回一个数的平方根
字符串函数
SELECT CHARINDEX('tte','pattern') AS [CHARINDEX] -- 返回字符串中指定表达式的起始位置 ,未找到返回0,不可使用通配符 SELECT PATINDEX ( '%tte%' ,'pattern') AS [%PATINDEX%] -- 返回字符串中指定表达式的起始位置 ,未找到返回0,可以使用统配符 SELECT PATINDEX ( 'tte%' ,'pattern') AS [%PATINDEX] --开始字符串一样则返回1，否则返回0，%前面指定的字符串具体查询，%后面部分进行模糊查询。 SELECT PATINDEX ( '%ern' ,'pattern') AS [PATINDEX%] --结尾不一样返回0,结尾一样等同于SELECT PATINDEX ( '%ern%' ,'pattern') SELECT PATINDEX ( 'ern' ,'pattern') AS [PATINDEX%] --指定字符串和表达式完全相等查询，指定字符串和表达式完全一样就返回1，否则返回数0. SELECT PATINDEX ( '%[ern]%' ,'pattern') AS 正则 --指定字符串中，只要任意一个值在表达式中出现，就返回第一次出现的位置 SELECT PATINDEX ( '%[^ern]%' ,'pattern') AS 正则 --表达式中除了指定字符串之外，返回其他任意字符串第一次出现的位置
SELECT LEN('AB CDEF ') AS [LEN] -- 返回指定字符串的字符个数，其中不包含尾随空格 SELECT REPLACE('123456789','456','***') --Replace()：用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式。 SELECT SUBSTRING('ABC',1,2) AS [SUBSTRING] --相当于excel中的mid。 SELECT STUFF('123456789123456789',2,3,'***') /*用于删除指定长度的字符，并在删除的位置插入新的字符/值。该方法有四个参数 参数1：用于指定要操作的字符串 参数2：用于指定要删除字符的起始位置 参数3：用于指定要删除字符的长度 参数4：用于指定在删除的位置插入的新的字符串/值。*/ SELECT LEFT('ABC',1) AS [LEFT] -- 返回字符串左边开始指个数的字符 SELECT LEFT('ABC',0) AS [LEFT 0] --返回空 SELECT RIGHT('ABC ',2) AS [RIGHT] --返回字符串右边开始指个数的字符，包含空格。
SELECT LTRIM(' AB C ') AS [LTRIM] --去除左边的空格 SELECT RTRIM(' AB C ') AS [RTRIM] --去除右边的空格 SELECT UPPER('A1 bcD') AS [UPPER] --用于返回指定英文字符串的大写形式的字符串。如果不为英文字符串，则返回原值 SELECT LOWER('A1 bcD') AS [LOWER] --用于返回指定英文字符串的大写形式的字符串。如果不为英文字符串，则返回原值 SELECT STR(123.45) AS STR --Str()：返回一个指定数值的等效字符串。该方法有三个参数： SELECT LEN(STR(123.45)) --参数1：指定要操作的数值。 SELECT STR(123.45,8) AS STR --参数2：指定要返回字符串的长度。默认值为10，即不指定默认长度为10，不足用空字符填充。 SELECT LEN(STR(123.45,8)) SELECT STR(123.45,8,1) AS STR --参数3：要返回的小数位数。默认值为0，即不指定就不保留小数，不足指定位数，用0补充。 SELECT LEN(STR(123.45,8,1)) SELECT '2'+SPACE(3)+'D' AS SPACE --用于返回指定数值长度的空格字符串 SELECT REVERSE(123) AS REVERSE --用于对指定字符串或数字进行反转，按照单个字符进行反转 SELECT REPLICATE('A',3)
日期函数
代表含义
SELECT GETDATE() --当前系统日期、时间 SELECT DATEADD(DAY,-2,'2018-10-15') --返回新的 DATETIME 值，DAY 可以换成其他DATEPART值。返回 2018-10-13 00:00:00.000 SELECT DATEDIFF(YY,'2017-09-11','2018-09-13') --返回两个指定日期时间指定日期部分的间隔，YY 可以换成其他DATEPART值。 返回1 SELECT DATEPART(MONTH, '2004-10-15') --返回代表指定日期的指定日期部分的整数 SELECT DATENAME(WEEKDAY, '2019-06-13') --返回代表指定日期的指定日期部分的字符串
--本月第一天 SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) --本月第20天 SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 19) --本月最后一天(下个月第一天减一天) SELECT DATEADD(DD,-1,DATEADD(mm, DATEDIFF(mm,0,getdate())+1, 0) ) 上月最后一天 SELECT DATEADD(DD,-DATEPART(DAY,GETDATE()),GETDATE()) --本周周一 SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) --本周周三(在周一的基础上加2，或者在第1个周二的基础上加周数) SELECT DATEADD(day,2, DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) ) SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 2) --上周日（在本周周一的基础上减1） SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), -1) SELECT DATEADD(DD,-1,DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) ) --本月第一个周一（先锁定第一周（无论周几），在看和0相差多少周） select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0) --本月第一个周六(时间平移，所有的dateadd和datediff都进行更改) select DATEADD(wk, DATEDIFF(wk,5,dateadd(dd,1-datepart(day,getdate()),getdate())), 5) --19年7月第一个周六 select DATEADD(wk, DATEDIFF(wk,5,dateadd(dd,1-datepart(day,'2019-07-01'),'2019-07-01')), 5) --19年7月第一个周日(有点特殊) select DATEADD(wk, DATEDIFF(wk,6,dateadd(dd,-datepart(day,getdate()),getdate()))+1, 6) select DATEADD(wk, DATEDIFF(wk,6,dateadd(dd,-datepart(day,'2019-07-01'),'2019-07-01'))+1, 6) select DATEADD(wk, DATEDIFF(wk,6,dateadd(dd,-datepart(day,'2019-07-15'),'2019-07-15'))+1, 6)
转换函数
CAST ( expression AS data_type)
CONVERT ( data_type [ ( length ) ] , expression ，[ style ] )
CAST和CONVERT比较：
1.cast一般更容易使用,convert的优点是可以格式化日期和数值;
2.convert一般用于日期和时间值，小数之间转换,cast一般用于小数转数值和字符型;
其他函数
ISNULL ( check_expression , replacement_value ) ：如果 check_expression不为NULL。返回 check_expression，否则返回replacement_value