SQL Server Functions

Notes

• This page focuses on functions you reach for constantly in T-SQL.
• SQL Server has many more system functions; keep this as a core cheat sheet.

String functions

• CONCAT(a,b,...) – concatenate (NULL-safe)
• LEN(str) – length (trailing spaces ignored)
• LEFT(str,n), RIGHT(str,n), SUBSTRING(str,start,len)
• CHARINDEX(substr,str) – position
• REPLACE(str, from, to)
• LOWER(str), UPPER(str), TRIM(str) (newer versions)

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM dbo.Users;

Numeric

• ABS(n), CEILING(n), FLOOR(n), ROUND(n,dec)
• POWER(a,b), SQRT(n)
• RAND()

Date and time

• GETDATE(), SYSDATETIME()
• CAST(dt AS date) / CONVERT(date, dt)
• DATEADD(unit, n, dt)
• DATEDIFF(unit, a, b)
• EOMONTH(dt) – end of month

SELECT DATEADD(day, 7, GETDATE()) AS next_week;

NULL handling

• ISNULL(x, alt) – SQL Server specific
• COALESCE(a,b,...) – standard
• NULLIF(a,b)

SELECT COALESCE(nickname, first_name) AS display_name
FROM dbo.Users;

Type conversion

• CAST(x AS type)
• CONVERT(type, x, style) – style controls formatting for dates/numbers
• TRY_CAST, TRY_CONVERT – return NULL instead of error

SELECT TRY_CONVERT(int, '123') AS ok,
       TRY_CONVERT(int, 'abc') AS fail_is_null;

Aggregation and analytics helpers

• COUNT, SUM, AVG, MIN, MAX
• STRING_AGG(x, sep) – concatenate values per group (newer versions)

Common window functions (often used with OVER)

• ROW_NUMBER(), RANK(), DENSE_RANK()
• LAG(x), LEAD(x)
• FIRST_VALUE(x), LAST_VALUE(x)

SELECT id, customer_id,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
FROM dbo.Orders;

Quick reminders

• LEN ignores trailing spaces; use DATALENGTH for bytes.
• GETDATE() is datetime; SYSDATETIME() is higher precision.
• TRY_* conversions are great for messy imports.