Back to Details

tsql-functions

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

T-SQL Functions Reference

T-SQL函数参考

Complete reference for all T-SQL function categories with version-specific availability.
包含所有T-SQL函数分类及对应版本可用性的完整参考手册。

Quick Reference

快速参考

String Functions

字符串函数

FunctionDescriptionVersion
CONCAT(str1, str2, ...)
NULL-safe concatenation2012+
CONCAT_WS(sep, str1, ...)
Concatenate with separator2017+
STRING_AGG(expr, sep)
Aggregate strings2017+
STRING_SPLIT(str, sep)
Split to rows2016+
STRING_SPLIT(str, sep, 1)
With ordinal column2022+
TRIM([chars FROM] str)
Remove leading/trailing2017+
TRANSLATE(str, from, to)
Character replacement2017+
FORMAT(value, format)
.NET format strings2012+
函数说明版本
CONCAT(str1, str2, ...)
支持NULL安全的字符串拼接2012+
CONCAT_WS(sep, str1, ...)
使用分隔符拼接字符串2017+
STRING_AGG(expr, sep)
字符串聚合2017+
STRING_SPLIT(str, sep)
将字符串拆分为行2016+
STRING_SPLIT(str, sep, 1)
拆分后包含序号列2022+
TRIM([chars FROM] str)
移除首尾字符2017+
TRANSLATE(str, from, to)
字符替换2017+
FORMAT(value, format)
.NET格式字符串支持2012+

Date/Time Functions

日期/时间函数

FunctionDescriptionVersion
DATEADD(part, n, date)
Add intervalAll
DATEDIFF(part, start, end)
Difference (int)All
DATEDIFF_BIG(part, s, e)
Difference (bigint)2016+
EOMONTH(date, [offset])
Last day of month2012+
DATETRUNC(part, date)
Truncate to precision2022+
DATE_BUCKET(part, n, date)
Group into buckets2022+
AT TIME ZONE 'tz'
Timezone conversion2016+
函数说明版本
DATEADD(part, n, date)
添加时间间隔所有版本
DATEDIFF(part, start, end)
计算时间差（整数）所有版本
DATEDIFF_BIG(part, s, e)
计算时间差（大整数）2016+
EOMONTH(date, [offset])
获取当月最后一天2012+
DATETRUNC(part, date)
截断日期至指定精度2022+
DATE_BUCKET(part, n, date)
按时间桶分组2022+
AT TIME ZONE 'tz'
时区转换2016+

Window Functions

窗口函数

FunctionDescriptionVersion
ROW_NUMBER()
Sequential unique numbers2005+
RANK()
Rank with gaps for ties2005+
DENSE_RANK()
Rank without gaps2005+
NTILE(n)
Distribute into n groups2005+
LAG(col, n, default)
Previous row value2012+
LEAD(col, n, default)
Next row value2012+
FIRST_VALUE(col)
First in window2012+
LAST_VALUE(col)
Last in window2012+
IGNORE NULLS
Skip NULLs in offset funcs2022+
函数说明版本
ROW_NUMBER()
生成连续唯一编号2005+
RANK()
带间隔的排名（相同值排名相同，后续排名跳过）2005+
DENSE_RANK()
无间隔的排名（相同值排名相同，后续排名连续）2005+
NTILE(n)
将数据分配至n个分组2005+
LAG(col, n, default)
获取前n行的值2012+
LEAD(col, n, default)
获取后n行的值2012+
FIRST_VALUE(col)
获取窗口内的第一个值2012+
LAST_VALUE(col)
获取窗口内的最后一个值2012+
IGNORE NULLS
在偏移函数中跳过NULL值2022+

SQL Server 2022 New Functions

SQL Server 2022新增函数

FunctionDescription
GREATEST(v1, v2, ...)
Maximum of values
LEAST(v1, v2, ...)
Minimum of values
DATETRUNC(part, date)
Truncate date
GENERATE_SERIES(start, stop, [step])
Number sequence
JSON_OBJECT('key': val)
Create JSON object
JSON_ARRAY(v1, v2, ...)
Create JSON array
JSON_PATH_EXISTS(json, path)
Check path exists
IS [NOT] DISTINCT FROM
NULL-safe comparison
函数说明
GREATEST(v1, v2, ...)
获取多个值中的最大值
LEAST(v1, v2, ...)
获取多个值中的最小值
DATETRUNC(part, date)
截断日期
GENERATE_SERIES(start, stop, [step])
生成数字序列
JSON_OBJECT('key': val)
创建JSON对象
JSON_ARRAY(v1, v2, ...)
创建JSON数组
JSON_PATH_EXISTS(json, path)
检查JSON路径是否存在
IS [NOT] DISTINCT FROM
支持NULL安全的比较

Core Patterns

核心使用模式

String Manipulation

