SQL Server教程 - T-SQL-内置函数（Built-in Functions）

更新记录
转载请注明出处：
2022年8月1日 发布。
2022年7月2日 从笔记迁移到博客。

内置函数说明（FUNCTION）

Sever 提供了众多功能强大、方便易用的函数。使用这些函数，可以极大地提高数据库的管理。SQL Server中的函数从功能方面主要分为以下几类：字符串函数、数学函数、数据转换函数、文本和图像函数、日期和时间函数、系统函数等。

内置函数分类

聚合函数、数学函数、字符串函数、日期函数、转换函数、其他函数

聚合函数

说明

对一组值进行计算返回一个单一的值
除了COUNT函数，其他聚合函数都会忽略NULL
通常和GROUP BY 一起使用

常用聚合函数：

COUNT_BIG()与COUNT类似，但COUNT返回INT类型，COUNT_BIG返回BIGINT类型

其他聚合函数

SUM

说明：返回表达式中所有值的和或非重复值的和

SUM([ALL | DISTINCT] expression)

注意：
会忽略null值
ALL是默认参数，可以使用DISTINCT去除重复值
如果字段使用了索引，将会加快该函数执行
支持所有数值类型，会把smallint和tinyint当作int类型处理

AVG

说明：返回表达式中数值的平均值

AVG([ALL | DISTINCT] expression)

注意：
会忽略null值
ALL是默认选项，可以使用DISTINCT去除重复值
支持所有数值类型，会把smallint和tinyint当作int类型处理
返回值以参数类型不同而不同

MAX

说明：返回表达式中的最大值

MAX([ALL | DISTINCT] expression)

注意：
​ 会忽略null值
​ ALL是默认选项，可以使用DISTINCT去除重复值（对MAX无效）
​ MAX不可以用于bit、TEXT、IMAGE类型字段
​ 如果表达式所有值都是null，则返回null，否则会忽略null值

MIN

说明：返回表达式中的最小值

MIN([ALL | DISTINCT] expression)

注意：
​ 会忽略null值
​ ALL是默认选项，可以使用DISTINCT去除重复值（对MIN无效）
​ MIN不可以用于bit类型字段
​ 如果表达式所有值都是null，则返回null，否则会忽略null值

COUNT

说明：返回表达式的项数

COUNT([ALL | DISTINCT] expression)

注意：
​ 会忽略null值
​ ALL是默认选项，可以使用DISTINCT去除重复值
​ COUNT返回INT数据类型的值
​ COUNT不可以用于在TEXT、IMAGE、NTEXT类型的列上
​ COUNT(*)不会忽略null值

DISTINCT

说明：删除表达式中的重复值

DISTINCT()

STDEV

所有值的标准偏差

STDEVP

所有值的总体标准偏差

VAR

所有值的方差

VARP

所有值的总体方差

数学函数

说明

数学函数主要用来处理数值数据，主要的数学函数有：绝对值函数、三角函数（包括正弦函数、余弦函数、正切函数、余切函数等）、对数函数、随机数函数等。在产生错误时，数学函数将会返回空值NULL。

ABS

说明：返回数值表达式的绝对值

ABS()

注意：
返回值的类型就是参数的类型
如果参数为空，则ABS返回结果为空

PI

说明：圆周率函数

PI()

POWER

说明：返回幂运算

POWER(number,power)

参数：power表示乘方次数

RAND

说明：返回0-1的FLOAT类型值随机数

RAND([seed])

实例：

SELECT CAST(RAND() * 100 AS INT) + 1 AS "1 to 100",
  CAST(RAND()* 1000 AS INT) + 900 AS "900 to 1900",
  CAST(RAND() * 5 AS INT)+ 1 AS "1 to 5";

ROUND

说明：数值指定精度四舍五入

ROUND(number, length, [function])

参数：length表示小数个数精度
返回类型：同输入参数number的类型

SQUARE

说明：返回数值的平方

SQUARE(number)

SQRT

说明：返回数值的平方根

SQRT(number)

SIGN

