SQL Server Functions Reference

Microsoft SQL Server (T-SQL) provides a comprehensive set of built-in functions designed for manipulating strings, working with dates and times, handling numeric data, and managing system or logical operations.

Below is an operational reference guide categorized by functional area, including T-SQL-specific behaviors and ANSI SQL alternatives.

1. String Functions

Function	Description & T-SQL Example	Notes & Key Behavior
`LEN()`	Returns the number of characters in a string, excluding trailing blanks. `SELECT LEN('SQL Server ');` — Output: `10`	Ignores trailing spaces. Use `DATALENGTH()` to measure exact byte size.
`DATALENGTH()`	Returns the number of bytes used to represent an expression. `SELECT DATALENGTH(N'SQL');` — Output: `6` (Unicode `NVARCHAR`)	Useful for checking storage size of `NVARCHAR` or `VARBINARY` columns.
`CHARINDEX()`	Searches for a substring and returns its starting character position. `SELECT CHARINDEX('Server', 'SQL Server');` — Output: `5`	Uses a 1-based index position. Returns `0` if the substring is not found.
`SUBSTRING()`	Extracts a portion of a string starting at a designated position for a given length. `SELECT SUBSTRING('SQL Server', 5, 6);` — Output: `'Server'`	Takes three arguments: `(expression, start_position, length)`.
`CONCAT()`	Concatenates two or more string values. `SELECT CONCAT('SQL', ' ', 'Server', ' ', 2022);`	Implicitly converts non-string types to strings and treats `NULL` inputs as empty strings.
`CONCAT_WS()`	Concatenates strings with a specified separator. `SELECT CONCAT_WS('-', '2026', '09', '27');`	Skips `NULL` arguments without inserting extra separators.
`STRING_AGG()`	Concatenates row values from a column into a single delimited string. `SELECT STRING_AGG(Email, '; ') FROM Users;`	An `WITHIN GROUP (ORDER BY ...)` clause can be used when ordering is required.
`REPLACE()`	Replaces all occurrences of a specified string value with another string. `SELECT REPLACE('v1.0', '1.0', '2.0');`	Case sensitivity depends on the database collation setting.
`STUFF()`	Deletes a specified number of characters and inserts a new string at a specified start point. `SELECT STUFF('SQL Server', 5, 0, '2026 ');` — Output: `'SQL 2026 Server'`	Takes four arguments: `(string, start, length_to_delete, new_string)`.

2. Date & Time Functions

Function	Description & T-SQL Example	Notes & Key Behavior
`GETDATE()`	Returns the current system date and time as a `DATETIME` value. `SELECT GETDATE();`	Non-deterministic. Returns the current date/time value for the statement.
`SYSDATETIME()`	Returns the current system date and time with higher fractional-second precision (`DATETIME2(7)`). `SELECT SYSDATETIME();`	Provides greater fractional-second precision than `GETDATE()`.
`DATEADD()`	Adds an interval value to a specified date. `SELECT DATEADD(month, 3, GETDATE());`	Accepts intervals such as `year`, `quarter`, `month`, `day`, `hour`, and `minute`.
`DATEDIFF()`	Calculates the difference between two date expressions. `SELECT DATEDIFF(day, '2026-01-01', GETDATE());`	Counts datepart boundaries crossed rather than exact elapsed time.
`DATEDIFF_BIG()`	Works like `DATEDIFF()` but returns a `BIGINT` value. `SELECT DATEDIFF_BIG(millisecond, '2020-01-01', GETDATE());`	Supports larger ranges for granular date differences.
`DATEPART()` / `DATENAME()`	`DATEPART()` returns an integer representation of a datepart; `DATENAME()` returns a string representation. `SELECT DATENAME(month, GETDATE());` — Output: `'September'`	Useful for extracting year, month, month name, day of week, and other date components.
`EOMONTH()`	Returns the last day of the month containing a specified date. `SELECT EOMONTH(GETDATE());`	An optional second argument specifies the number of months to add or subtract before calculating the end of the month.
`FORMAT()`	Formats a date or numeric value using a culture-aware format string. `SELECT FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm');`	Flexible, but may introduce performance overhead on large datasets.

