SQL Server Functions
SQL Server provides T-SQL functions for text processing, numeric work, dates, conversion, and session information. Use a function because it answers a specific data question, not simply because it shortens a statement.
Understand the idea
CONCAT combines display text, ROUND changes numeric precision, and DATEADD shifts a date by a specified unit. CAST is a standard-looking conversion form; TRY_CONVERT is useful when an invalid conversion should produce NULL rather than stop a query. Formatting functions return display text and can have a different cost from simple numeric calculations. The function directory below is specific to T-SQL.
Where to try it
Use SQL Server for this example. It is shown for reading and local practice; the SQLite browser editor does not run this dialect or supply application-driver parameters. Follow the setup and explanation below in a disposable local environment.
SQLite sample data · MySQL / MariaDB sample data · See the sample tables
A worked example
SELECT CONCAT('Order ', 101) AS label,
ROUND(18.256, 2) AS rounded,
DATEADD(day, 7, CAST('2026-01-05' AS date)) AS follow_up;
Read the result
SQL Server returns Order 101, a numeric result equivalent to 18.26, and 2026-01-12. Displayed trailing zeros depend on the result type and client. Use SQL Server to run this example.
Try one change
Compare LEN('bag ') with DATALENGTH('bag '). Explain why a report about characters and a report about storage bytes need different functions.
T-SQL function directory
| Function or expression | Use and caution |
|---|---|
ASCII, CHAR, UNICODE, NCHAR | Convert characters and codes; use Unicode-aware forms for Unicode text. |
CHARINDEX, PATINDEX | Find literal or pattern positions; their pattern and collation rules differ. |
CONCAT, CONCAT_WS, CONCAT with + | Build labels; the plus operator has type-conversion and NULL considerations. |
LEFT, RIGHT, SUBSTRING | Extract part of a string using the T-SQL argument rules. |
LEN, DATALENGTH | Measure text length excluding trailing spaces, or storage bytes respectively. |
LOWER, UPPER, LTRIM, RTRIM, TRIM | Change case or remove specified outer whitespace; version-specific options differ. |
REPLACE, STUFF, REPLICATE, REVERSE, SPACE | Replace matching text, replace a segment, repeat, reverse, or pad a value. |
QUOTENAME | Quote an identifier within its supported length; still use an allowlist when choosing dynamic identifiers. |
SOUNDEX, DIFFERENCE | Make phonetic comparisons designed around particular language assumptions; not universal fuzzy matching. |
STR, FORMAT | Produce display text from values; consider formatting in the client for frequent large reports. |
ABS, SIGN, CEILING, FLOOR, ROUND | Transform numeric values with explicit rounding and range expectations. |
POWER, SQUARE, SQRT, EXP, LOG, LOG10 | Calculate powers, roots, and logarithms with valid numeric inputs. |
ACOS, ASIN, ATAN, ATN2, COS, COT, SIN, TAN, PI, DEGREES, RADIANS | Trigonometric and angle calculations; T-SQL spells the two-argument arctangent ATN2. |
RAND | Pseudo-random numeric generation; unsuitable for secrets. |
COUNT, SUM, AVG, MIN, MAX | Summarise rows; watch join duplication before aggregating. |
GETDATE, GETUTCDATE, SYSDATETIME, SYSUTCDATETIME, CURRENT_TIMESTAMP | Read server date-time values with differing precision and time-zone meaning. |
DATEADD | Shift a temporal value by a specified date part and amount. |
DATEDIFF, DATEDIFF_BIG | Count crossed date-part boundaries, with different result ranges. |
DATEPART, DATENAME, DAY, MONTH, YEAR | Read components; textual names can depend on session language. |
DATEFROMPARTS | Construct a date from year, month, and day with validation. |
ISDATE | Test a particular convertible date-time expression; target type and session settings still matter. |
CAST, CONVERT, TRY_CAST, TRY_CONVERT | Convert a value; TRY forms return NULL for many failed conversions, but some prohibited conversions still error. |
COALESCE, ISNULL, NULLIF | Handle missing values or create NULL; result-type rules are not identical between COALESCE and ISNULL. |
IIF, CASE | Choose expression results; know evaluation and type-conversion rules before hiding risky arithmetic in branches. |
ISNUMERIC | Checks convertibility to some numeric type, not specifically your intended decimal or integer type. |
CURRENT_USER, SESSION_USER, SYSTEM_USER, USER_NAME | Inspect different user contexts; permissions and execution context affect the answer. |
SESSIONPROPERTY | Read a named session setting when investigating conversion or query behaviour. |
For engine-specific options, see the Microsoft T-SQL function documentation.