Lesson 9: Transform values in a SELECT
Functions operate on values per row unless used as aggregates or window functions. Cast explicitly when input formats are known, and avoid turning dates or numbers into text until presentation.
SELECT customer_id,
COALESCE(NULLIF(TRIM(phone), ''), 'Not provided') AS phone_label,
DATE_TRUNC('month', created_at) AS signup_month
FROM customers;Common SQL function reference
17 examples shown
| Function or expression | Purpose | Example |
|---|---|---|
| CASE | Conditional expression returning a value. | CASE WHEN total>100 THEN 'large' ELSE 'regular' END |
| COALESCE | First non-NULL argument. | COALESCE(phone,'Not provided') |
| NULLIF | Returns NULL when two values are equal. | numerator / NULLIF(denominator,0) |
| CAST | Converts a value to a compatible type. | CAST(raw_total AS numeric(10,2)) |
| || | Concatenates text in PostgreSQL. | first_name || ' ' || last_name |
| CONCAT | Concatenates values and treats NULL as empty text in PostgreSQL. | CONCAT(first_name,' ',last_name) |
| LOWER / UPPER | Changes text letter case. | LOWER(email) |
| TRIM / BTRIM | Removes surrounding spaces or selected characters. | BTRIM(code,'-') |
| LENGTH | Counts characters or bytes depending on function. | CHAR_LENGTH(name) |
| SUBSTRING | Extracts a part of a string. | SUBSTRING(code FROM 1 FOR 3) |
| REPLACE | Replaces all occurrences of a substring. | REPLACE(phone,'-','') |
| ROUND | Rounds numeric values. | ROUND(total,2) |
| ABS | Returns the absolute numeric value. | ABS(balance) |
| CURRENT_DATE | Current date in the database session timezone. | order_date >= CURRENT_DATE - 30 |
| DATE_TRUNC | Truncates a timestamp to a chosen time unit (PostgreSQL). | DATE_TRUNC('month',created_at) |
| EXTRACT | Reads a date/time field. | EXTRACT(YEAR FROM created_at) |
| JSONB operators | Reads keys and values from PostgreSQL JSONB. | metadata ->> 'source' returns text. |
NULL-safe expressions
NULL means unknown or missing, not an empty string or zero. Arithmetic with NULL usually returns NULL. Use COALESCE only when the fallback is semantically correct. Divide safely with NULLIF, then decide whether a missing ratio should remain NULL or be displayed another way.
Progress stays in this browser.