字符串操作

sql
-- Concatenate with separator (NULL-safe)
SELECT CONCAT_WS(', ', FirstName, MiddleName, LastName) AS FullName

-- Split string to rows with ordinal
SELECT value, ordinal
FROM STRING_SPLIT('apple,banana,cherry', ',', 1)

-- Aggregate strings with ordering
SELECT DeptID,
       STRING_AGG(EmployeeName, ', ') WITHIN GROUP (ORDER BY HireDate)
FROM Employees
GROUP BY DeptID
sql
-- 使用分隔符拼接字符串（支持NULL安全）
SELECT CONCAT_WS(', ', FirstName, MiddleName, LastName) AS FullName

-- 将字符串拆分为行并包含序号
SELECT value, ordinal
FROM STRING_SPLIT('apple,banana,cherry', ',', 1)

-- 带排序的字符串聚合
SELECT DeptID,
       STRING_AGG(EmployeeName, ', ') WITHIN GROUP (ORDER BY HireDate)
FROM Employees
GROUP BY DeptID

Date Operations

日期操作

sql
-- Truncate to first of month
SELECT DATETRUNC(month, OrderDate) AS MonthStart

-- Group by week buckets
SELECT DATE_BUCKET(week, 1, OrderDate) AS WeekBucket,
       COUNT(*) AS OrderCount
FROM Orders
GROUP BY DATE_BUCKET(week, 1, OrderDate)

-- Generate date series
SELECT CAST(value AS date) AS Date
FROM GENERATE_SERIES(
    CAST('2024-01-01' AS date),
    CAST('2024-12-31' AS date),
    1
)
sql
-- 截断日期至当月第一天
SELECT DATETRUNC(month, OrderDate) AS MonthStart

-- 按周桶分组统计
SELECT DATE_BUCKET(week, 1, OrderDate) AS WeekBucket,
       COUNT(*) AS OrderCount
FROM Orders
GROUP BY DATE_BUCKET(week, 1, OrderDate)

-- 生成日期序列
SELECT CAST(value AS date) AS Date
FROM GENERATE_SERIES(
    CAST('2024-01-01' AS date),
    CAST('2024-12-31' AS date),
    1
)

Window Functions

窗口函数

sql
-- Running total with partitioning
SELECT OrderID, CustomerID, Amount,
       SUM(Amount) OVER (
           PARTITION BY CustomerID
           ORDER BY OrderDate
           ROWS UNBOUNDED PRECEDING
       ) AS RunningTotal
FROM Orders

-- Get previous non-NULL value (SQL 2022+)
SELECT Date, Value,
       LAST_VALUE(Value) IGNORE NULLS OVER (
           ORDER BY Date
           ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
       ) AS PreviousNonNull
FROM Measurements
sql
-- 按客户分组的累计求和
SELECT OrderID, CustomerID, Amount,
       SUM(Amount) OVER (
           PARTITION BY CustomerID
           ORDER BY OrderDate
           ROWS UNBOUNDED PRECEDING
       ) AS RunningTotal
FROM Orders

-- 获取前一个非NULL值（SQL 2022+）
SELECT Date, Value,
       LAST_VALUE(Value) IGNORE NULLS OVER (
           ORDER BY Date
           ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
       ) AS PreviousNonNull
FROM Measurements

JSON Operations

JSON操作

sql
-- Extract scalar value
SELECT JSON_VALUE(JsonColumn, '$.customer.name') AS CustomerName

-- Parse JSON array to rows
SELECT j.ProductID, j.Quantity
FROM Orders
CROSS APPLY OPENJSON(OrderDetails)
WITH (
    ProductID INT '$.productId',
    Quantity INT '$.qty'
) AS j

-- Build JSON object (SQL 2022+)
SELECT JSON_OBJECT('id': CustomerID, 'name': CustomerName) AS CustomerJson
FROM Customers
sql
-- 提取标量值
SELECT JSON_VALUE(JsonColumn, '$.customer.name') AS CustomerName

-- 解析JSON数组为行
SELECT j.ProductID, j.Quantity
FROM Orders
CROSS APPLY OPENJSON(OrderDetails)
WITH (
    ProductID INT '$.productId',
    Quantity INT '$.qty'
) AS j

-- 构建JSON对象（SQL 2022+）
SELECT JSON_OBJECT('id': CustomerID, 'name': CustomerName) AS CustomerJson
FROM Customers

Additional References

更多参考资料

For deeper coverage of specific function categories, see:
  • references/string-functions.md
    - Complete string function reference with examples
  • references/window-functions.md
    - Window and ranking functions with frame specifications
如需深入了解特定函数分类，请查看：
  • references/string-functions.md
    - 包含示例的完整字符串函数参考
  • references/window-functions.md
    - 包含框架规范的窗口和排名函数参考