SQL provides a rich set of built-in functions to manipulate strings, dates, numbers, and handle NULL values. These are essential for transforming raw data into meaningful results.
String Functions
| Function | Description | Example |
|---|---|---|
UPPER(str) | Converts to uppercase | UPPER('hello') → HELLO |
LOWER(str) | Converts to lowercase | LOWER('HELLO') → hello |
LENGTH(str) | String length | LENGTH('SQL') → 3 |
TRIM(str) | Removes leading/trailing spaces | TRIM(' SQL ') → SQL |
SUBSTRING(str, start, len) | Extracts part of a string | SUBSTRING('hello', 2, 3) → ell |
CONCAT(a, b) | Joins two strings | CONCAT('a', 'b') → ab |
REPLACE(str, from, to) | Replaces occurrences | REPLACE('abc', 'b', 'x') → axc |
SELECT UPPER(name) AS upper_name,
LENGTH(name) AS name_length,
CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;Date and Time Functions
| Function | Description |
|---|---|
CURRENT_DATE | Today’s date |
CURRENT_TIMESTAMP / NOW() | Current date and time |
EXTRACT(YEAR FROM date) | Extracts year, month, day, etc. |
DATE_TRUNC('month', date) | Truncates to a specific precision |
AGE(date1, date2) | Difference between two dates (PostgreSQL) |
DATEDIFF(date1, date2) | Difference in days (MySQL, SQL Server) |
SELECT name,
EXTRACT(YEAR FROM hire_date) AS hire_year,
CURRENT_DATE - hire_date AS days_with_company
FROM employees;Numeric Functions
| Function | Description |
|---|---|
ROUND(val, decimals) | Rounds to specified decimals |
CEIL(val) | Round up |
FLOOR(val) | Round down |
ABS(val) | Absolute value |
MOD(a, b) | Remainder of a / b |
POWER(a, b) | a raised to the power b |
SELECT price,
ROUND(price * 0.08, 2) AS tax,
FLOOR(price) AS dollar_part
FROM products;NULL Handling Functions
NULLs can cause unexpected results in calculations and comparisons. Use these functions to handle them gracefully:
COALESCE
Returns the first non-NULL value from a list:
SELECT name,
COALESCE(email, 'no-email@example.com') AS contact
FROM employees;NULLIF
Returns NULL if two values are equal, otherwise returns the first value:
-- Convert zero to NULL to avoid division by zero
SELECT AVG(salary / NULLIF(hours_worked, 0)) FROM payroll;IS NULL / IS NOT NULL
Used in WHERE clauses to check for NULL:
SELECT * FROM employees WHERE email IS NULL;Type Casting
Cast between data types when needed:
-- PostgreSQL
SELECT '123'::INTEGER;
-- Standard SQL
SELECT CAST('123' AS INTEGER);Q: What does COALESCE do?
A: COALESCE returns the first non-NULL value from a list of expressions. It is commonly used to provide default values for NULL columns.
Q: What does NULLIF do?
A: NULLIF(expr1, expr2) returns NULL if expr1 equals expr2, otherwise returns expr1. It is useful for preventing division-by-zero errors.
Q: How do you concatenate strings in SQL?
A: Different databases use different syntax: CONCAT(a, b) in MySQL/PostgreSQL, a || b in PostgreSQL/SQLite, a + b in SQL Server (depends on settings).
Q: How do you extract year from a date?
A: Use EXTRACT(YEAR FROM date_column) (SQL standard) or YEAR(date_column) (MySQL shortcut).
1. Format employee names.
SELECT UPPER(name) AS name_upper,
LOWER(email) AS email_lower,
CONCAT(name, ' - ', department) AS label
FROM employees;2. Calculate age from birth_date.
SELECT name,
EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date)) AS age
FROM employees;3. Use COALESCE to handle missing emails.
SELECT name, COALESCE(email, 'unknown@company.com') AS email
FROM employees;4. Prevent division by zero.
SELECT name,
sales / NULLIF(quota, 0) AS achievement_rate
FROM sales_reps;5. Round sales amounts.
SELECT product_id,
ROUND(SUM(amount), 2) AS total_sales
FROM sales
GROUP BY product_id;6. Find orders placed in the last 30 days.
SELECT * FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';Premium Content
Unlock Built-In Functions and Null Handling and all premium lessons with a subscription.
From ₹199.99/year — See plans