说明：返回参数的符号，的值为负、零或正时，返回结果依次为－1、0或1.
参数：
注意：

SIGN(x)

CEILING

说明：
参数：
注意：

FLOOR

说明：
参数：
注意：

SIN

说明：三角正弦

SIN(float)

COS

说明：三角函数

COS(float)

COT

说明：三角余切

COT(float)

TAN

说明：正切值

TAN(float)

字符串函数

说明

字符串函数用于对字符和二进制字符串进行各种操作，它们返回对字符数据进行操作时通常所需要的值。大多数字符串函数只能用于char、nchar、varchar和nvarchar数据类型，或隐式转换为上述数据类型。某些字符串函数还可用于 binary 和 varbinary 数据类型。字符串函数可以用在SELECT 或者WHERE语句中。

ASCII

说明：

ASCII(char)

返回值：返回的数据类型为INT类型

CHAR

说明：
参数：
返回值：
注意：

CHAR(115)

NCHAR

说明：
参数：
返回值：
注意：

UNICODE

说明：
参数：
返回值：
注意：

CHARINDEX

说明：返回指定表达式的起始位置

CHARINDEX(expression1,expression2,[start_location])

参数：expression1表示要被查找的字符串
参数：expression2表示要查找的字符串
参数：start_location表示搜索的起始位置，未指定或参数为负数则为0
返回值：int或bigint
注意：没有查找到返回整数0

LEFT

说明：从左边取字符串N个字符

LEFT(expression,int)

返回值的类型依参数的类型而不同

RIGHT

说明：取右边字符串N个字符

RIGHT(expression, int)

返回值的类型依参数的类型而不同s

LOWER

说明：字符串转为小写

LOWER(expression)

参数：
返回值：
注意：

UPPER

说明：字符串转为大写

UPPER(expression)

参数：
返回值：
注意：

LEN

说明：返回字符串表达式中的字符数

LEN(expression)

注意：不包含尾随空格
注意：返回的字符数，不是字节数

DATALENGTH

说明：返回字符串表达式中的字节数

DATALENGH(expression)

注意：不包含尾随空格
注意：返回的字节数

TRIM

SQL Server 2017起可用

TRIM(<string>)

LTRIM

说明：删除左边起始空格

LTRIM(expression)

参数：字符串
返回值：删除空格后的字符串
注意：

RTRIM

说明：删除右边起始空格

RTRIM(expression)

参数：字符串
返回值：删除空格后的字符串
注意：

REPLICATE

说明：指定字符串重复N次

REPLICATE(string_expression, interger)

SPACE

说明：返回指定个数的空格

SPACE(integer)

参数：
返回值：
注意：

REPLACE

说明：用一个字符串替换指定字符串

REPLACE(expression, search_pattern, replacement_string)

REVERSE

说明：反转字符串

REVERSE(expression)

SUBSTRING

说明：截取指定长度的字符串

SUBSTRING(expression, start_location, length)

返回值：根据参数的不同，返回值的类型不同

STRING_SPLIT

说明：将字符串分割为列

STRING_SPLIT(str_val)

实例：

SELECT value
FROM STRING_SPLIT('1,2,3,4,5,6,7,8,9,10',',');

结果：

实例：

SELECT value
FROM STRING_SPLIT('dog cat fish bird lizard',' ');

结果：

STRING_AGG

说明：将选中的多个列合并到一个行中

STRING_AGG(ColumnName, ConcatString)

实例：

SELECT STRING_AGG(Name, ', ') AS List
FROM Production.ProductCategory;

STUFF

说明：删除指定长度的字符串并且在指定位置插入字符串

STUFF(被处理字符串,截取开始位置,截取长度,插入的字符串)

返回值：字符串类型

PATINDEX

说明：获得字符串在另一个字符串中的起始位置

PATINDEX(查找字符串,被查找字符串)

参数：
返回值：
注意：PATINDEX支持使用通配符查找，比如：PATINDEX(‘%BC%’,’ABCD’);

STR

说明：数值转为字符串

STR(float_expression,[length],[decimal])

