T-SQL Functions Reference Skill

T-SQL Functions Reference

Complete reference for all T-SQL function categories with version-specific availability.

Quick Reference

String Functions

| Function | Description | Version | |----------|-------------|---------| | CONCAT(str1, str2, ...) | NULL-safe concatenation | 2012+ | | CONCAT_WS(sep, str1, ...) | Concatenate with separator | 2017+ | | STRING_AGG(expr, sep) | Aggregate strings | 2017+ | | STRING_SPLIT(str, sep) | Split to rows | 2016+ | | STRING_SPLIT(str, sep, 1) | With ordinal column | 2022+ | | TRIM([chars FROM] str) | Remove leading/trailing | 2017+ | | TRANSLATE(str, from, to) | Character replacement | 2017+ | | FORMAT(value, format) | .NET format strings | 2012+ |

Date/Time Functions

| Function | Description | Version | |----------|-------------|---------| | DATEADD(part, n, date) | Add interval | All | | DATEDIFF(part, start, end) | Difference (int) | All | | DATEDIFF_BIG(part, s, e) | Difference (bigint) | 2016+ | | EOMONTH(date, [offset]) | Last day of month | 2012+ | | DATETRUNC(part, date) | Truncate to precision | 2022+ | | DATE_BUCKET(part, n, date) | Group into buckets | 2022+ | | AT TIME ZONE 'tz' | Timezone conversion | 2016+ |

Window Functions

| Function | Description | Version | |----------|-------------|---------| | ROW_NUMBER() | Sequential unique numbers | 2005+ | | RANK() | Rank with gaps for ties | 2005+ | | DENSE_RANK() | Rank without gaps | 2005+ | | NTILE(n) | Distribute into n groups | 2005+ | | LAG(col, n, default) | Previous row value | 2012+ | | LEAD(col, n, default) | Next row value | 2012+ | | FIRST_VALUE(col) | First in window | 2012+ | | LAST_VALUE(col) | Last in window | 2012+ | | IGNORE NULLS | Skip NULLs in offset funcs | 2022+ |

SQL Server 2022 New Functions

| Function | Description | |----------|-------------| | 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 |

Core Patterns

String Manipulation

-- 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

Date Operations

-- 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
)

Window Functions

-- 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

JSON Operations

-- 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

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

Agent Skills: T-SQL Functions Reference

Install this agent skill to your local

Skill Files