Ø 简介
本文主要列举 SQL Server 中常用的一些系统函数,帮助我们在编写 SQL 时忘了某个函数的用法方便查阅。主要分为以下几类函数,更多函数可参考官网。
1. 字符串函数
2. 日期函数
3. 数学函数
4. 其他函数
1. 字符串函数
Ø 提示:SQL Server 中的字符串索引始终从1开始。
1) CHARINDEX()
语法:CHARINDEX(expressionToFind, expressionToSearch[, start_location])
描述:返回第一个表达式在第二个表达式中第一次出现的起始位置,不存在返回零。示例:SELECTCHARINDEX('a', 'abc123xyz') AS Column1, CHARINDEX('123', 'abc123xyz') AS Column2, CHARINDEX('123', 'abc123xyz', 5) AS Column3, CHARINDEX('de', 'abc12xyz') AS Column4, CHARINDEX('', 'abc12xyz') AS Column5, CHARINDEX(NULL, 'abc12xyz') AS Column6, CHARINDEX('de', NULL) AS Column7;
结果:
2) PATINDEX()
语法:PATINDEX('%pattern%', expression)
描述:返回模式在指定表达式中第一次出现的起始位置;如果在所有有效的文本和字符数据类型中都找不到该模式,则返回零。
示例:SELECTPATINDEX('%1___2%', 'abc1def2abcxyz') AS Column1, PATINDEX('%1%2%', 'abc1def2abcxyz') AS Column2;
结果:
3) LEN()
语法:LEN(string_expression)
描述:返回指定字符串表达式的字符数,其中不包含尾随空格。
示例:SELECTLEN(' abc') AS Column1, LEN('abc ') AS Column2;
结果:
4) LEFT()
语法:LEFT(character_expression, integer_expression)
描述:返回字符串中从左边开始指定个数的字符。
示例:SELECTLEFT('abcxyz', 3) AS Column1, LEFT(25.2523, 5) AS Column2;
结果:
5) RIGHT()
语法:RIGHT(character_expression, integer_expression)
描述:返回字符串中从右边开始指定个数的字符。
示例:SELECTRIGHT('abcxyz', 3) AS Column1, RIGHT(25.2523, 5) AS Column2;
结果:
6) LTRIM()
语法:LTRIM(character_expression)
描述:返回删除了前导空格之后的字符表达式。
示例:SELECTLTRIM(' 左边没有空格')AS Column1;
结果:
7) RTRIM()
语法:RTRIM(character_expression)
描述:截断所有尾随空格后返回一个字符串。
示例:SELECTRTRIM('右边没有空格 ')AS Column1;
结果:
8) SUBSTRING()
语法:SUBSTRING(expression, start, length)
描述:返回 SQL Server 中的字符、二进制、文本或图像表达式的一部分。
示例:SELECTSUBSTRING('截取子字符串',, 1) AS Column1, SUBSTRING('截取子字符串',, 4) AS Column1, SUBSTRING('截取子字符串',, 3) AS Column1;
结果:
9) UPPER()
语法:UPPER(character_expression)
描述:返回小写字符数据转换为大写的字符表达式。
示例:SELECTUPPER('aBc123') AS Column1;
结果:
10) LOWER()
语法:LOWER(character_expression)
描述:将大写字符数据转换为小写字符数据后返回字符表达式。
示例:SELECTLOWER('AbC123') AS Column1;
结果:
11) REPLACE()
语法:REPLACE(string_expression, string_pattern, string_replacement)
描述:用另一个字符串值替换出现的所有指定字符串值。
示例:SELECTREPLACE('我是被替换的字符串','被替换的','') AS Column1;
结果:
2. 日期函数
1) GETDATE()
语法:GETDATE()
描述:返回当前数据库系统时间戳,返回值的类型为 datetime,并且不含数据库时区偏移量。 此值得自运行 SQL Server 实例的计算机的操作系统。
示例:SELECTGETDATE() AS Column1;
结果:
2) GETUTCDATE()
语法:GETUTCDATE()
描述:返回当前数据库系统时间戳作为 datetime 值。 数据库时区偏移量未包含在内。 此值表示当前的 UTC 时间(通用协调时间)。 此值得自运行 SQL Server 实例的计算机的操作系统。
示例:SELECTGETUTCDATE() AS Column1;
结果:
3) DATEADD()
语法:DATEADD(datepart, number, date)
描述:返回指定的 date,同时有指定数目的间隔(带符号整数)被添加到该 date 的指定 datepart(日期部分)。
示例:SELECTGETDATE() AS Column0, DATEADD(YEAR, 1, GETDATE()) AS Column1, DATEADD(MONTH, 1, GETDATE()) AS Column2, DATEADD(DAY, 1, GETDATE()) AS Column3, DATEADD(HOUR, 1, GETDATE()) AS Column4, DATEADD(MINUTE, 1, GETDATE()) AS Column5, DATEADD(SECOND, 1, GETDATE()) AS Column6, DATEADD(MILLISECOND, 500, GETDATE()) AS Column7, DATEADD(DAY, -1, GETDATE()) AS Column8;
结果:
4) DATEDIFF()
语法:DATEDIFF(datepart, startdate, enddate)
描述:返回指定的 startdate 和 enddate 之间所跨的指定 datepart 边界的计数(带符号整数)。
示例:SELECTDATEDIFF(DAY, '2018-05-15', '2018-05-16') AS Column1, DATEDIFF(DAY, '2018-05-15', '2018-05-15') AS Column2, DATEDIFF(DAY, '2018-05-16', '2018-05-15') AS Column3;
结果:
5) DATENAME()
语法:DATENAME(datepart, date)
描述:返回表示指定 date 的指定 datepart 的字符串。
示例:SELECTGETDATE() AS CurrentDate, DATENAME(YEAR, GETDATE()) AS年,DATENAME(QUARTER, GETDATE()) AS季度,DATENAME(MONTH, GETDATE()) AS月,DATENAME(DAY, GETDATE()) AS日,DATENAME(HOUR, GETDATE()) AS时,DATENAME(MINUTE, GETDATE()) AS分,DATENAME(SECOND, GETDATE()) AS秒,DATENAME(MILLISECOND, GETDATE()) AS毫秒,DATENAME(WEEK, GETDATE()) AS周,DATENAME(WEEKDAY, GETDATE()) AS星期;
结果:
6) DATEPART()
语法:DATEPART(datepart, date)
描述:返回表示指定 date 的指定 datepart 的整数。
示例:SELECTGETDATE() AS Column0, DATEPART(YEAR, GETDATE()) AS Column1, DATEPART(MONTH, GETDATE()) AS Column2, DATEPART(DAY, '2018-06-06') AS Column3;
结果:
7) YEAR()
语法:YEAR(date)
描述:返回表示指定 date 的年份的整数。
示例:SELECTYEAR(GETDATE()) AS Column1, YEAR('2018-06-07') AS Column2;
结果:
8) MONTH()
语法:MONTH(date)
描述:返回表示指定日期的月份的整数。
示例:SELECTMONTH(GETDATE()) AS Column1, MONTH('2018-06-07') AS Column2;
结果:
9) DAY()
语法:DAY(date)
描述:返回一个整数,该整数表示指定 date 的当天(某月的某一天)。
示例:SELECTDAY(GETDATE()) AS Column1, DAY('2018-06-07') AS Column2;
结果:
3. 数学函数
1) ABS()
语法:ABS(numeric_expression)
描述:返回指定数值表达式的绝对值(正值)的数学函数。 (ABS 将负值更改为正值。 ABS 对零或正值没有影响。)
示例:SELECTABS(-1) AS Column1, ABS(0.0) AS Column2, ABS(1) AS Column3;
结果:
2) RAND()
语法:RAND([seed])
描述:返回一个介于 0 到 1(不包括 0 和 1)之间的伪随机 float 值。注意:相同的种子,随机数始终一致。
示例:ELECTRAND() AS Column1, RAND() AS Column2, RAND(100) AS Column3, RAND(100) AS Column4;
结果:
3) ROUND()
语法:ROUND(numeric_expression, length[, function])
描述:返回一个数值,舍入到指定的长度或精度。
示例:SELECTROUND(12, 2) AS Column1, CAST(ROUND(12, 2) AS numeric(19,2)) AS Column2, ROUND(12.2346, 2) AS Column3, ROUND(12.2356, 2) AS Column4, ROUND(12.2354, 2) AS Column5, ROUND(748.58, -2) AS Column6, ROUND(150.75, 0) AS Column7, ROUND(150.75, 0, 1) AS Column8;
结果:
4) CEILING()
语法:CEILING(numeric_expression)
描述:此函数返回大于或等于指定数值表达式的最小整数。
示例:SELECTCEILING(1.48) AS Column1, CEILING(14.32) AS Column2, CEILING(44.98) AS Column3;
结果:
5) FLOOR()
语法:FLOOR(numeric_expression)
描述:返回小于或等于指定数值表达式的最大整数。
示例:SELECTFLOOR(1.48) AS Column1, FLOOR(14.32) AS Column2, FLOOR(44.98) AS Column3;
结果:
4. 其他函数
1) CAST()
语法:CAST(expression AS data_type[(length)])
描述:这些函数将表达式由一种数据类型转换为另一种数据类型。
示例:SELECTAS Original, CAST(9.5 AS int) AS int, CAST(9.5 AS decimal(6,4)) AS decimal;
结果:
2) CONVERT()
语法:CONVERT(data_type[(length)], expression[, style])
描述:这些函数将表达式由一种数据类型转换为另一种数据类型。
示例:SELECTAS Original, CONVERT(int, 9.5) AS int, CONVERT(decimal(6,4), 9.5) AS decimal;
结果:
Sytle 参数示例:SELECTGETDATE() AS Original, CONVERT(varchar(128), GETDATE(), 8) AS [style is 8], CONVERT(varchar(128), GETDATE(), 14) AS [style is 14], CONVERT(varchar(128), GETDATE(), 11) AS [style is 11], CONVERT(varchar(128), GETDATE(), 111) AS [style is 111], CONVERT(varchar(128), GETDATE(), 23) AS [style is 23], CONVERT(varchar(128), GETDATE(), 12) AS [style is 12], CONVERT(varchar(128), GETDATE(), 112) AS [style is 112], CONVERT(varchar(128), GETDATE(), 102) AS [style is 102], CONVERT(varchar(128), GETDATE(), 20) AS [style is 20], CONVERT(varchar(128), GETDATE(), 21) AS [style is 21];