2、MSSQL系统内置函数
数学函数
rand、floor、sign
--4、RAND()和RAND(X)函数：返回一个随机浮点值n（0<=n<=1.0);
SELECT RAND(),RAND(),RAND(); ----不带参数时生成的随机数不同；
SELECT RAND(5),RAND(5),RAND(3);--带相同的参数时生成相同的随机数；
--RAND函数生成[0,1)之间的数
SELECT RAND()
--生成n-m之间的数，包含n不包含m
--SELECT FLOOR(RAND()*(m-n)+n)
--1-100
SELECT FLOOR(RAND()*(99)+1)
--5、ROUND(X,Y)函数：四舍五入，返回最接近于参数X的值，其值保留到小数点后Y位，
--若Y为负数，则小数点左起Y位均为0；
SELECT ROUND(33333.333333,2),ROUND(33333.33333,-2),ROUND(33333,-2);
--6、SIGN(X)函数：返回参数的符号；
SELECT SIGN(3),SIGN(0),SIGN(-3),SIGN(3.33),SIGN(-33.33);
ceiling、power
--7、CEILING(X)函数：返回不小于X的最小整数；
SELECT CEILING(33.333),CEILING(33.666),CEILING(-33.333),CEILING(-33.666);
--8、FLOOR(X)函数：返回比X小的最大整数；
SELECT FLOOR(33.333),FLOOR(-33.333);
--9、POWER(X,Y)函数：返回x的y次方；
SELECT POWER(2,3),POWER(3,0),POWER(5,-2),POWER(5.0,-2),POWER(5.000,-2);
square、exp、log
--10、SQUARE(X)函数：返回x的平方；
SELECT SQUARE(0),SQUARE(3),SQUARE(-3),SQUARE(3.3),SQUARE(2.0);
--11、EXP(X)函数：返回e的x乘方；
SELECT EXP(3),EXP(-3),EXP(0),EXP(3.3);
--12、LOG(X)函数：返回x的自然对数,x不能为0和负数；
SELECT LOG(3.3),LOG(3),LOG(4);
--13、LOG10(X)函数：返回x的基数为10的对数，如100的基数为10的对数是2；
SELECT LOG10(1000),LOG10(1),LOG10(5);
radians、degrees、sin、asin
--14、RADIANS(X)函数：将参数x由角度转换为弧度；
SELECT RADIANS(45.0),RADIANS(45),RADIANS(-45.0);
--15、DEGREES(X):函数：将参数x由弧度转换为角度；
SELECT DEGREES(33),DEGREES(33.33333),DEGREES(-33.33333),DEGREES(PI());
--16、SIN(X)函数：返回x的正弦，x为弧度值；
SELECT SIN(30),SIN(-30),SIN(PI()),SIN(PI()/2),ROUND(SIN(PI()),0);
--17、ASIN(X)函数：返回x的反正弦，即返回正弦为x的值；
SELECT ASIN(1),ASIN(0),ASIN(-1);
cos、acos、tan、atan、cot
--18、COS(X)函数：返回x的余弦，x为弧度值；
SELECT COS(30),COS(-30),COS(PI()),COS(1),COS(0);
--19、ACOS(X)函数：返回x的反余弦，即返回余弦为x的值；
SELECT ACOS(1),ACOS(0),ACOS(-1),ACOS(0.3434235),ROUND(ACOS(0.3434235),1);
--20、TAN(X)函数：返回x的正切，x为弧度值；
SELECT TAN(1),TAN(0),TAN(-1);
--21、ATAN(X)函数：返回x的反正切，即返回正切为x的值；
SELECT TAN(1),ATAN(1.5574077246549),ATAN(0); ------TAN和ATAN互为反函数；
--22、COT(X)函数：返回x的余切；
SELECT COT(3),1/TAN(3),COT(-3);--------------------COT和TAN互为倒数；
日期和时间函数
查询某个月的天数
此表值函数的原理：通过dateadd()增加一个月的时间，减去当前日期的日数，得到当前月的最后一天，然后通过day()函数得到当前月的天数
--方法一
CREATE FUNCTION [dbo].[DayofMon] (@nowdate DATETIME)
RETURNS INT
AS
BEGIN
DECLARE @num INT;
SET @num = (
SELECT DAY(DATEADD(dd,-DAY(@nowdate),DATEADD(mm,1,@nowdate)))
);
RETURN @num;
END;
--SELECT dbo.DayofMon('2021-5-15')
--SELECT dbo.DayofMon(GETDATE());
--方法二
DECLARE @DaysOfMonth INT,@NowDate DATETIME;
--SELECT @NowDate = GETDATE();
SELECT @NowDate = '2021-06-16';
SELECT @DaysOfMonth = (select DAY(DATEADD(dd,-DAY(@NowDate),DATEADD(mm,1,@NowDate))));
SELECT @DaysOfMonth;
--方法三
DECLARE @dt DATETIME;
SELECT @dt = '2021-05-02';
SELECT 32-DAY(@dt+32-DAY(@dt))
SELECT 32-DAY(CONVERT(DATETIME,'20161001')+32-DAY(CONVERT(DATETIME,'20161001')))
获取某月最后一天的日期
SELECT CONVERT(NVARCHAR(20),DATEADD(dd,-1*DAY(GETDATE()),DATEADD(MM,1,GETDATE())),23)
DATEADD和DATEDIFF
--2011-01-15 00:00:00.000
SELECT DATEADD(mm,1,'2010-12-15')
--2010-12-31 00:00:00.000
SELECT DATEADD(dd,-DAY('2010-12-15'),'2011-01-15 00:00:00.000')
--3
SELECT DATEDIFF(dd,'2021-06-02','2021-06-05')
当天周几
SELECT DATENAME(WEEKDAY,GETDATE())
当周第几周
SELECT DATENAME(WEEK,GETDATE())+1
DATEPART ( datepart , date )
返回的是int类
SELECT DATEPART(year, '2017-12-05')
,DATEPART(month, '2017-12-05')
,DATEPART(day, '2017-12-05')
,DATEPART(dayofyear, '2017-12-05')
,DATEPART(weekday, '2017-12-05');
DAY ( date )
返回一个整型数字表示该月的第几天
SELECT DAY('2017-12-05') --返回5,如果05改为35则会抛出异常
ISDATE ( expression )
是日期返回1否则返回0
IF ISDATE('2017-12-05 10:41:32.680') = 1
PRINT 'VALID'
ELSE
PRINT 'INVALID';
IF ISDATE('2017-12-35 10:41:32.680') = 1
PRINT 'VALID'
ELSE
PRINT 'INVALID';
--输出 VALID INVALID
MONTH ( date )
SELECT MONTH('2017-12-15 10:41:32.680') --返回12
1 1
SELECT MONTH('2017-12-15 10:41:32.680') --返回12
YEAR ( date )
SELECT YEAR ('2017-12-15 10:41:32.680') --返回2017
1 1
SELECT YEAR ('2017-12-15 10:41:32.680') --返回2017
时间转换
select getdate()--获取完整日期 具体到毫秒 2012-02-15 11:41:24.903
select convert(varchar,getdate(),120) --具体到秒 2012-02-15 11:46:04
select convert(varchar,getdate(),121) 2012-02-15 11:46:43.810
select convert(nvarchar,getdate(),20) 2012-02-15 11:45:42
select convert(nvarchar,getdate(),21) 2012-02-15 11:47:37.340
select convert(nvarchar,getdate(),22) 02/15/12 11:48:01 AM
select convert(nvarchar,getdate(),23) 2012-02-15
select convert(nvarchar,getdate(),24) 11:48:42
select convert(nvarchar,getdate(),25) 2012-02-15 11:49:00.030
select convert(nvarchar,getdate(),100) 02 15 2012 11:51AM
select convert(nvarchar,getdate(),101) 02/15/2012
select convert(nvarchar,getdate(),102) 2012.02.15
select convert(nvarchar,getdate(),103) 15/02/2012
select convert(nvarchar,getdate(),104) 15.02.2012
select convert(nvarchar,getdate(),105) 15-02-2012
select convert(nvarchar,getdate(),106) 15 02 2012
select convert(nvarchar,getdate(),107) 02 15, 2012
select convert(varchar(10),getdate(),108) --时间 11:47:15
select convert(nvarchar,getdate(),109) 02 15 2012 11:54:16:250AM
select convert(nvarchar,getdate(),110) 02-15-2012
select convert(nvarchar,getdate(),111) 2012/02/15
select convert(nvarchar,getdate(),112) 20120215
select convert(nvarchar,getdate(),113) 15 02 2012 11:55:18:293
select convert(nvarchar,getdate(),114) 11:55:32:373
字符串函数
ASCII ( character_expression )
返回字符ascii值
SELECT ASCII('A') AS A, ASCII('B') AS B,
ASCII('a') AS a, ASCII('b') AS b,
ASCII(1) AS [1], ASCII(2) AS [2];
CHAR ( integer_expression )
--将一个int转为字符
SELECT CHAR(39),CHAR(78)
SELECT ASCII('A'),CHAR(65) --65 A
查找字符串
CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] ) --查找字符的位置从索引1开始
SELECT CHARINDEX('B','ABCD') --返回2
select CHARINDEX('a','abcdefg')--1 第一个字符索引是1不是0
select CHARINDEX('a','abcdefg',1)--1
select CHARINDEX('b','abcdefg',1)--2
select CHARINDEX('b','abcdefg',2)--2
select CHARINDEX('b','abcdefg',3)--0
select CHARINDEX('cd','abcdefg',3)--3
--------------PATINDEX ( '%pattern%' , expression )
--'%pattern%'的用法类似于 like '%pattern%'的用法，
--也就是模糊查找其pattern字符串是否是expression找到，找到并返回其第一次出现的位置。
--返回指定表达式中模式第一次出现的开始位置
select PATINDEX('%cd%','abcdefg')--3
select PATINDEX('%_cd%','abcdefg')--2
select PATINDEX('%ca%','abcdefg')--0
--返回0-则为纯数字(支持正负数，小数点)
SELECT PATINDEX('%[^0-9|.|-|+]%','2.2')--返回0
--返回0-则为纯整数
select PATINDEX('%[^0-9]%', '2.2')--返回非0
LEFT 和RIGHT
--返回字符表达式最左侧指定数目的字符串
select LEFT('abcdefg',0)--''
select LEFT('abcdefg',1)--'a'
select LEFT('abcdefg',2)--'ab'
select LEFT('abcdefg',100)--'abcdefg'
select LEFT('abcdefg',-1)--传递到 left 函数的长度参数无效。
--返回字符表达式最右侧指定数目的字符串
select RIGHT('abcdefg',0)--''
select RIGHT('abcdefg',1)--'g'
select RIGHT('abcdefg',2)--'fg'
select RIGHT('abcdefg',100)--'abcdefg'
select RIGHT('abcdefg',-1)--传递到 right 函数的长度参数无效。
STR
--STR函数将一个浮点类型数字转换字符串，
SELECT STR(3.1415,8,5)
--将3.1415，转换为字符串长度8位，小数位5，超过了数字长度，
--左侧补空格所以结果是: 3.14150 左侧有有个空格
SELECT STR(3.1415,7,5) --结果:3.14150 左侧没空格
截取字符串
--SUBSTRING（被截取字符串,开始位置,从1开始,长度）
SELECT SUBSTRING('abcd',1,1)--a
SELECT SUBSTRING('abcd',2,2)--bc
SELECT SUBSTRING('abcd',2,5)--bcd
SELECT SUBSTRING('abcd',2,0)--''
SELECT SUBSTRING('abcd',2,-1)--传递到 substring 函数的长度参数无效
--SUBSTRING 与charindex一起使用
DECLARE @strSizeAmount VARCHAR(100),@SizeAmount VARCHAR(100)
SET @SizeAmount='AAA@BB@CCC'
SET @strSizeAmount = SUBSTRING(@SizeAmount, 1, CHARINDEX('@', @SizeAmount) - 1)
SELECT @strSizeAmount --返回AAA
--获取剩下的字符串
SELECT @SizeAmount=SUBSTRING(@SizeAmount,CHARINDEX('@', @SizeAmount) + 1,LEN(@SizeAmount)-LEN(@strSizeAmount))
SELECT @SizeAmount -- 返回BB@CCC
REPLACE
--replace（被搜索字符串,要被替换的字符串，替换的字符串）
select REPLACE('abcdefg','cd','a')--abaefg
select REPLACE('abcdefg','cd','')--abefg
REPLICATE
--返回指定次数重复的表达式
select REPLICATE('a',4)--aaaa
select REPLICATE('abc|',4)--abc|abc|abc|abc|
STUFF
--删除指定长度的字符,并在指定的起点处插入另一组字符
--stuff(character_expression , start , length ,character_expression)
-----character_expression被搜索字符串
-----start开始位置
-----length要删除的长度
-----character_expression替换字符串
select STUFF('abcd',1,4,'1')--1
select STUFF('abcdefg',2,3,'1111')--a1111efg
select STUFF('abcdefg',2,3,'11')--a11efg
SELECT STUFF(',A,B,C',1,1,'') --A,B,C
--返回指定个数空格的字符串
select 'A'+ space(2)+'B'--A B
元数据函数
错误捕获函数
| 函数 | 说明 |
|---|---|
| ERROR_MESSAGE() | 返回错误的描述。 |
| ERROR_NUMBER() | 返回错误号。 |
| ERROR_SEVERITY() | 返回错误的严重级别。错误的严重级别是一个从0到25的整数。 |
| ERROR_STATE() | 返回错误的状态号。错误状态是一个整数，可以唯一地表示系统错误的原因。 |
| ERROR_LINE() | 返回例程中导致出错的行号。 |
| ERROR_PROCEDURE() | 返回发生错误的存储过程名或触发器名。 |
| 严重级****别 | 说****明 |
|---|---|
| 0～10 | 信息性消息。不会引发系统错误 |
| 11～16 | 用户可以更正的错误，例如违反了外键或主键规则 |
| 17 | 非致命的、不重要的资源错误 |
| 18 | 非致命的内部错误 |
| 19 | 致命的、不重要的资源错误 |
| 20 | 当前进程中的致命错误 |
| 21 | 所有进程中的致命数据库错误 |
| 22 | 致命的表完整性错误 |
| 23 | 致命的数据库完整性错误 |
| 24 | 致命的硬件错误 |
| 25 | 致命的系统错误 |
<span data-wiz-span="data-wiz-span" style="font-size: 0.917rem;">BEGIN TRY
SELECT 5 / 0
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE(),ERROR_NUMBER(),ERROR_SEVERITY(),
ERROR_STATE(),ERROR_LINE(),ERROR_PROCEDURE()
END CATCH</span>
https://www.cnblogs.com/xugang/archive/2011/04/09/2010216.html
<span data-wiz-span="data-wiz-span" style="font-size: 0.917rem;"> --任何用户都可以指定 0 到 18 之间的严重级别。
-- [0,10]的闭区间内，不会跳到catch；
-- 如果是[11,19],则跳到catch；
-- 如果[20,无穷)，则直接终止数据库连接；
BEGIN TRY
RAISERROR('test',10,1) --10不会跳转到catch中，16则会跳转
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE(),ERROR_NUMBER(),ERROR_SEVERITY(),
ERROR_STATE(),ERROR_LINE(),ERROR_PROCEDURE()
END CATCH</span>
浙公网安备 33010602011771号