Functions and Expressions

Use built-in functions to transform and compute values in queries.

String functions

SELECT LOWER(name), UPPER(name), LENGTH(name) FROM users;
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

Numeric functions

SELECT ABS(x), ROUND(x, 2), CEIL(x), FLOOR(x) FROM metrics;

Date/time functions

SELECT NOW() AS ts, CURRENT_DATE AS d;
SELECT DATE_TRUNC('month', created_at) AS month FROM orders; -- Postgres

Engines differ: use DATE_FORMAT (MySQL) or strftime (SQLite) equivalents.

JSON functions

-- Postgres
SELECT data->>'name' AS name FROM events;
SELECT jsonb_build_object('id', id, 'name', name) FROM users;

CASE expressions

SELECT
  CASE
    WHEN total >= 100 THEN 'vip'
    WHEN total >= 50  THEN 'plus'
    ELSE 'basic'
  END AS tier
FROM customers;

NULL and COALESCE

SELECT COALESCE(nickname, name) AS display FROM users;

Engine nuances

• Function names and arguments vary; consult your engine docs
• Beware implicit casts in expressions; cast explicitly for clarity

Summary

• Strings, numbers, dates, and JSON have rich function sets
• CASE and COALESCE cover many conditional and null-handling needs