SQL REFERENCE

Scalar Functions

Math and string functions available in expressions.

Math Functions

Function	Description	Example
ABS(x)	Absolute value	ABS(-1.0) → 1.0
CEIL(x)	Round up	CEIL(1.2) → 2.0
FLOOR(x)	Round down	FLOOR(2.7) → 2.0
SQRT(x)	Square root	SQRT(9.0) → 3.0
ROUND(x)	Round to nearest integer	ROUND(2.7) → 3.0
LN(x)	Natural logarithm	LN(1.0) → 0.0
EXP(x)	Exponential	EXP(0.0) → 1.0
LEAST(a,b,...)	Minimum of arguments	LEAST(3, 1, 2) → 1
GREATEST(a,b,...)	Maximum of arguments	GREATEST(3, 1, 2) → 3

SELECT ABS(x) FROM nums;
SELECT SQRT(x) FROM nums WHERE x > 0.0;
SELECT ROUND(LN(v), 6) FROM consts WHERE v = 1.0;   -- 0.0
SELECT ROUND(EXP(v), 6) FROM consts WHERE v = 0.0;  -- 1.0

String Functions

Function	Description	Example
UPPER(s)	To uppercase	UPPER('abc') → 'ABC'
LOWER(s)	To lowercase	LOWER('ABC') → 'abc'
LENGTH(s)	String length	LENGTH('hello') → 5
TRIM(s)	Remove whitespace	TRIM(' hi ') → 'hi'
SUBSTR(s,start,len)	Substring	SUBSTR('Hello World',1,5) → 'Hello'
REPLACE(s,from,to)	Replace substring	REPLACE('Hello World','World','Teide') → 'Hello Teide'
CONCAT(a,b,...)	Concatenate	CONCAT('abc','!') → 'abc!'

Aliases: LEN, CHAR_LENGTH, CHARACTER_LENGTH (for LENGTH); BTRIM (for TRIM); SUBSTRING (for SUBSTR).

SELECT UPPER(s) FROM strs WHERE s = 'abc';           -- ABC
SELECT LOWER(s) FROM strs WHERE s = 'UPPER lower';   -- upper lower
SELECT LENGTH(s) FROM strs WHERE s = 'Hello World';  -- 11
SELECT TRIM(s) FROM strs WHERE s = '  spaces  ';     -- spaces
SELECT SUBSTR(s, 7, 5) FROM strs WHERE s = 'Hello World'; -- World
SELECT REPLACE(s, 'World', 'Teide') FROM strs WHERE s = 'Hello World'; -- Hello Teide
SELECT s || '!' FROM strs WHERE s = 'abc';           -- abc!

Date/Time Functions

Function	Description
CURRENT_DATE	Current date
CURRENT_TIMESTAMP / NOW()	Current timestamp
EXTRACT(field FROM expr)	Extract date part
DATE_TRUNC(unit, expr)	Truncate timestamp
DATE_DIFF(unit, start, end)	Difference between timestamps

EXTRACT fields: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DOW, DOY, EPOCH

DATE_TRUNC units: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND