七、SQLSERVER函数
一、SQL Server 系统函数
1.字符串函数
1.ascii(string) -- 字符串转为ASCII码 2.char(int) -- ASCII码转为字符串 3.concat(string) --多个字符串连接成一个字符串 4.len(string) -- 长度 5.datalength(string) -- 字节数 6.left(string,index) -- 左裁减 7.right(index,string) -- 右裁减 8.ltrim(string) -- 去左空格 9.rtrim(string) -- 去右空格 10.upper(string) -- 大写 11.lower(string) -- 小写 12.substring(string,index,index) --截取字符串 13.charindex(string,separator) -- 字符索引 14.space(int) --产生空格 15.replicate(string,int) --产生重复 16.replace(string,old,new) -- 查找并替换 17.stuff(string,index,index) -- 按位置替换 18.reverse(string) --字符串反转 19.str(string,int,int) -- 数值转换为字符值 20.string_agg(string,separator) --将分组字符串行连接成一个字符串
【例】字符串函数
--1.ascii:返回字符串转为ASCII码
select ascii('A'); --返回 65
--2.char:返回ASCII码转为字符串
select char(65); --返回 A
---3.concat:返回多个字符串连接成一个字符串
select concat('中','国'); --返回 中国
--4.len:返回一个字符串的长度
select len('中国'); --返回 2
--5.datalength:返回一个字符串的字节数
select datalength('A中国'); --返回 5
--6.left:返回一个字符串左边的一部分字符
select left('A中国',2); --返回 A中
--7.right:返回一个字符串右边的一部分字符
select right('A中国',2); --返回 中国
--8.ltrim:去掉字符串右边全部空格
select ltrim(' 中国'); --返回 中国
--9.ltrim:去掉字符串左边全部空格
select rtrim('中国 '); --返回 中国
--10.upper:字母大写转换函数
select upper('a中国'); --返回 A中国
--11.lower:字母小写转换函数
select lower('A中国'); --返回 a中国
--12.substring:字符串截取函数 SQL的下标从1开始
select substring('中国',1, 1); --返回 中
--13.charindex:字符串查找函数
select charIndex('国','中国'); --返回 2
--14.space:产生空格函数
select space(2); --返回 ' '
--15.replicate:产生字符串重复函数
select replicate('abc',2); --返回 abcabc
--16.replace:字符替换函数
select replace('abc','a','A'); --返回 Abc
--17.stuff:字符替换函数
select stuff('abc',2,1,'A'); --返回 aAc
--18.reverse:字符串反转
select reverse('abc'); --返回 cba
--19.str:将数值转换为字符串函数
select str(123,3,0)--返回 123
--20.将分组字符串行连接成一个字符串
select string_agg(学生姓名,';') 学生
from 学生表
group by 班级; --返回 马玉;雷军
2.日期和时间函数
1.getDate() 获取当前系统日期 2.dateAdd() 添加指定日期后的日期 3.dateDiff() 获取两个时间差 4.dateName() 获取指定日期名称 5.datePart() 获取指定日期部分的整数形式
【例】日期和时间函数使用
--getdate:当前系统日期、时间 select getDate() --dateadd:在向指定日期加上一段时间的基础上，返回新的datetime值 select dateadd(day,2,'2023-10-15') --返回：2023-10-17 00:00:00.000 --datediff:返回跨两个指定日期的日期和时间边界数 select datediff(day,'2023-09-01','2023-09-18') --返回：17 --datepart:返回代表指定日期的指定日期部分的整数，day、 month、year select datepart(month, '2023-10-15') --返回 10 --使用dateFromParts(year, month, day):返回指定日期 select dateFromParts(year(getDate()), month(getDate()), day(getDate())) AS today --datename:返回当前日期的中文名称 select datename(dw,getdate()) --返回 星期三 --使用cast(expression AS data_type):转换为Date日期类型 select cast(getDate() AS Date) AS today --使用convert(data_type,expression,style):转换为Date日期类型 select convert(DATE, getDate()) AS today --使用convert(value, format, culture):字符串格式化，返回日期格式 select format(getDate(), 'yyyy-MM-dd') AS today;
日期部分参数及缩写如下：
【例】日期部分参数及缩写使用
--1.yy,yyyy 年(year) select datePart(yy,getDate()) --返回 2023 --2.qq,q 季度(quarter) select datePart(qq,getDate()) --返回 4 --3.mm,m 月(month) select datePart(mm,getDate()) ----返回 10 --4.dy,y 一年中的某一天(dayofyear) select datePart(dy,getDate()) --返回 275 --5.dd,d 日(day) select datePart(dd,getDate()) --返回 2 --6.wk,ww 周(week) select datePart(wk,getDate()) --返回 40 --7.dw,w 星期(weekday) select datePart(dw,getDate()) --返回 2 --8.hh 小时(hour) select datePart(hh,getDate()) --返回 21 --9.mi,n 分钟(minute) select datePart(mi,getDate()) --返回 2 --10.ss,s 秒(second) select datePart(ss,getDate()) --返回 39 --11.ms 毫秒(millisecond) select datePart(ms,getDate()) --返回 900
时间及日期查询
--查询本月记录 SELECT * FROM 学生表 WHERE datediff(month,创建时间,getdate())=0 --查询上个月数据 SELECT * FROM 学生表 WHERE datediff(month,创建时间,getdate())=1 --查询本周数据 SELECT * FROM 学生表 WHERE datediff(week,创建时间,getdate())=0 --查询上周数据 SELECT * FROM 学生表 WHERE datediff(week,创建时间,getdate())=1 --查询当天数据 select * from 学生表 where DateDiff(dd,创建时间,getdate())=0 --查询昨天数据 select * from 学生表 where DateDiff(dd,创建时间,GETDATE())=1 --查询24小时内的: select * from 学生表 where DateDiff(hh,创建时间,getDate())<=24
时间分组查询：
--使用format按日期分组查询 select format(创建时间,'yyyy-MM-dd') as 日期 from 学生表 where 创建时间 between '2023/09/08 09:10:43' and '2023/09/28 04:32:37' group by format(创建时间,'yyyy-MM-dd') --使用convert按日期分组查询 select convert(varchar(7),创建时间,120)日期 from 学生表 where 创建时间 between '2023/09/08 09:10:43' and '2023/09/28 04:32:37' group by convert(varchar(7),创建时间,120) --使用datepart按日期分组查询 select datepart(dd,创建时间) from 学生表 where 创建时间 between '2022/09/25 09:10:43' and '2023/09/28 04:32:37' group by datepart(dd,创建时间) --使用year、month、day按日期分组查询 select year(创建时间),month(创建时间) ,day(创建时间) from 学生表 where 创建时间 between '2022/09/25 09:10:43' and '2023/09/28 04:32:37' group by year(创建时间),month(创建时间) ,day(创建时间)
3.常用数学函数
1.rand() --返回0到1之间的随机float值 2.abs() --获取绝对值 3.ceiling() --向上取整， 4.floor() --向下取整 5.power() --取数值表达式的幂 6.sign() --对于整数返回+1，对于负数返回-1，对于0则返回0 7.sqrt() --取浮点数表达式的平方根 8.round() --取数值表达式四舍五入为指定精度
【例】常用数学函数
--1.获取0~1之间的随机数 select rand(); --返回 0.402002817266769 --2.获取绝对值 select abs(-123); --返回 123 --3.向上取整 select ceiling(12.5); --返回 13 --4.向下取整 select floor(12.5); --返回 12 --5.取数值表达式的幂 select power(4,2); --返回 16 --6.返回符号或0 select sign(23); --返回 1 --7.返回平方根 select sqrt(9); --返回 3
【例】round(number, decimals, operation)函数，此函数用于将指定的数字四舍五入到指定的小数位。
select round(9/4,2) --结果：2 --建议写成 select round(9*1.0/4,2) --结果：2.250000
【例】保留N位小数
--数字保留N位小数，四舍五入，N位小数后面用0补
select round(1.67789,2,0) --1.68000
select cast(round(1.67789,2,0) as decimal(18,2)) --1.68
--数字保留N位小数，不四舍五入，N位小数后面用0补
select round(1.67789,2,1) --1.67000
select cast(round(1.67789,2,1) as decimal(18,2)) --1.67
--字符串保留N位小数，四舍五入
select round('1.67789',2,0) --1.68
--字符串保留N位小数，不四舍五入
select round('1.67789',2,1) --1.67
4.常用聚合函数
1.sum() --和 2.avg() --平均分 3.max() --最大值 4.min() --最小值 5.count() --计数
【例】常用聚合函数
--1.求和 select sum(列名); --返回 该列值的总和,该列必须是整数类型 --2.求平均分 select avg(列名); --返回 该列值的平均数,该列必须是整数类型 --3.求最大值 select max(列名); --返回 该列值中最大值,该列必须是整数类型 --4.求最小值 select min(列名); --返回 该列值中最小值,该列必须是整数类型 --5.统计 select count(列名); --返回 统计个数
5.其他函数
【例】ISNULL，给为NULL的数据设置默认值
ISNULL(a.字段名,0) 字段名
6.类型转换
1.cast(expression as data_type)
--目标对象转换为数字
select cast(123.4 as int) -- 123
select cast('123.4' as decimal) -- 123
select cast('123.4' as decimal(8, 2)) -- 123
2.convert(data_type,expression,style)
--目标对象转换为数字 select convert(int, 123.4) -- 123 select convert(decimal, '123.4') -- 123 select convert(decimal(8, 2), '123.4') -- 123.40
3.convert 转换为时间格式
--目标对象转换为时间 select convert(varchar(100), GETDATE(), 23) --2023-09-28 select convert(varchar(100), GETDATE(), 120) --2023-09-28 16:55:51
convert style 值如下：
二、SQL Server 自定义函数
CREATE FUNCTION --方法名称(--参数)
RETURNS 变量名 类型
AS
--T-SQL语句
BEGIN
END
创建函数
CREATE FUNCTION [dbo].[fn_name] RETURNS ... AS BEGIN ... END
将一个字符串转换成一个表变量
ALTER FUNCTION [dbo].[fn_String2Table] ( @String VARCHAR(8000), --要转换的字符串。 @Separator VARCHAR(10) = ',' --分隔符。 ) RETURNS @Table TABLE([ID] BIGINT) AS BEGIN SET @String = @String + @Separator WHILE CHARINDEX(@Separator, @String) > 0 BEGIN INSERT INTO @Table ([ID]) VALUES(SUBSTRING(@String, 1, CHARINDEX(@Separator, @String) - 1)) SELECT @String = SUBSTRING(@String, CHARINDEX(@Separator, @String) + 1, LEN(@String) - CHARINDEX(@Separator, @String)) END RETURN END
调用：
DECLARE @TempTable TABLE([ID] INT) INSERT INTO @TempTable ([ID]) SELECT [ID] FROM fn_ConvertStringToTable(@Ids, ',')
【例】字符串分离、去重、原样排序
CREATE FUNCTION [dbo].[fn_deldupstritem2str]
(
@srctext nvarchar(max),
@splitter varchar(10)
)
RETURNS varchar(max)
AS
BEGIN
declare @strSplitter2 varchar(200);
declare @srctext2 nvarchar(max);
declare @rtnText nvarchar(max);
SET @strSplitter2= '%'+@splitter+'%';
IF (@srctext is null or @srctext = '') or (@srctext NOT like @strSplitter2)
begin
set @rtnText = @srctext;
end
ELSE
begin
SET @srctext2 = REPLACE(@srctext,'/','+');
SET @rtnText=(
SELECT
STUFF(--STUFF函数来去掉结果字符串中的开头的逗号和空格
(
SELECT
@splitter + value
FROM String_Split(@srctext2, @splitter) d
group by value
ORDER BY CHARINDEX(@splitter+ value+ @splitter, @splitter + @srctext2 + @splitter )
FOR XML PATH('') --结果：+牛皮+仿皮+海绵+木内架+水性胶水+公仔棉
),
1,
1,
''
) AS t2
);
end
return @rtnText;
END
GO
调用：
select dbo.fn_deldupstritem2str('你，好，吗，好','，') --结果：你，好，吗
