The NULL rule
Scalar functions are strict: a NULL argument gives a NULL result. LENGTH(NULL) is NULL, not
zero; UPPER(NULL) is NULL, not the empty string.
The exceptions are the functions whose purpose is to inspect or replace a null, and the JSON
constructors, since JSON has a null of its own: COALESCE, NULLIF, IFNULL, NVL, TYPEOF,
JSON_VALID, JSON_TYPE, JSON_ARRAY and JSON_OBJECT all see the null and decide for
themselves. JSON_ARRAY(1, NULL, 'x') builds [1,null,"x"].
Strings
| Function | Does |
|---|---|
LENGTH(s), CHAR_LENGTH(s), LEN(s) |
Characters |
OCTET_LENGTH(s) |
Bytes |
UPPER(s), LOWER(s) |
Case |
TRIM(s), LTRIM(s), RTRIM(s) |
Strip whitespace |
SUBSTR(s, start [, length]), SUBSTRING |
A piece, 1-based |
LEFT(s, n), RIGHT(s, n) |
Ends |
REPLACE(s, from, to) |
Substitute |
INSTR(s, sub), POSITION(sub, s) |
Where a substring is, or 0 |
CONCAT(a, b, ...) |
Join |
CONCAT_WS(sep, a, b, ...) |
Join with a separator |
LPAD(s, n, pad), RPAD(s, n, pad) |
Pad to a width |
REPEAT(s, n), SPACE(n) |
Repeat |
REVERSE(s) |
|
FORMAT(value, format) |
Format a value |
Numbers
| Function | Does |
|---|---|
ABS(n), SIGN(n) |
|
ROUND(n [, digits]), FLOOR(n), CEIL(n), CEILING(n), TRUNC(n) |
Rounding |
MOD(a, b) |
Remainder |
POWER(base, exp), SQRT(n), EXP(n) |
|
LOG(n), LN(n), LOG10(n), LOG2(n) |
|
SIN, COS, TAN, ASIN, ACOS, ATAN, ATAN2 |
|
DEGREES(n), RADIANS(n), PI() |
|
RANDOM() |
A random value. Cannot be indexed |
Dates and times
UTC or local, and say which. NOW(), CURRENT_DATE and CURRENT_TIME are UTC.
LOCALTIMESTAMP (or NOW_LOCAL), LOCALDATE and LOCALTIME are the machine's local time. Storing
local time in a database that may move machines is usually a bug you find later, so prefer the UTC
forms unless you mean otherwise.
| Function | Does |
|---|---|
NOW() |
Current UTC date and time |
CURRENT_DATE, CURRENT_TIME |
UTC, one part of it |
LOCALTIMESTAMP, LOCALDATE, LOCALTIME |
The same, local |
DATE(dt), TIME(dt) |
Take one part of a value |
YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND |
Extract a field |
DAYOFWEEK(dt), DAYOFYEAR(dt), WEEKOFYEAR(dt), QUARTER(dt) |
|
DATEADD(part, n, dt) |
Add an interval |
DATEDIFF(part, from, to) |
Difference, in part units |
STRFTIME(format, dt) |
Format |
MAKEDATE(year, dayOfYear), MAKETIME(h, m, s) |
Build one |
TOTAL_SECONDS(interval), and TOTAL_MINUTES, TOTAL_HOURS, TOTAL_DAYS, TOTAL_MILLISECONDS |
An interval as a number |
part is 'year', 'month', 'day', 'hour', 'minute', 'second' or 'millisecond', and
the usual abbreviations work too: 'yyyy', 'yy', 'mm', 'm', 'dd', 'd', 'hh', 'mi',
'n', 'ss', 's', 'ms', 'wk', 'ww'.
SELECT * FROM Orders WHERE CreatedAt > DATEADD('day', -7, NOW());
SELECT DATEDIFF('day', ShippedAt, DeliveredAt) AS TransitDays FROM Orders;Nulls
| Function | Does |
|---|---|
COALESCE(a, b, ...) |
The first argument that is not null |
IFNULL(a, b), NVL(a, b) |
Two-argument COALESCE |
NULLIF(a, b) |
Null when the two are equal, otherwise a |
IIF(condition, then, else) |
Inline conditional |
CASE is the general form:
SELECT CASE WHEN Total > 1000 THEN 'large'
WHEN Total > 100 THEN 'medium'
ELSE 'small' END AS Size
FROM Orders;Conversion
| Function | Does |
|---|---|
CAST(expr AS type) |
The standard form |
CONVERT(expr, type) |
|
TOSTRING, TOINT, TODOUBLE, TOREAL, TODECIMAL, TOBOOLEAN, TOBOOL, TODATE, TODATETIME, TOGUID |
Named conversions |
TYPEOF(expr) |
The type name of a value |
HEX(blob), UNHEX(s) |
Hexadecimal |
BASE64(blob), UNBASE64(s) |
Base64 |
See types for what CAST does and does not promise.
Aggregates
| Function | Does |
|---|---|
COUNT(*), COUNT(expr), COUNT(DISTINCT expr) |
COUNT(*) reads a stored counter and is constant in table size |
SUM, AVG, MIN, MAX |
|
GROUP_CONCAT(expr [, separator]) |
Join the values in a group |
COUNT(*) only means something in an aggregate query, so it belongs in the select list or HAVING
of a query that groups, or of an aggregate query with no GROUP BY.
Window functions
ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE,
NTH_VALUE, PERCENT_RANK, CUME_DIST, plus the aggregates above with OVER. See
queries.
JSON
Paths are JSONPath: $.name, $.items[0], $.a.b.c.
| Function | Does |
|---|---|
JSON_VALUE(doc, path) |
A scalar at a path |
JSON_QUERY(doc, path) |
An object or array at a path |
JSON_EXTRACT(doc, path) |
Either |
JSON_SET(doc, path, value) |
Set, creating or replacing |
JSON_INSERT(doc, path, value) |
Set only if absent |
JSON_REPLACE(doc, path, value) |
Set only if present |
JSON_REMOVE(doc, path) |
Delete |
JSON_TYPE(doc [, path]) |
The JSON type at a path |
JSON_VALID(s) |
Whether it parses |
JSON_ARRAY_LENGTH(doc [, path]) |
|
JSON_ARRAY(a, b, ...), JSON_OBJECT(k, v, ...) |
Build one |
SELECT Id, JSON_VALUE(Data, '$.customer.name') AS CustomerName
FROM Documents
WHERE JSON_VALUE(Data, '$.status') = 'active';
UPDATE Documents SET Data = JSON_SET(Data, '$.status', 'archived') WHERE Id = @id;Identifiers and sequences
| Function | Does |
|---|---|
NEWGUID(), NEWUUID() |
A new GUID |
NEXTVAL(sequence), INCREMENT(sequence) |
Take the next value from a sequence |
CURRVAL(sequence), LASTINCREMENT(sequence) |
The last value taken |
LAST_INSERT_ROWID() |
The row id of the last insert on this connection |
CHANGES() |
Rows affected by the last statement |
CREATE SEQUENCE OrderNumbers START WITH 1000;
INSERT INTO Orders (Number, CustomerId)
VALUES (NEXTVAL('OrderNumbers'), @customerId);System
| Function | Does |
|---|---|
DATABASE() |
The database name |
VERSION() |
The engine version |
Reading the schema
INFORMATION_SCHEMA is queryable like any other table, which is how tooling and scaffolding find
out what is in a database. See INFORMATION_SCHEMA for what each view
holds; the short version:
SELECT * FROM INFORMATION_SCHEMA.TABLES;
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Users';
SELECT * FROM INFORMATION_SCHEMA.INDEXES;
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS;
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE;
SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS;
SELECT * FROM INFORMATION_SCHEMA.VIEWS;
SELECT * FROM INFORMATION_SCHEMA.ROUTINES;
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS;Functions of your own
A name the engine does not have is looked up in the catalogue, so a function you defined with
CREATE FUNCTION is called exactly like a built-in. A name that is neither is refused when the
statement is written rather than when it runs, including inside a CHECK, a DEFAULT, a computed
column or an index expression. See routines.