SQL REFERENCE

Scalar Functions

Scalar functions operate on individual values and return a single value per row. They can be used in SELECT, WHERE, ORDER BY, and other expression contexts.

Math Functions

Function	Description	Example	Result
`ABS(x)`	Absolute value	`ABS(-42)`	`42`
`CEIL(x)`	Smallest integer >= x	`CEIL(3.2)`	`4`
`CEILING(x)`	Alias for CEIL	`CEILING(3.2)`	`4`
`FLOOR(x)`	Largest integer <= x	`FLOOR(3.8)`	`3`
`SQRT(x)`	Square root	`SQRT(16)`	`4.0`
`ROUND(x)`	Round to nearest integer	`ROUND(3.5)`	`4`
`ROUND(x, n)`	Round to n decimal places	`ROUND(3.14159, 2)`	`3.14`
`LN(x)`	Natural logarithm	`LN(2.71828)`	`1.0`
`LOG(x)`	Alias for LN (natural log)	`LOG(10)`	`2.302...`
`EXP(x)`	e raised to the power x	`EXP(1)`	`2.71828...`
`LEAST(a, b, ...)`	Smallest of the arguments	`LEAST(5, 3, 8)`	`3`
`GREATEST(a, b, ...)`	Largest of the arguments	`GREATEST(5, 3, 8)`	`8`

Math Examples

-- Normalize a value to 2 decimal places
SELECT name, ROUND(score / max_score * 100, 2) AS pct
FROM results;

-- Clamp a value to a range [0, 100]
SELECT LEAST(GREATEST(raw_value, 0), 100) AS clamped
FROM measurements;

-- Distance calculation
SELECT SQRT((x2 - x1) * (x2 - x1) + (y2 - y1) * (y2 - y1)) AS distance
FROM points;

-- Growth rate
SELECT name, LN(current_value / initial_value) AS log_return
FROM investments;

String Functions

Function	Description	Example	Result
`UPPER(s)`	Convert to uppercase	`UPPER('hello')`	`'HELLO'`
`LOWER(s)`	Convert to lowercase	`LOWER('HELLO')`	`'hello'`
`LENGTH(s)`	Number of characters	`LENGTH('hello')`	`5`
`LEN(s)`	Alias for LENGTH	`LEN('hello')`	`5`
`CHAR_LENGTH(s)`	Alias for LENGTH	`CHAR_LENGTH('hello')`	`5`
`TRIM(s)`	Remove leading/trailing whitespace	`TRIM(' hi ')`	`'hi'`
`SUBSTR(s, start, len)`	Extract substring (1-based)	`SUBSTR('hello', 2, 3)`	`'ell'`
`SUBSTRING(s, start, len)`	Alias for SUBSTR	`SUBSTRING('hello', 1, 3)`	`'hel'`
`REPLACE(s, from, to)`	Replace all occurrences	`REPLACE('aabbcc', 'bb', 'XX')`	`'aaXXcc'`
`CONCAT(a, b, ...)`	Concatenate strings	`CONCAT('a', 'b', 'c')`	`'abc'`
`a \|\| b`	Concatenation operator	`'hello' \|\| ' ' \|\| 'world'`	`'hello world'`

String Examples

-- Clean and normalize data
SELECT
  TRIM(name) AS name,
  UPPER(SUBSTR(status, 1, 1)) || LOWER(SUBSTR(status, 2, LENGTH(status) - 1)) AS status
FROM raw_data;

-- Build formatted output
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

-- Extract domain from email
SELECT
  email,
  SUBSTR(email, LENGTH(REPLACE(email, '@', '')) + 1, LENGTH(email)) AS domain
FROM users;

-- Replace sensitive data
SELECT REPLACE(phone, SUBSTR(phone, 1, 6), '***-***') AS masked_phone
FROM contacts;

Conditional Functions

Function	Description	Example	Result
`COALESCE(a, b, ...)`	First non-NULL argument	`COALESCE(NULL, NULL, 42)`	`42`
`NULLIF(a, b)`	NULL if a = b, else a	`NULLIF(0, 0)`	`NULL`
`IF(cond, then, else)`	Conditional value	`IF(x > 0, 'pos', 'non-pos')`	—

Conditional Examples

-- Default values for NULLs
SELECT
  name,
  COALESCE(department, 'Unassigned') AS department,
  COALESCE(manager_id, -1) AS manager_id
FROM employees;

-- Safe division (avoid divide by zero)
SELECT
  revenue / NULLIF(cost, 0) AS margin_ratio
FROM financials;

-- Simple conditional
SELECT
  name,
  IF(salary > 100000, 'High', 'Standard') AS band
FROM employees;

-- COALESCE chain for fallback display names
SELECT COALESCE(nickname, first_name, email, 'Anonymous') AS display
FROM users;

Type Conversion

Function	Description	Example	Result
`CAST(expr AS type)`	Convert to target type	`CAST(42 AS VARCHAR)`	`'42'`

CAST Examples

-- String to number
SELECT CAST('3.14' AS REAL);      -- 3.14
SELECT CAST('42' AS INTEGER);     -- 42

-- Number to string (for concatenation)
SELECT 'ID: ' || CAST(id AS VARCHAR) FROM products;

-- String to date
SELECT CAST('2024-06-15' AS DATE);

-- Boolean conversions
SELECT CAST(1 AS BOOLEAN);        -- TRUE
SELECT CAST(TRUE AS INTEGER);     -- 1

-- Integer to real for precise division
SELECT CAST(completed AS REAL) / total AS completion_rate
FROM projects;

Function Composition

Scalar functions can be freely composed and nested:

SELECT
  name,
  ROUND(ABS(salary - AVG(salary) OVER ()), 2) AS abs_diff,
  UPPER(SUBSTR(department, 1, 3)) AS dept_code,
  COALESCE(CAST(bonus AS VARCHAR), 'N/A') AS bonus_display
FROM employees
ORDER BY abs_diff DESC;