SQL String & Date Functions

String Functions

SELECT LENGTH('hello');                    -- 5
SELECT UPPER('hello'), LOWER('HELLO');
SELECT TRIM('  hello  ');                  -- 'hello'
SELECT SUBSTRING('hello world', 7, 5);     -- 'world' (start, length)
SELECT LEFT('hello world', 5), RIGHT('hello world', 5);
SELECT POSITION('world' IN 'hello world'); -- 7
SELECT REPLACE('hello world', 'world', 'SQL');
SELECT CONCAT('hello', ' ', 'world');
SELECT 'hello' || ' ' || 'world';          -- PostgreSQL concat operator
SELECT CONCAT_WS(', ', 'Alice', 'Bob');    -- 'Alice, Bob' — separator-aware concat
SELECT LPAD('42', 5, '0');                 -- '00042'

LIKE / ILIKE Pattern Matching

WHERE name LIKE 'A%'          -- starts with A
WHERE name LIKE '%son'        -- ends with son
WHERE name LIKE '_ohn'        -- exactly 4 chars ending in "ohn"
WHERE name ILIKE 'alice%'     -- case-insensitive (PostgreSQL)
WHERE name ~ '^[A-Z]'         -- regex, case-sensitive (PostgreSQL)

% matches any sequence (including empty); _ matches exactly one character — the two most commonly confused wildcards in an interview.

Date & Time Functions

SELECT NOW(), CURRENT_DATE, CURRENT_TIME;

SELECT EXTRACT(YEAR FROM NOW());     -- 2026
SELECT EXTRACT(DOW FROM NOW());      -- 0=Sunday .. 6=Saturday

SELECT DATE_TRUNC('month', NOW());   -- 2026-07-01 00:00:00 — snaps down to the start of the unit
SELECT DATE_TRUNC('week', NOW());    -- Monday of the current week

SELECT NOW() + INTERVAL '7 days';
SELECT '2026-07-06'::DATE - '2026-01-01'::DATE;   -- integer days between

SELECT AGE('2026-07-06', '1998-01-15');           -- '28 years 5 mons 22 days'
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');
SELECT TO_DATE('06/07/2026', 'DD/MM/YYYY');

Common Date Filters — the Index-Friendly Rewrite

-- Works, but defeats an index on created_at (function wraps the column)
WHERE EXTRACT(YEAR FROM created_at) = 2026

-- Equivalent, index-friendly — see SQL Indexes & Query Performance
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'

WHERE created_at >= NOW() - INTERVAL '30 days'          -- last 30 days
WHERE created_at::DATE = CURRENT_DATE - 1               -- yesterday

This is the same functional-index gotcha as [[SQL Indexes & Query Performance]] — wrapping an indexed date column in EXTRACT()/YEAR() forces evaluation per row; a range comparison lets the index do its job.

NULL-Handling Functions

SELECT COALESCE(phone, mobile, email, 'no contact') FROM users;    -- first non-NULL
SELECT revenue / NULLIF(orders, 0) AS avg_order_value FROM summary; -- NULL instead of divide-by-zero error
SELECT NULLIF(TRIM(name), '') FROM users;   -- blank string coerced to NULL

JSON Functions (PostgreSQL)

SELECT data->>'name' FROM users;              -- extract as text
SELECT data->'address'->>'city' FROM users;   -- nested access
SELECT * FROM products WHERE tags @> '["sale"]';  -- JSONB containment check

Interview Drills

1. Why is WHERE EXTRACT(YEAR FROM created_at) = 2026 slower than a range comparison on a large indexed table? — Wrapping the indexed column in a function forces the database to evaluate that function on every row rather than using the index's stored raw values directly — rewriting as created_at >= '2026-01-01' AND created_at < '2027-01-01' lets a B-tree index on created_at be used normally.
2. What's the difference between NULLIF(x, 0) and just checking x != 0 before dividing? — NULLIF(orders, 0) returns NULL when orders is 0, which makes revenue / NULLIF(orders, 0) return NULL instead of raising a divide-by-zero error — it's a single-expression guard usable inline in a SELECT list, versus needing a CASE or application-level branch.

Cross-links

[[SQL Basics & Query Fundamentals]] · [[SQL Indexes & Query Performance]] · [[SQL Window Functions]]