SQL Functions
Concept
Functions transform values. Scalar (single-row) functions return one value per row (e.g., UPPER(name)); aggregate functions (Module 11) return one value per group. Function names and behavior vary more between DBMSs than any other part of SQL, so dialect labels are especially important in this module.
Why It Matters
Functions clean data (trimming spaces, fixing case), derive new values (age from birth date, year from timestamp), handle NULLs, and implement business logic (CASE expressions for categories).
Syntax
SELECT FUNCTION_NAME(argument1, argument2, ...) AS alias
FROM table;Example
10.1 String Functions CONCAT(a,b,...), a || b CONCAT(a,b,...), a + b CONCAT(a,b) (2 args), a || b a || b, concat() 3.44+ Length (characters) LENGTH(s), CHAR_LENGTH(s) CHAR_LENGTH(s) (LENGTH = bytes) LEN(s) (ignores trailing spaces) LOWER, UPPER TRIM, LTRIM, RTRIM same (TRIM 2017+)
SUBSTRING(s FROM p FOR n), SUBSTR(s,p,n) SUBSTRING(s,p,n), SUBSTR SUBSTR(s,p,n), SUBSTRING 3.34+ Left / right n chars LEFT(s,n), RIGHT(s,n) SUBSTR(s,1,n), SUBSTR(s,-n) POSITION(sub IN s), STRPOS LOCATE(sub,s), INSTR(s,sub)
String positions in SQL are 1-based.
Data cleaning example:
Other useful string functions: LPAD/RPAD (PostgreSQL, MySQL, Oracle), REPEAT/REPLICATE, REVERSE, INITCAP (PostgreSQL, Oracle), STRING_AGG/GROUP_CONCAT/LISTAGG (aggregate, Module 11), SPLIT_PART (PostgreSQL), STRING_SPLIT (SQL Server).
10.2 Numeric Functions Round to d decimals (negative d rounds left of decimal)
ROUND(1234.567, 2) / ROUND(1234.567, -2) CEIL(x) / CEILING(x) Smallest integer ≥ x Largest integer ≤ x MOD(a, b) / a % b x to the power y POWER(2, 10) TRUNC(x, d) / TRUNCATE(x, d) Cut decimals without rounding TRUNC(4.99, 1) Dialect notes:
SQL Server: CEILING only (no CEIL); ROUND(x, d) requires the second argument; modulo is % only; truncation is ROUND(x, d, 1).
MySQL: TRUNCATE(x, d); both MOD() and %.
PostgreSQL: TRUNC, both MOD() and %.
Oracle: MOD() only (no %), TRUNC, CEIL.
SQLite: ROUND, ABS, %; CEIL, FLOOR, POWER, SQRT, MOD available when compiled with math functions (default in 3.35+ CLI builds).
Negative numbers: FLOOR(-4.1) = -5, CEIL(-4.9) = -4, ROUND(-2.5) = -3 (half away from zero for exact numerics in most DBMSs).
(200 mod 12 = 8; 30 mod 12 = 6.) 10.3 Date and Time Functions Current date and time: CURRENT_DATE, CURDATE() CAST(GETDATE() AS DATE)
TRUNC(SYSDATE), CURRENT_DATE (has time part) DATE('now'), CURRENT_DATE CURRENT_TIME, CURTIME() CAST(GETDATE() AS TIME) TIME('now'), CURRENT_TIME CURRENT_TIMESTAMP, NOW()
CURRENT_TIMESTAMP, GETDATE(), SYSDATETIME() SYSTIMESTAMP, CURRENT_TIMESTAMP CURRENT_TIMESTAMP (UTC) Extracting parts: Date difference:
Note the argument order: MySQL DATEDIFF(end, start) vs SQL Server DATEDIFF(unit, start, end). Also, SQL Server DATEDIFF(year, ...) counts boundaries crossed, not full years: DATEDIFF(year, '2025-12-31', '2026-01-01') returns 1.
Date addition and subtraction: Truncating and formatting dates:
Years of service (PostgreSQL, fixed "as of" date 2026-09-29 so results are reproducible):
MySQL equivalent: TIMESTAMPDIFF(YEAR, hire_date, '2026-09-29').
10.4 NULL Functions COALESCE(a, b, c,...) First non-NULL argument Standard; all DBMSs NULLIF(a, b) NULL if a = b, else a IFNULL(a, b) a if not NULL, else b ISNULL(a, b)
SQL Server (MySQL's ISNULL(a) is a test returning 0/1!) NVL2(a, b, c) b if a not NULL, else c NULLIF to avoid division by zero:
COALESCE across several columns:
10.5 Conditional Expressions — CASE CASE is SQL's if/else. It is an expression, so it can appear in SELECT, WHERE, ORDER BY, GROUP BY, and inside aggregates.
Searched CASE (conditions):
WHEN clauses are evaluated top to bottom; the first TRUE wins. Put the most specific conditions first.
If no WHEN matches and there is no ELSE, the result is NULL.
All THEN/ELSE results should have compatible data types.
Simple CASE (compare one expression to values):
Simple CASE cannot test for NULL (CASE col WHEN NULL never matches); use searched CASE with IS NULL.
CASE in ORDER BY (custom sort order):
Shortcuts: IF(cond, a, b) (MySQL), IIF(cond, a, b) (SQL Server 2012+, SQLite 3.32+), DECODE(expr, v1, r1, ..., default) (Oracle). Prefer CASE for portability.
10.6 Type Conversion
-- PostgreSQL (most functions also valid in MySQL/SQL Server where noted above)
SELECT
CONCAT(first_name, ' ', last_name) AS full_name,
UPPER(last_name) AS last_upper,
LOWER(email) AS email_lower,
LENGTH(first_name) AS name_len,
SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS email_user,
LEFT(first_name, 1) || LEFT(last_name, 1) AS initials,
REPLACE(job_title, 'Engineer', 'Developer') AS new_title
FROM employees
WHERE emp_id IN (2, 3);SELECT TRIM(' Pune ') AS t, -- 'Pune'
LTRIM(' Pune ') AS lt, -- 'Pune '
RTRIM(' Pune ') AS rt, -- ' Pune'
TRIM(BOTH '0' FROM '000123') AS t0; -- '123' (standard syntax; PostgreSQL, MySQL, Oracle; SQL Server 2022+)UPDATE customers
SET email = LOWER(TRIM(email))
WHERE email IS NOT NULL;SELECT product_name,
price,
ROUND(price * 1.18, 2) AS price_with_gst,
CEIL(price / 1000) AS price_in_thousands_up,
FLOOR(price / 1000) AS price_in_thousands_down,
MOD(stock, 12) AS loose_units_after_dozens
FROM products
WHERE product_id IN (102, 103);-- Standard (PostgreSQL, MySQL, Oracle)
SELECT order_id, EXTRACT(YEAR FROM order_date) AS yr, EXTRACT(MONTH FROM order_date) AS mon FROM orders;
-- SQL Server
SELECT order_id, YEAR(order_date) AS yr, MONTH(order_date) AS mon, DATEPART(weekday, order_date) AS dow FROM orders;
-- MySQL also
SELECT order_id, YEAR(order_date), MONTH(order_date), DAYNAME(order_date) FROM orders;
-- SQLite
SELECT order_id, CAST(strftime('%Y', order_date) AS INTEGER) AS yr, strftime('%m', order_date) AS mon FROM orders;-- PostgreSQL: DATE - DATE = integer days
SELECT DATE '2026-03-10' - DATE '2026-01-05' AS days; -- 64
SELECT AGE(DATE '2026-09-29', DATE '2015-03-01'); -- 11 years 6 mons 28 days
-- MySQL
SELECT DATEDIFF('2026-03-10', '2026-01-05') AS days; -- 64 (later date first)
SELECT TIMESTAMPDIFF(MONTH, '2026-01-05', '2026-03-10') AS months; -- 2
-- SQL Server
SELECT DATEDIFF(day, '2026-01-05', '2026-03-10') AS days; -- 64 (start date first)
-- Oracle: DATE - DATE = number of days (can be fractional)
SELECT DATE '2026-03-10' - DATE '2026-01-05' AS days FROM dual; -- 64
SELECT MONTHS_BETWEEN(DATE '2026-03-10', DATE '2026-01-05') FROM dual;
-- SQLite
SELECT julianday('2026-03-10') - julianday('2026-01-05') AS days; -- 64.0-- PostgreSQL
SELECT order_date + INTERVAL '30 days' AS due_date FROM orders;
SELECT order_date + 30 AS due_date FROM orders; -- DATE + integer days
SELECT CURRENT_DATE - INTERVAL '1 month';
-- MySQL
SELECT DATE_ADD(order_date, INTERVAL 30 DAY) AS due_date FROM orders;
SELECT DATE_SUB(CURDATE(), INTERVAL 1 MONTH);
-- SQL Server
SELECT DATEADD(day, 30, order_date) AS due_date FROM orders;
SELECT DATEADD(month, -1, CAST(GETDATE() AS DATE));
-- Oracle
SELECT order_date + 30 AS due_date FROM orders;
SELECT ADD_MONTHS(TRUNC(SYSDATE), -1) FROM dual;
-- SQLite
SELECT date(order_date, '+30 days') AS due_date FROM orders;
SELECT date('now', '-1 month');-- PostgreSQL
SELECT DATE_TRUNC('month', order_date)::DATE AS month_start, TO_CHAR(order_date, 'YYYY-MM') AS ym FROM orders;
-- MySQL
SELECT DATE_FORMAT(order_date, '%Y-%m-01') AS month_start, DATE_FORMAT(order_date, '%Y-%m') AS ym FROM orders;
-- SQL Server
SELECT DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1) AS month_start, FORMAT(order_date, 'yyyy-MM') AS ym FROM orders;
-- SQL Server 2022+: DATETRUNC(month, order_date)
-- Oracle
SELECT TRUNC(order_date, 'MM') AS month_start, TO_CHAR(order_date, 'YYYY-MM') AS ym FROM orders;
-- SQLite
SELECT date(order_date, 'start of month') AS month_start, strftime('%Y-%m', order_date) AS ym FROM orders;SELECT first_name,
hire_date,
EXTRACT(YEAR FROM AGE(DATE '2026-09-29', hire_date)) AS years_of_service
FROM employees
ORDER BY hire_date;SELECT name,
COALESCE(email, 'no-email@placeholder') AS contact_email
FROM customers;Expected Result
Order summary with functions:
-- PostgreSQL
SELECT order_id,
TO_CHAR(order_date, 'Mon DD, YYYY') AS order_day,
ROUND(total_amount / 1000, 1) AS amount_k,
CASE WHEN status = 'CANCELLED' THEN 0 ELSE total_amount END AS billable
FROM orders
ORDER BY order_id;Common Mistakes
Assuming a function exists everywhere (LEFT in Oracle, LEN in PostgreSQL, DATEDIFF argument order).
Using MySQL LENGTH for character count on multibyte text (it returns bytes).
Forgetting ELSE in CASE and getting unexpected NULLs.
Using ISNULL(a, b) in MySQL (it takes one argument there).
Wrapping indexed columns in functions inside WHERE, preventing index use.
Relying on implicit conversion (WHERE order_date = '05-01-2026') whose interpretation depends on settings.
SQL Server DATEDIFF(year, ...) treated as "full years".
Best Practices
Prefer standard functions (COALESCE, NULLIF, CASE, CAST, EXTRACT, CURRENT_TIMESTAMP) for portability.
Clean data on the way in (TRIM, LOWER for emails) rather than on every read.
Use CASE for readable business rules and document the thresholds.
Use NULLIF to guard divisions.
Interview Relevance
High: "COALESCE vs ISNULL/NVL?", "What does NULLIF do?", "Write a CASE expression to classify...", "How do you compute age/tenure?", "Difference between ROUND, CEIL, FLOOR, TRUNC?".
Practice Questions
Show each customer's name in uppercase and the domain part of their email (after @).
Show each product's price rounded up to the nearest hundred.
Show orders with the number of days between the order date and 2026-03-31.
Categorize products as 'Budget' (< 1000), 'Mid' (1000–20000), 'Premium' (> 20000).
Show each customer's email, or 'MISSING' if NULL, using three different functions in three dialects.
Compute the percentage of stock remaining assuming capacity is 250 units, guarding against divide-by-zero.