Expressions

Calculated fields, validation rules, default values and expression columns in the visual query designer are written as expressions: SQL-style formulas such as Quantity * Unit_price or length(Code) > 0. They are checked as you save, and an error says where the problem is and suggests a fix, for example "ifnul" is not a function; did you mean "ifnull"?.

What you can write

Fields by name: Quantity, or in double quotes when the name has spaces: "Unit price". Case doesn't matter. In a query, a field of a particular source is written o.Quantity.
Values: numbers (42, 1.5, 2e3, 0x1F), text in single quotes ('it''s' for a quote inside), NULL, TRUE, FALSE.
Operators, in order of precedence from tightest: || (join text); * / %; + -; < <= > >=; = <> IS IS NOT IN (…) LIKE GLOB BETWEEN … AND … IS NULL; NOT; AND; OR. Use parentheses when in doubt.
CASE WHEN … THEN … ELSE … END, CASE value WHEN … THEN … END and CAST(value AS TEXT | INTEGER | REAL).
CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP (UTC).
Functions from the list below.

There are no subqueries, statements, comments or parameters. Integer division truncates: 7 / 2 is 3, 7 / 2.0 is 3.5.

Where each kind of expression may be used

	Fields	Clock (`'now'`, `CURRENT_DATE`)	Totals (`sum`, `count` …)	`random()`
Calculated field	this table's	no	no	no
Validation rule	this table's	no	no	no
Default value	none	yes	no	no
Query expression column	the query's sources	yes	yes	yes

Calculated fields and validation rules must give the same answer every time for the same record, so they cannot read the clock or use random numbers.

Expressions written before this check

Expressions saved by an earlier version of AbiData were plain SQLite SQL. Those that fall outside this language keep working, and the table designer marks them needs attention with the reason. Rewrite them when you next edit the field; a changed expression must follow the rules on this page.

Functions

Text

Function	What it gives	Example
`char(code, …)`	The text made of the given Unicode code points.	`char(65, 66) is 'AB'`
`concat(value, …)`	Joins the values as text, skipping NULLs.	`concat(First, ' ', Last)`
`concat_ws(separator, value, …)`	Joins the values with the separator, skipping NULLs.	`concat_ws(', ', City, Country)`
`format(pattern, value, …)`	Formats values like printf.
`glob(pattern, text)`	Case-sensitive match with * and ?, like text GLOB pattern.
`hex(value)`	The value's bytes as upper-case hexadecimal.
`instr(text, find)`	The position of the first occurrence of find (from 1), or 0.
`length(text)`	The number of characters.	`length(Code) > 0`
`like(pattern, text [, escape])`	Case-insensitive match with % and _, like text LIKE pattern.
`lower(text)`	The text in lower case (ASCII letters).
`ltrim(text [, chars])`	Removes spaces (or the given characters) from the start.
`octet_length(text)`	The number of bytes.
`printf(pattern, value, …)`	Formats values: %d, %f, %s, %.2f, %05d and the other printf patterns.	`printf('%.2f', Price)`
`quote(value)`	The value written as a SQL literal.
`replace(text, find, with)`	Replaces every occurrence of find.	`replace(Phone, ' ', '')`
`rtrim(text [, chars])`	Removes spaces (or the given characters) from the end.
`substr(text, start [, length])`	Part of the text; start counts from 1, a negative start from the end.	`substr(Code, 1, 3)`
`substring(text, start [, length])`	The same as substr.
`trim(text [, chars])`	Removes spaces (or the given characters) from both ends.	`trim(Code)`
`typeof(value)`	The storage class: 'null', 'integer', 'real', 'text' or 'blob'.
`unhex(text [, ignore])`	The bytes the hexadecimal text stands for.
`unicode(text)`	The code point of the first character.
`upper(text)`	The text in upper case (ASCII letters).

Numbers

Function	What it gives	Example
`abs(number)`	The number without its sign.
`acos(number)`	Arccosine, in radians.
`acosh(number)`	Inverse hyperbolic cosine.
`asin(number)`	Arcsine, in radians.
`asinh(number)`	Inverse hyperbolic sine.
`atan(number)`	Arctangent, in radians.
`atan2(y, x)`	The angle of the point (x, y), in radians.
`atanh(number)`	Inverse hyperbolic tangent.
`ceil(number)`	The smallest whole number not below it.
`ceiling(number)`	The same as ceil.
`cos(radians)`	Cosine.
`cosh(number)`	Hyperbolic cosine.
`degrees(radians)`	Converts radians to degrees.
`exp(number)`	e raised to the number.
`floor(number)`	The largest whole number not above it.
`ln(number)`	The natural logarithm.
`log([base,] number)`	The base-10 logarithm, or the logarithm in the given base.
`log10(number)`	The base-10 logarithm.
`log2(number)`	The base-2 logarithm.
`mod(a, b)`	The remainder of a divided by b, as a real number.
`pi()`	π.
`pow(base, exponent)`	base raised to exponent.
`power(base, exponent)`	The same as pow.
`radians(degrees)`	Converts degrees to radians.
`random()`	A random integer. Queries only.
`round(number [, digits])`	Rounded to the given number of decimal places (default 0).	`round(Price * 1.2, 2)`
`sign(number)`	-1, 0 or 1.
`sin(radians)`	Sine.
`sinh(number)`	Hyperbolic sine.
`sqrt(number)`	The square root.
`tan(radians)`	Tangent.
`tanh(number)`	Hyperbolic tangent.
`trunc(number)`	The whole part, towards zero.

