SQL ships with functions to manipulate strings, dates and numbers. The exact names vary by database, but every dialect offers concatenation, case conversion, substring extraction, date arithmetic and rounding. You also need NULL-handling helpers like COALESCE.
The everyday function families
String: CONCAT, UPPER/LOWER, LENGTH, SUBSTRING, TRIM, REPLACE. Date: CURRENT_DATE, EXTRACT/DATEPART, DATEDIFF, and date ± interval. Numeric: ROUND, CEIL, FLOOR, ABS, MOD.
String and date examples
SELECT UPPER(name) AS shout,
LENGTH(name) AS name_len,
CONCAT(name, ' (', dept_id, ')') AS labelled,
EXTRACT(YEAR FROM hire_date) AS hire_year
FROM employees;Numeric and NULL handling
SELECT ROUND(salary / 12.0, 2) AS monthly,
MOD(emp_id, 2) AS odd_even,
COALESCE(manager_id, 0) AS mgr_or_zero -- replace NULL
FROM employees;⚡ The edge
- Function names differ by dialect — e.g. string length is LENGTH (MySQL/Postgres) vs LEN (SQL Server); date parts use EXTRACT vs DATEPART. Know your database.
- Wrapping a column in a function inside WHERE usually disables index use (it's non-sargable). Prefer a range condition on the raw column — e.g. hire_date >= '2021-01-01' over YEAR(hire_date)=2021.
Worked example
Show each employee's name in uppercase and the year they were hired.
- Uppercase the name with UPPER(name).
- Pull the year out of the hire_date with EXTRACT(YEAR FROM hire_date) (or YEAR(hire_date) in some dialects).
- Select both columns from employees.
Answer: SELECT UPPER(name), EXTRACT(YEAR FROM hire_date) FROM employees;
Worked example
Replace any NULL manager_id with 0 in the output.
- COALESCE returns the first non-null of its arguments.
- COALESCE(manager_id, 0) yields manager_id when present, else 0.
- Use it in the SELECT list.
Answer: SELECT name, COALESCE(manager_id, 0) AS mgr FROM employees;
⚠ Watch out
- Function names and signatures vary by database — don't assume MySQL syntax works in SQL Server.
- Wrapping a column in a function in WHERE makes the condition non-sargable (no index) — prefer range conditions.
- Many functions propagate NULL (NULL + anything = NULL); guard with COALESCE.