参数：float_expression表示要被转换的浮点数
参数：length，表示转换后的长度，包括小数点、符号以及空格，默认为10
参数：decimal表示小数点后的位数，必须小于等于16

COALESCE

说明：返回第一个不是NULL的值

COALESCE(<value1>,<value2>,...,<valueN>)

CHOOSE

select a value in an array based on an index

CHOOSE ( index, val_1, val_2 [, val_n ] )

注意：基于1开始

实例：结果’d’

SELECT CHOOSE (4, 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i');

日期和时间

说明

日期和时间函数主要用于处理日期和时间值，本节将介绍各种日期和时间函数的功能和用法。一般的日期函数除了使用 date类型的参数外，也可以使用 datetime 类型的参数，但会忽略这些值的时间部分。相同的，以time类型值为参数的函数，可以接受 datetime 类型值的参数，但会忽略日期部分。

GETDATE

说明：返回系统当前日期时间

GETDATE()

GETUTCDATE

说明：返回当前国际标准日期时间

GETUTCDATE()

SYSDATETIME

说明：返回系统当前日期时间

SYSDATETIME()

返回：datetime2(7)类型

实例：

SELECT SYSDATETIME()

YEAR

说明：获得日期字符串中’年’部分

YEAR(date)

MONTH

说明：获得日期字符串中’月’部分

MONTH(date)

DAY

说明：获得日期字符串中’日’部分

DAY(date)

DATEDIFF

说明：获得日期时间的时间差

DATEDIFF(datepart,start_date,end_date)

参数：datepart表示结果的单位，可取值如下：

实例：

SELECT DATEDIFF(year,'2008-12-31','2009-01-01') AS YearDiff

Select DATEDIFF(d,'2008-12-31','2009-01-01') AS DayDiff;

DATEADD

说明：添加日期时间

DATEADD(datepart,number,date)

参数：datepart表示要与数值相加的日期部分参数，可取值如下：

参数：number表示与datepart相加的值（包含小数将被忽略）
参数：date表示参与运算的日期时间

实例：

SELECT DATEADD(month,1,'2009-01-29') AS FebDate;

DAYNAME

说明：获得日期时间字符串中指定的部分的字符串值
参数：
返回值：字符串类型
注意：datepart可取值部分如下表

DATENAME(datepart,date)

DATEPART

说明：获得日期时间字符串中指定的部分的数值值

DATEPART(datepart,date)

返回值：数值类型

CURRENT_TIMESTAMP

当前日期时间

FORMAT

FORMAT(value, format [, culture ])

Format支持

实例：

SELECT FORMAT( GETDATE(), 'dd', 'en-US' ) AS Result;

SELECT FORMAT( GETDATE(), 'MM/dd/yyyy', 'en-US' ) AS Result;

DATEFROMPARTS

将数值拼接为字符串

实例：

SELECT DATEFROMPARTS(2012, 3, 10) AS RESULT;

SELECT TIMEFROMPARTS(12, 10, 32, 0, 0) AS RESULT;

SELECT DATETIME2FROMPARTS (2012, 3, 10, 12, 10, 32, 0, 0) AS RESULT;

EOMONTH

返回一个月最后的一天日期

实例：

SELECT EOMONTH(GETDATE()) AS [End of this month],
  EOMONTH(GETDATE(),1) AS [End of next month],
  EOMONTH('2020-01-01') AS [Another month];

获得：

转换函数

说明

在同时处理不同数据类型的值时，SQL Server一般会自动进行隐式类型转换，这种隐式类型。转换对于数据类型相近的数值是有效的，比如int和float,但是对于其他数据类型，例如整数类型和字符数据类型，这种隐式转换就无法实现了，此时必须使用显式转换。为了实现这种转换，Transact-SQL提供了两个显式转换的函数，分别是CAST()函数和CONVERT()函数。

转换分为两种：隐式转换 和 显式转换，显式转换需要使用CAST函数和CONVERT函数。

CAST(expression AS data_type)

说明：用于将某种数据类型显式转为另一种数据类型

CAST(expression AS data_type)

实例：

SELECT CAST('1314.1314' AS FLOAT(2));

CONVERT(data_type[(Length)],expression,[style]);

说明：用于将某种数据类型显式转为另一种数据类型

CONVERT(data_type[(Length)],expression,[style]);

Style用于定义转换后的样式，不同的数据类型不同：

实例：

SELECT CONVERT(VARCHAR,GETDATE(),111)

排序函数

ROW_NUMBER()

说明：为每条结果条件一个递增的顺序数值列

ROW_NUMBER() OVER (ORDER BY 列名 ASC|DESC)

实例：

SELECT ROW_NUMBER() OVER (ORDER BY name) AS 'ROW',id,name
FROM panda_table;

结果：

RANK()

说明：和ROW_NUMBER()类似，但是如果排序值相同的情况下，生成的值是相同的。如果两个或多个行与一个排名关联，则每个关联行将得到相同的排名。例如，如果两名学生
具有相同的s_score值，则他们将并列第一。由于已有两行排名在前，因此具有下一个最高

实例：使用RANK(函数对根据s_id字段查询的结果进行分组排序

SELECT RANK() OVER (ORDER BY s id ASC) AS RankID,s id,f name
FROM fruits;

系统函数

COL_LENGTH

说明：返回列的定义长度

COL_LENGTH('table_name','column_name')

COL_NAME

说明：返回列名

COL_NAME(table_id,column_id)

DATALENGTH

说明：获得表达式的实际长度

DATALENGTH(expression);

实例：

DECLARE @panda INT;
SELECT @panda = 123;
SELECT DATALENGTH(@panda); //4

DB_ID()

说明：获得数据库的编号

DB_ID([dtabase_name])

注意：如果不提供参数，将返回当前数据库的编号
实例：

SELECT DB_ID();

DB_NAME()

说明：获得当前数据库的名称

SELECT DB_NAME() AS "Database Name"

HOST_ID()

说明：获得当前服务器端的计算机标识号

HOST_ID();

实例：

SELECT HOST_ID(), HOST_NAME();

HOST_NAME()

说明：获得当前服务器的计算机名

HOST_NAME();

实例：

SELECT HOST_ID(), HOST_NAME();

DB_NAME()

说明：获得数据库名
注意：如果不提供参数，将返回当前数据库名

DB_NAME([database_id])

NEWID()

说明：获得一个GUID

SELECT NEWID();

ISNULL()

说明：如果第一个参数为NULL，则返回第二个参数

SELECT ISNULL(NULL,'Panda');

USER_ID()

说明：获得指定用户名的用户ID

USER_ID(user_name)

USER_NAME()

说明：获得用户名

USER_NAME([user_id])

实例：

SELECT USER_NAME() AS "User Name",

CURRENT_USER

获得当前用户

SELECT CURRENT_USER AS "Current User"

SUSER_NAME()

SUSER_NAME()

实例：

SELECT SUSER_NAME()

SUSER_SID()

说明：获得当前用户的安全标识号（SID，Security Identification Number）
返回值：INT类型

SUSER_SID(user_name)

实例：

SELECT SUSER_SID('sa');

SUSER_SNAME()

说明：获得当前用户的用户名
返回值：字符串类型

SUSER_SNAME([user_id]);

实例：

SELECT SUSER_SNAME(SUSER_SID('sa'));

APP_NAME()

获得当前连接数据库的应用名称
实例：

SELECT APP_NAME() AS "App Name";

OBJECT_ID()

说明：获得数据库中的对象Id

OBJECT_ID('object_name')

OBJECT_NAME()

说明：获得数据库中对象的名称

OBJECT_NAME(object_id)

实例：

NEWID()

说明：创建新GUID
参数：
返回值：
注意：

SELECT NEWID();

GETANSINULL()

返回当前数据库默认的NULL值。GETANSINULLO函数对ANSI空值NULL返回1;如果没有定义ANSI空值，则返回0。

GETANSINULL(database_name)

实例：

SELECT GETANSINULL('test db')