Choices

Function	What it gives	Example
`coalesce(a, b, …)`	The first value that is not NULL.	`coalesce(Discount, 0)`
`if(condition, then [, else …])`	The same as iif; more condition and value pairs may follow.
`ifnull(a, b)`	a, or b when a is NULL.	`ifnull(Nickname, Name)`
`iif(condition, then, else)`	then when the condition is true, otherwise else.	`iif(Qty > 10, 'bulk', 'single')`
`likelihood(value, probability)`	The value; a hint for the query planner.
`likely(value)`	The value; a hint for the query planner.
`max(a, b, …)`	The largest of the values (NULL if any is NULL). In a query, max(column) is the largest value in the group.
`max(a, b, …)`	The largest of the values (NULL if any is NULL). In a query, max(column) is the largest value in the group. Queries only.
`min(a, b, …)`	The smallest of the values (NULL if any is NULL). In a query, min(column) is the smallest value in the group.
`min(a, b, …)`	The smallest of the values (NULL if any is NULL). In a query, min(column) is the smallest value in the group. Queries only.
`nullif(a, b)`	NULL when a equals b, otherwise a.	`Total / nullif(Count, 0)`
`unlikely(value)`	The value; a hint for the query planner.

Dates

Function	What it gives	Example
`date(time [, modifier …])`	The date as YYYY-MM-DD. Modifiers such as '+1 day', 'start of month'. date('now') is today (defaults only).	`date(Ordered, '+30 days')`
`datetime(time [, modifier …])`	The date and time as YYYY-MM-DD HH:MM:SS.
`julianday(time [, modifier …])`	The Julian day number; subtract two to get the days between dates.	`julianday(Shipped) - julianday(Ordered)`
`strftime(pattern, time [, modifier …])`	Formats a date: %Y year, %m month, %d day, %H:%M:%S time, %W week, %w weekday.	`strftime('%Y-%m', Ordered)`
`time(time [, modifier …])`	The time as HH:MM:SS.
`timediff(a, b)`	The time from b to a, as ±YYYY-MM-DD HH:MM:SS.SSS.
`unixepoch(time [, modifier …])`	Seconds since 1970-01-01.

JSON

Function	What it gives	Example
`json(text)`	The JSON text, minified; an error if it is not JSON.
`json_array(value, …)`	A JSON array of the values.
`json_array_length(json [, path])`	The number of elements in the array.
`json_error_position(text)`	0 for valid JSON, otherwise the position of the first error.
`json_extract(json, path, …)`	The value at the path, such as '$.items[0].name'.	`json_extract(Data, '$.colour')`
`json_insert(json, path, value, …)`	Adds values where the paths don't exist yet.
`json_object(key, value, …)`	A JSON object of the key and value pairs.
`json_patch(target, patch)`	Applies an RFC 7396 merge patch.
`json_quote(value)`	The value as a JSON value.
`json_remove(json, path, …)`	Removes the values at the paths.
`json_replace(json, path, value, …)`	Replaces values where the paths exist.
`json_set(json, path, value, …)`	Sets values, adding or replacing.
`json_type(json [, path])`	The JSON type at the path: 'object', 'array', 'text', 'integer', 'real', 'true', 'false' or 'null'.
`json_valid(text [, flags])`	1 when the text is valid JSON.

Locations

Function	What it gives	Example
`geo_area_m2(area)`	The area's size in square metres.	`geo_area_m2(Fence)`
`geo_bbox(area)`	The area's bounding box, as [west, south, east, north].
`geo_centroid(area)`	The area's centre.
`geo_distance_m(a, b)`	The distance between two locations, in metres.	`geo_distance_m(Spot, Depot) < 500`
`geo_lat(point)`	The latitude of a location.
`geo_lon(point)`	The longitude of a location.
`geo_point(lat, lon)`	A location from latitude and longitude.
`geo_within(point, area)`	1 when the location is inside the area.	`geo_within(Spot, Zone)`

Totals

Function	What it gives	Example
`avg(number)`	The average of the group's values. Queries only.
`count(value) / count(*) / count(DISTINCT value)`	The number of values (or rows) in the group. Queries only.
`group_concat(value [, separator])`	The group's values joined with commas (or the separator). Queries only.
`sum(number)`	The sum of the group's values. Queries only.
`total(number)`	The sum of the group's values, 0.0 when there are none. Queries only.