Tsql Functions

T-SQL Functions Reference

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

Quick Reference

String Functions

Date/Time Functions

Window Functions

SQL Server 2022 New Functions

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

String Functions Reference

Complete reference for T-SQL string manipulation functions.

Basic String Functions

Character Functions

Length Functions

Extraction Functions

Case Functions

Trimming Functions

-- SQL Server 2022 TRIM enhancements
SELECT TRIM('xy' FROM 'xxyHelloxyy')      -- 'Hello'
SELECT LTRIM('00012345', '0')              -- '12345'
SELECT RTRIM('Amount$$$', '$')             -- 'Amount'

Search and Position Functions

CHARINDEX

Find position of substring:

SELECT CHARINDEX('World', 'Hello World')           -- 7
SELECT CHARINDEX('o', 'Hello World', 5)            -- 8 (start from position 5)
SELECT CHARINDEX('xyz', 'Hello World')             -- 0 (not found)

PATINDEX

Find position using pattern (supports wildcards):

SELECT PATINDEX('%[0-9]%', 'Test123')              -- 5 (first digit)
SELECT PATINDEX('%@%.%', 'user@domain.com')        -- 5 (email pattern)
SELECT PATINDEX('[A-Z]%', 'hello')                 -- 0 (doesn't start with uppercase)

String Manipulation

REPLACE

Replace all occurrences:

SELECT REPLACE('Hello World', 'World', 'Universe')  -- 'Hello Universe'
SELECT REPLACE('aaa', 'a', 'bb')                    -- 'bbbbbb'

STUFF

Delete and insert at position:

-- STUFF(string, start, length_to_delete, insert_string)
SELECT STUFF('Hello', 2, 3, 'XYZ')                  -- 'HXYZo'
SELECT STUFF('1234567890', 4, 3, '-')               -- '123-7890'

-- Insert without deleting
SELECT STUFF('Hello', 3, 0, 'XXX')                  -- 'HeXXXllo'

TRANSLATE (SQL 2017+)

Character-by-character replacement:

SELECT TRANSLATE('2*[3+4]', '[]', '()')             -- '2*(3+4)'
SELECT TRANSLATE('Hello', 'elo', 'axy')             -- 'Haxxy'

REVERSE

Reverse a string:

SELECT REVERSE('Hello')                             -- 'olleH'

-- Check for palindrome
SELECT CASE WHEN LOWER(Name) = REVERSE(LOWER(Name))
       THEN 'Palindrome' ELSE 'Not' END

Concatenation

CONCAT (SQL 2012+)

NULL-safe concatenation:

-- Returns NULL if using + with NULL
SELECT 'Hello' + NULL + 'World'                     -- NULL

-- CONCAT treats NULL as empty string
SELECT CONCAT('Hello', NULL, 'World')               -- 'HelloWorld'
SELECT CONCAT(FirstName, ' ', LastName)

CONCAT_WS (SQL 2017+)

Concatenate with separator (ignores NULLs):

SELECT CONCAT_WS(', ', 'Apple', NULL, 'Banana', 'Cherry')
-- Result: 'Apple, Banana, Cherry'

SELECT CONCAT_WS(' - ', City, State, Country)

STRING_AGG (SQL 2017+)

Aggregate strings from multiple rows:

-- Basic aggregation
SELECT STRING_AGG(ProductName, ', ') AS Products
FROM Products

-- With ordering
SELECT CategoryID,
       STRING_AGG(ProductName, ', ') WITHIN GROUP (ORDER BY ProductName) AS Products
FROM Products
GROUP BY CategoryID

-- Pre-2017 alternative using FOR XML PATH
SELECT STUFF((
    SELECT ', ' + ProductName
    FROM Products
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS Products

Splitting Strings

STRING_SPLIT (SQL 2016+)

Split string into rows:

-- Basic split
SELECT value FROM STRING_SPLIT('apple,banana,cherry', ',')

-- With ordinal (SQL 2022+, requires compat level 160)
SELECT value, ordinal
FROM STRING_SPLIT('apple,banana,cherry', ',', 1)
ORDER BY ordinal

-- Join with split values
SELECT p.ProductID, p.ProductName
FROM Products p
WHERE p.ProductID IN (
    SELECT CAST(value AS INT)
    FROM STRING_SPLIT('1,5,10,15', ',')
)

Formatting

FORMAT (SQL 2012+)

Format using .NET format strings:

-- Numbers
SELECT FORMAT(123456.789, 'N2')                     -- '123,456.79'
SELECT FORMAT(123456.789, 'C', 'en-US')             -- '$123,456.79'
SELECT FORMAT(0.85, 'P0')                           -- '85%'

-- Dates
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd')              -- '2024-01-15'
SELECT FORMAT(GETDATE(), 'MMMM dd, yyyy')           -- 'January 15, 2024'
SELECT FORMAT(GETDATE(), 'd', 'de-DE')              -- '15.01.2024'

-- Custom patterns
SELECT FORMAT(123, '00000')                         -- '00123'
SELECT FORMAT(1234567890, '(###) ###-####')         -- '(123) 456-7890'

Performance Note: FORMAT is slower than CONVERT for simple conversions.

QUOTENAME

Add delimiters for identifiers:

SELECT QUOTENAME('My Table')                        -- '[My Table]'
SELECT QUOTENAME('My Table', '"')                   -- '"My Table"'
SELECT QUOTENAME('O''Brien')                        -- '[O'Brien]'

-- Dynamic SQL safety
DECLARE @TableName NVARCHAR(128) = 'Users'
EXEC('SELECT * FROM ' + QUOTENAME(@TableName))

Phonetic Functions

SOUNDEX

4-character phonetic code:

SELECT SOUNDEX('Smith')                             -- 'S530'
SELECT SOUNDEX('Smyth')                             -- 'S530'

-- Find similar names
SELECT * FROM Customers
WHERE SOUNDEX(LastName) = SOUNDEX('Smith')

DIFFERENCE

Compare SOUNDEX values (0-4, higher = more similar):

SELECT DIFFERENCE('Smith', 'Smyth')                 -- 4 (very similar)
SELECT DIFFERENCE('Smith', 'Jones')                 -- 2 (less similar)

-- Fuzzy name matching
SELECT * FROM Customers
WHERE DIFFERENCE(LastName, 'Smith') >= 3

Miscellaneous

REPLICATE

Repeat a string:

SELECT REPLICATE('Ab', 3)                           -- 'AbAbAb'
SELECT REPLICATE('0', 5 - LEN(CAST(@Num AS VARCHAR))) + CAST(@Num AS VARCHAR)  -- Zero padding

SPACE

Generate spaces:

SELECT 'Hello' + SPACE(10) + 'World'
SELECT REPLICATE(' ', 10)                           -- Equivalent

STR

Convert number to string with formatting:

SELECT STR(123.456, 10, 2)                          -- '    123.46'
SELECT STR(123.456, 6, 1)                           -- ' 123.5'

STRING_ESCAPE (SQL 2016+)

Escape special characters:

SELECT STRING_ESCAPE('Tab	here
newline', 'json')
-- Result: 'Tab\there\nnewline'

Performance Considerations

1. Avoid functions in WHERE clauses on indexed columns - breaks SARGability 2. Use CONCAT instead of + for NULL handling - cleaner, safer 3. FORMAT is slow - use CONVERT with style codes for performance-critical code 4. STRING_AGG is faster than FOR XML PATH - use when available 5. CHARINDEX vs LIKE - CHARINDEX can be SARGable with careful use

Window Functions Reference

Complete reference for T-SQL window and ranking functions.

Ranking Functions

ROW_NUMBER()

Unique sequential numbers (no ties):

SELECT Name, Score,
       ROW_NUMBER() OVER (ORDER BY Score DESC) AS RowNum
FROM Students
-- Scores: 100, 95, 95, 90 -> RowNum: 1, 2, 3, 4

RANK()

Same rank for ties, gaps after:

SELECT Name, Score,
       RANK() OVER (ORDER BY Score DESC) AS Rank
FROM Students
-- Scores: 100, 95, 95, 90 -> Rank: 1, 2, 2, 4

DENSE_RANK()

Same rank for ties, no gaps:

SELECT Name, Score,
       DENSE_RANK() OVER (ORDER BY Score DESC) AS DenseRank
FROM Students
-- Scores: 100, 95, 95, 90 -> DenseRank: 1, 2, 2, 3

NTILE(n)

Distribute into n equal groups:

SELECT Name, Score,
       NTILE(4) OVER (ORDER BY Score DESC) AS Quartile
FROM Students
-- Divides into 4 groups (quartiles)

Offset Functions

LAG()

Access previous row:

-- Basic usage
SELECT Date, Value,
       LAG(Value) OVER (ORDER BY Date) AS PrevValue
FROM Metrics

-- With offset and default
SELECT Date, Value,
       LAG(Value, 3, 0) OVER (ORDER BY Date) AS Value3DaysAgo
FROM Metrics

-- Calculate change
SELECT Date, Value,
       Value - LAG(Value, 1, Value) OVER (ORDER BY Date) AS DailyChange
FROM Metrics

LEAD()

Access next row:

SELECT Date, Value,
       LEAD(Value) OVER (ORDER BY Date) AS NextValue,
       LEAD(Value, 7) OVER (ORDER BY Date) AS ValueIn7Days
FROM Metrics

FIRST_VALUE()

First value in window:

SELECT Name, DeptID, Salary,
       FIRST_VALUE(Name) OVER (
           PARTITION BY DeptID
           ORDER BY Salary DESC
       ) AS HighestPaidInDept
FROM Employees

LAST_VALUE()

Last value in window (requires frame specification):

SELECT Name, DeptID, Salary,
       LAST_VALUE(Name) OVER (
           PARTITION BY DeptID
           ORDER BY Salary DESC
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS LowestPaidInDept
FROM Employees

Important: LAST_VALUE requires explicit frame because default frame is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

IGNORE NULLS (SQL Server 2022+)

Skip NULL values in offset functions:

-- Get last non-NULL value
SELECT Date, Value,
       LAST_VALUE(Value) IGNORE NULLS OVER (
           ORDER BY Date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS LastKnownValue
FROM Measurements

-- Forward-fill missing data
SELECT Date, Value,
       COALESCE(Value,
           LAST_VALUE(Value) IGNORE NULLS OVER (
               ORDER BY Date
               ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
           )
       ) AS FilledValue
FROM SensorData

-- RESPECT NULLS is default (explicit)
SELECT FIRST_VALUE(Value) RESPECT NULLS OVER (ORDER BY Date)

Distribution Functions

PERCENT_RANK()

Relative rank as percentage (0 to 1):

SELECT Name, Score,
       PERCENT_RANK() OVER (ORDER BY Score) AS PercentRank
FROM Students
-- Formula: (rank - 1) / (total_rows - 1)

CUME_DIST()

Cumulative distribution:

SELECT Name, Score,
       CUME_DIST() OVER (ORDER BY Score) AS CumeDist
FROM Students
-- Percentage of rows <= current row

PERCENTILE_CONT()

Continuous percentile (interpolates):

SELECT DeptID,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary)
           OVER (PARTITION BY DeptID) AS MedianSalary
FROM Employees
-- May return value not in dataset (interpolated)

PERCENTILE_DISC()

Discrete percentile (actual value):

SELECT DeptID,
       PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY Salary)
           OVER (PARTITION BY DeptID) AS MedianSalary
FROM Employees
-- Returns actual value from dataset

Aggregate Window Functions

All aggregate functions can be used with OVER clause:

-- Running total
SELECT OrderID, Amount,
       SUM(Amount) OVER (ORDER BY OrderDate) AS RunningTotal
FROM Orders

-- Partition aggregates
SELECT DeptID, EmployeeName, Salary,
       AVG(Salary) OVER (PARTITION BY DeptID) AS DeptAvgSalary,
       Salary - AVG(Salary) OVER (PARTITION BY DeptID) AS DiffFromAvg
FROM Employees

-- Count distinct per partition (workaround)
SELECT CustomerID, ProductID,
       DENSE_RANK() OVER (PARTITION BY CustomerID ORDER BY ProductID) +
       DENSE_RANK() OVER (PARTITION BY CustomerID ORDER BY ProductID DESC) - 1
       AS DistinctProductCount
FROM Orders

Window Frame Specifications

Frame Types

-- ROWS: Physical row count
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

-- RANGE: Logical value range (includes ties)
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- GROUPS (SQL 2022+): Peer group count
GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING

Frame Bounds

UNBOUNDED PRECEDING     -- From start of partition
n PRECEDING             -- n rows/range before current
CURRENT ROW             -- Current row
n FOLLOWING             -- n rows/range after current
UNBOUNDED FOLLOWING     -- To end of partition

Common Frame Patterns

-- Running total (default for ordered aggregates)
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- Entire partition
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

-- Moving average (7-day)
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

-- Centered moving average
ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING

-- Exclude current row from aggregate
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING

ROWS vs RANGE

-- ROWS counts physical rows
SELECT Date, Value,
       SUM(Value) OVER (ORDER BY Date ROWS 2 PRECEDING) AS RowSum
-- Exactly 3 rows: current + 2 preceding

-- RANGE uses logical values (includes ties)
SELECT Date, Value,
       SUM(Value) OVER (ORDER BY Date RANGE 2 PRECEDING) AS RangeSum
-- All rows with Date within 2 days before current

WINDOW Clause (SQL Server 2022+)

Define reusable window specifications:

SELECT
    OrderID,
    CustomerID,
    Amount,
    SUM(Amount) OVER w AS RunningTotal,
    AVG(Amount) OVER w AS RunningAvg,
    COUNT(*) OVER w AS RunningCount
FROM Orders
WINDOW w AS (PARTITION BY CustomerID ORDER BY OrderDate)

-- Multiple windows
SELECT
    OrderID,
    SUM(Amount) OVER daily AS DailyTotal,
    SUM(Amount) OVER monthly AS MonthlyTotal
FROM Orders
WINDOW
    daily AS (PARTITION BY CAST(OrderDate AS DATE) ORDER BY OrderID),
    monthly AS (PARTITION BY YEAR(OrderDate), MONTH(OrderDate) ORDER BY OrderID)

Practical Examples

Running Totals and Averages

SELECT
    Date,
    Sales,
    SUM(Sales) OVER (ORDER BY Date) AS CumulativeSales,
    AVG(Sales) OVER (ORDER BY Date ROWS 6 PRECEDING) AS MovingAvg7Day,
    AVG(Sales) OVER (
        PARTITION BY YEAR(Date), MONTH(Date)
        ORDER BY Date
    ) AS MTDAverage
FROM DailySales

Year-over-Year Comparison

SELECT
    Date,
    Sales,
    LAG(Sales, 365) OVER (ORDER BY Date) AS SalesLastYear,
    Sales - LAG(Sales, 365) OVER (ORDER BY Date) AS YoYChange,
    CASE
        WHEN LAG(Sales, 365) OVER (ORDER BY Date) > 0
        THEN (Sales - LAG(Sales, 365) OVER (ORDER BY Date)) * 100.0 /
             LAG(Sales, 365) OVER (ORDER BY Date)
        ELSE NULL
    END AS YoYChangePercent
FROM DailySales

Top N per Group

-- Top 3 products per category
WITH RankedProducts AS (
    SELECT
        CategoryID,
        ProductName,
        TotalSales,
        ROW_NUMBER() OVER (
            PARTITION BY CategoryID
            ORDER BY TotalSales DESC
        ) AS Rank
    FROM ProductSales
)
SELECT * FROM RankedProducts WHERE Rank <= 3

Gap and Island Detection

-- Find consecutive date ranges
WITH Grouped AS (
    SELECT
        Date,
        Date - ROW_NUMBER() OVER (ORDER BY Date) * INTERVAL '1 day' AS GroupID
    FROM ActiveDates
)
SELECT
    MIN(Date) AS StartDate,
    MAX(Date) AS EndDate,
    COUNT(*) AS ConsecutiveDays
FROM Grouped
GROUP BY GroupID

Cumulative Distribution

SELECT
    Score,
    COUNT(*) AS Frequency,
    SUM(COUNT(*)) OVER (ORDER BY Score) AS CumulativeFrequency,
    SUM(COUNT(*)) OVER (ORDER BY Score) * 100.0 /
        SUM(COUNT(*)) OVER () AS CumulativePercent
FROM TestScores
GROUP BY Score

Performance Considerations

1. Index for ORDER BY column - Critical for window function performance 2. ROWS vs RANGE - ROWS is typically faster (no tie handling) 3. Multiple window functions - Same OVER clause shares a single sort 4. Batch mode - SQL 2019+ can use batch mode for window functions on rowstore 5. Memory grants - Large partitions may spill to disk; monitor for tempdb spills