3. Mathematical & Numeric Functions

Function	Description & T-SQL Example	Notes & Key Behavior
`ROUND()`	Rounds a numeric expression to a specified length or precision. `SELECT ROUND(123.4567, 2);` — Output: `123.4600`	An optional third argument can truncate instead of round: `ROUND(123.45, 2, 1)` → `123.4500`.
`CEILING()` / `FLOOR()`	`CEILING()` returns the smallest integer greater than or equal to the value; `FLOOR()` returns the largest integer less than or equal to it. `SELECT CEILING(4.2), FLOOR(4.8);` — Output: `5, 4`	Useful for calculating pagination or bucket boundaries.
`ABS()`	Returns the absolute value of a numeric expression. `SELECT ABS(-42.5);` — Output: `42.5`	Preserves the input data type.
`ISNUMERIC()`	Determines whether an expression can be converted to one of the numeric data types recognized by SQL Server. `SELECT ISNUMERIC('123.45');` — Output: `1`	Returns `1` when the expression is recognized as numeric and `0` otherwise. Some currency symbols and other characters may also return `1`.

4. Logical & Conditional Functions

Function	Description & T-SQL Example	Notes & Key Behavior
`IIF()`	Shorthand syntax for a `CASE` expression. `SELECT IIF(Score >= 50, 'Pass', 'Fail');`	Evaluates a Boolean expression and returns one of two values. Internally translated to a `CASE` expression.
`ISNULL()`	Replaces `NULL` with a specified replacement value. `SELECT ISNULL(Phone, 'No Phone');`	T-SQL-specific. `COALESCE()` is the ANSI SQL alternative and supports multiple arguments.
`COALESCE()`	Evaluates arguments in order and returns the first non-null value. `SELECT COALESCE(Mobile, HomePhone, 'N/A');`	Standard SQL function. Accepts multiple fallback values.
`NULLIF()`	Returns `NULL` if two specified expressions are equal. `SELECT NULLIF(TotalAmount, 0);`	Frequently used to prevent divide-by-zero errors: `100 / NULLIF(Quantity, 0)`.
`CHOOSE()`	Returns the item at a specified 1-based index position from a list of values. `SELECT CHOOSE(2, 'Red', 'Green', 'Blue');` — Output: `'Green'`	Returns `NULL` if the specified index is out of bounds.

5. Conversion Functions

T-SQL provides several functions for converting between data types.

`CAST()` vs. `CONVERT()` vs. `TRY_CONVERT()`

-- 1. CAST (ANSI Standard Syntax)
SELECT CAST(123.45 AS INT); -- Output: 123

-- 2. CONVERT (T-SQL Specific - Supports style formatting codes for dates)
SELECT CONVERT(VARCHAR(10), GETDATE(), 120); -- Output: '2026-09-27' (ISO 8601 style 120)

-- 3. TRY_CONVERT / TRY_CAST (Safe conversion - Returns NULL on conversion failure)
SELECT TRY_CONVERT(INT, 'InvalidNumber'); -- Output: NULL

6. System & Metadata Functions

Function	Description & T-SQL Example	Notes & Key Behavior
`SCOPE_IDENTITY()`	Returns the last identity value inserted into an identity column within the current scope. `SELECT SCOPE_IDENTITY();`	Useful for retrieving an identity generated by an insert in the current scope.
`SUSER_SNAME()` / `USER_NAME()`	Returns the login name or current database user name. `SELECT SUSER_SNAME();`	Useful in security policies, auditing, and access-control logic.
`OBJECT_ID()`	Returns the database object identification number for a schema-scoped object. `SELECT OBJECT_ID('dbo.Orders');`	Useful for checking whether an object exists before executing DDL commands: `IF OBJECT_ID('dbo.Orders') IS NOT NULL ...`.