SQL Server Functions
Quick SQL Server (T-SQL) function reference. Names differ from MySQL — and they're worth memorising if you switch between the two.
String
| Function | Does |
|---|---|
CONCAT(a, b, …) | Join strings (NULLs become ''). |
CONCAT_WS(sep, a, b) | Join with separator. |
UPPER(s) / LOWER(s) | Case. |
SUBSTRING(s, pos, len) | Substring. |
LEN(s) | Length (T-SQL, not LENGTH). |
LEFT / RIGHT | Take from either side. |
REPLACE, STUFF, REVERSE | Edit a string. |
FORMAT(x, 'C', 'en-AU') | Locale-aware formatting. |
Numeric
ABS, CEILING, FLOOR, ROUND(x, n), POWER, SQRT, RAND, SIGN, % for modulo.
Date / time
| Function | Returns |
|---|---|
GETDATE() / SYSDATETIME() | Current datetime. |
DATEPART(yyyy, x) | Extract a part. |
DATEADD(day, 7, x) | Add an interval. |
DATEDIFF(day, a, b) | Difference in chosen unit. |
FORMAT(x, 'yyyy-MM-dd') | Format to string. |
EOMONTH(x) | End of month. |
NULL / conditional
ISNULL(x, fallback), COALESCE(a, b, …), NULLIF(a, b), IIF(cond, t, f), CASE … END.
Conversion
CAST(x AS INT), CONVERT(VARCHAR, x, 121) (with style code), TRY_CAST, TRY_CONVERT, PARSE.
System / metadata
@VERSION, DB_NAME(), USER_NAME(), NEWID() (UUID), SCOPE_IDENTITY().
Tip: T-SQL's
LEN ignores trailing spaces; DATALENGTH doesn't. If you're checking real length of a VARCHAR with whitespace, use DATALENGTH.Example
Example
SELECT GETDATE(), DATEPART(YEAR, GETDATE()),
UPPER('hi'), CONCAT('a','b'),
ISNULL(phone, 'n/a')
FROM customers;
Try it Yourself »
Exercise
T-SQL function returning the current datetime.
SELECT
();
Seven letters.
Discussion
Loading…