Scalar Functions
Scalar Functions
Stoolap provides a comprehensive set of scalar functions that operate on individual values and return a single result. This document covers all available scalar functions organized by category.
String Functions
UPPER
Converts a string to uppercase.
SELECT UPPER('hello'); -- Returns 'HELLO'
SELECT UPPER(name) FROM users;
LOWER
Converts a string to lowercase.
SELECT LOWER('HELLO'); -- Returns 'hello'
SELECT LOWER(email) FROM users;
LENGTH / CHAR_LENGTH
Returns the number of characters in a string.
SELECT LENGTH('hello'); -- Returns 5
SELECT CHAR_LENGTH('hello'); -- Same as LENGTH
SUBSTRING / SUBSTR
Extracts a substring from a string.
-- SUBSTRING(string, start [, length])
SELECT SUBSTRING('hello world', 1, 5); -- Returns 'hello'
SELECT SUBSTRING('hello world', 7); -- Returns 'world'
SELECT SUBSTR('hello', 2, 3); -- Returns 'ell'
Note: Position is 1-indexed (first character is at position 1).
CONCAT
Concatenates two or more strings.
SELECT CONCAT('hello', ' ', 'world'); -- Returns 'hello world'
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
CONCAT_WS
Concatenates strings with a separator (Concatenate With Separator).
-- CONCAT_WS(separator, string1, string2, ...)
SELECT CONCAT_WS(', ', 'apple', 'banana', 'cherry'); -- Returns 'apple, banana, cherry'
SELECT CONCAT_WS('-', city, state, zip) AS address FROM customers;
Note: NULL values are skipped.
TRIM / LTRIM / RTRIM
Removes whitespace from strings.
SELECT TRIM(' hello '); -- Returns 'hello'
SELECT LTRIM(' hello'); -- Returns 'hello'
SELECT RTRIM('hello '); -- Returns 'hello'
LPAD / RPAD
Pads a string to a specified length.
-- LPAD(string, length [, pad_string])
SELECT LPAD('42', 5, '0'); -- Returns '00042'
SELECT RPAD('hello', 10, '-'); -- Returns 'hello-----'
LEFT / RIGHT
Returns characters from the left or right side of a string.
SELECT LEFT('hello world', 5); -- Returns 'hello'
SELECT RIGHT('hello world', 5); -- Returns 'world'
REPLACE
Replaces occurrences of a substring.
SELECT REPLACE('hello world', 'world', 'there'); -- Returns 'hello there'
REVERSE
Reverses a string.
SELECT REVERSE('hello'); -- Returns 'olleh'
REPEAT
Repeats a string a specified number of times.
SELECT REPEAT('ab', 3); -- Returns 'ababab'
LOCATE
Finds the position of a substring within a string.
-- LOCATE(substring, string [, start_position])
SELECT LOCATE('l', 'hello'); -- Returns 3 (1-based)
SELECT LOCATE('o', 'hello world', 5); -- Returns 8 (starting from position 5)
Returns 0 if not found.
POSITION
SQL standard syntax for finding substring position.
SELECT POSITION('l' IN 'hello'); -- Returns 3
STRPOS
PostgreSQL-style function for finding substring position.
-- STRPOS(string, substring)
SELECT STRPOS('hello', 'l'); -- Returns 3
INSTR
Finds the position of a substring (string first, then substring).
-- INSTR(string, substring)
SELECT INSTR('hello', 'l'); -- Returns 3
SPLIT_PART
Splits a string by a delimiter and returns a specific part.
-- SPLIT_PART(string, delimiter, part_number)
SELECT SPLIT_PART('a,b,c', ',', 2); -- Returns 'b'
CHAR
Returns the character for an ASCII/Unicode code point.
SELECT CHAR(65); -- Returns 'A'
SELECT CHAR(97); -- Returns 'a'
COLLATE
Applies a specific collation for sorting and comparison.
SELECT COLLATE('Hello', 'NOCASE');
SELECT * FROM users ORDER BY COLLATE(name, 'NOCASE');
Supported collations:
BINARY- Case-sensitive, accent-sensitiveNOCASE,CASE_INSENSITIVE- Case-insensitiveNOACCENT,ACCENT_INSENSITIVE- Accent-insensitiveNUMERIC- Compare strings as numbers
Numeric Functions
ABS
Returns the absolute value.
SELECT ABS(-10); -- Returns 10
SELECT ABS(-3.14); -- Returns 3.14
ROUND
Rounds a number to specified decimal places.
SELECT ROUND(3.14159); -- Returns 3.0
SELECT ROUND(3.14159, 2); -- Returns 3.14
CEIL / CEILING
Returns the smallest integer >= the number.
SELECT CEIL(3.14); -- Returns 4.0
SELECT CEILING(-3.14); -- Returns -3.0
FLOOR
Returns the largest integer <= the number.
SELECT FLOOR(3.99); -- Returns 3.0
SELECT FLOOR(-3.14); -- Returns -4.0
TRUNC / TRUNCATE
Truncates a number to specified decimal places (towards zero).
SELECT TRUNC(3.99); -- Returns 3.0
SELECT TRUNCATE(3.14159, 2); -- Returns 3.14
SQRT
Returns the square root.
SELECT SQRT(16); -- Returns 4.0
SELECT SQRT(2); -- Returns 1.4142...
POWER / POW
Raises a number to a power.
SELECT POWER(2, 3); -- Returns 8.0
SELECT POW(10, 2); -- Returns 100.0
MOD
Returns the remainder of division.
SELECT MOD(10, 3); -- Returns 1
SIGN
Returns the sign of a number (-1, 0, or 1).
SELECT SIGN(-15); -- Returns -1
SELECT SIGN(0); -- Returns 0
SELECT SIGN(42); -- Returns 1
EXP
Returns e raised to the specified power.
SELECT EXP(1); -- Returns 2.7183...
LN
Returns the natural logarithm.
SELECT LN(2.718281828); -- Returns ~1
LOG
Returns logarithm. With one argument, returns base-10 log. With two arguments, uses first as base.
SELECT LOG(10); -- Returns 1.0 (base 10)
SELECT LOG(10, 100); -- Returns 2.0 (log base 10 of 100)
LOG10
Returns the base-10 logarithm.
SELECT LOG10(100); -- Returns 2.0
SELECT LOG10(1000); -- Returns 3.0
LOG2
Returns the base-2 logarithm.
SELECT LOG2(8); -- Returns 3.0
PI
Returns the value of pi.
SELECT PI(); -- Returns 3.1416...
RANDOM
Returns a random number between 0 and 1.
SELECT RANDOM(); -- Returns random float 0-1
SELECT FLOOR(RANDOM() * 100); -- Random integer 0-99
SIN / COS / TAN
Trigonometric functions (input in radians).
SELECT SIN(0); -- Returns 0.0
SELECT COS(0); -- Returns 1.0
SELECT TAN(0); -- Returns 0.0
GREATEST
Returns the largest value from a list.
SELECT GREATEST(1, 5, 3); -- Returns 5
SELECT GREATEST(price, min_price) FROM products;
LEAST
Returns the smallest value from a list.
SELECT LEAST(1, 5, 3); -- Returns 1
SELECT LEAST(price, max_price) FROM products;
Date and Time Functions
NOW / CURRENT_TIMESTAMP
Returns the current date and time.
SELECT NOW(); -- Returns current timestamp
SELECT CURRENT_TIMESTAMP; -- Same as NOW()
CURRENT_DATE
Returns the current date (at midnight UTC).
SELECT CURRENT_DATE; -- Returns today's date
DATE_TRUNC
Truncates a timestamp to specified precision.
SELECT DATE_TRUNC('year', '2024-03-15 10:30:45'); -- '2024-01-01T00:00:00Z'
SELECT DATE_TRUNC('month', '2024-03-15 10:30:45'); -- '2024-03-01T00:00:00Z'
SELECT DATE_TRUNC('day', NOW()); -- Start of today
SELECT DATE_TRUNC('hour', NOW()); -- Current hour
Supported units: year, quarter, month, week, day, hour, minute, second
TIME_TRUNC
Truncates a timestamp to a duration interval.
SELECT TIME_TRUNC('15m', '2024-03-15 10:37:45'); -- '2024-03-15T10:30:00Z'
SELECT TIME_TRUNC('1h', '2024-03-15 10:37:45'); -- '2024-03-15T10:00:00Z'
Supported intervals: ns, us, ms, s, m, h (with numeric prefix, e.g., 15m, 4h)
EXTRACT
Extracts a field from a timestamp. Uses SQL standard syntax.
SELECT EXTRACT(YEAR FROM '2024-03-15'); -- 2024
SELECT EXTRACT(MONTH FROM '2024-03-15'); -- 3
SELECT EXTRACT(DAY FROM '2024-03-15'); -- 15
SELECT EXTRACT(HOUR FROM '2024-03-15 14:30:00'); -- 14
SELECT EXTRACT(MINUTE FROM '2024-03-15 14:30:00'); -- 30
SELECT EXTRACT(SECOND FROM '2024-03-15 14:30:45'); -- 45
SELECT EXTRACT(DOW FROM '2024-03-15'); -- 5 (Friday, 0=Sunday)
SELECT EXTRACT(DOY FROM '2024-03-15'); -- 75 (day of year)
SELECT EXTRACT(WEEK FROM '2024-03-15'); -- 11 (ISO week)
SELECT EXTRACT(QUARTER FROM '2024-05-15'); -- 2
Supported fields: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DOW (day of week), DOY (day of year), WEEK, QUARTER
YEAR / MONTH / DAY
Shorthand functions to extract date parts.
SELECT YEAR('2024-03-15'); -- Returns 2024
SELECT MONTH('2024-03-15'); -- Returns 3
SELECT DAY('2024-03-15'); -- Returns 15
HOUR / MINUTE / SECOND
Shorthand functions to extract time parts.
SELECT HOUR('2024-03-15 14:30:45'); -- Returns 14
SELECT MINUTE('2024-03-15 14:30:45'); -- Returns 30
SELECT SECOND('2024-03-15 14:30:45'); -- Returns 45
DATE_ADD
Adds an interval to a timestamp.
-- DATE_ADD(timestamp, amount [, unit])
SELECT DATE_ADD('2024-03-15', 10); -- Add 10 days (default)
SELECT DATE_ADD('2024-03-15', 2, 'month'); -- Add 2 months
Supported units: year, month, week, day, hour, minute, second
DATE_SUB
Subtracts an interval from a timestamp.
SELECT DATE_SUB('2024-03-15', 10); -- Subtract 10 days
SELECT DATE_SUB('2024-03-15', 1, 'month'); -- Subtract 1 month
DATEDIFF
Returns the difference between two dates in days.
SELECT DATEDIFF('2024-03-15', '2024-03-01'); -- Returns 14
TO_CHAR
Formats a timestamp as a string.
SELECT TO_CHAR('2024-03-15 14:30:45', 'YYYY-MM-DD'); -- '2024-03-15'
SELECT TO_CHAR('2024-03-15 14:30:45', 'DD MON YYYY'); -- '15 MAR 2024'
SELECT TO_CHAR('2024-03-15 14:30:45', 'HH24:MI:SS'); -- '14:30:45'
Format patterns:
YYYY- 4-digit yearYY- 2-digit yearMM- Month as 01-12MON- Abbreviated month (JAN, FEB, …)MONTH- Full month nameDD- Day of month (01-31)DY- Abbreviated day name (SUN, MON, …)DAY- Full day nameHH24- Hour (00-23)HHorHH12- Hour (01-12)MI- Minutes (00-59)SS- Seconds (00-59)
Conversion Functions
CAST
Converts a value from one type to another.
SELECT CAST('123' AS INTEGER); -- Returns 123
SELECT CAST(3.14 AS INTEGER); -- Returns 3
SELECT CAST(42 AS TEXT); -- Returns '42'
SELECT CAST('true' AS BOOLEAN); -- Returns true
SELECT CAST('2024-03-15' AS TIMESTAMP); -- Returns timestamp
Supported types: INTEGER/INT, FLOAT/REAL/DOUBLE, TEXT/STRING/VARCHAR, BOOLEAN/BOOL, TIMESTAMP/DATETIME/DATE, JSON
COALESCE
Returns the first non-NULL value.
SELECT COALESCE(NULL, NULL, 'default'); -- Returns 'default'
SELECT COALESCE(nickname, first_name, 'Anonymous') FROM users;
NULLIF
Returns NULL if two values are equal, otherwise returns the first value.
SELECT NULLIF(10, 10); -- Returns NULL
SELECT NULLIF(10, 20); -- Returns 10
IFNULL
Returns the second value if the first is NULL.
SELECT IFNULL(NULL, 'default'); -- Returns 'default'
SELECT IFNULL(nickname, 'No nickname') FROM users;
IIF
Inline conditional (if-then-else).
-- IIF(condition, true_value, false_value)
SELECT IIF(5 > 3, 'yes', 'no'); -- Returns 'yes'
SELECT IIF(quantity > 0, 'In Stock', 'Out of Stock') FROM products;
TYPEOF
Returns the data type name of a value.
SELECT TYPEOF(123); -- Returns 'INTEGER'
SELECT TYPEOF(3.14); -- Returns 'FLOAT'
SELECT TYPEOF('hello'); -- Returns 'TEXT'
SELECT TYPEOF(true); -- Returns 'BOOLEAN'
SELECT TYPEOF(NULL); -- Returns 'NULL'
JSON Functions
Stoolap provides comprehensive JSON support for storing and querying JSON data.
JSON_EXTRACT
Extracts a value from JSON using a path expression.
-- JSON_EXTRACT(json, path)
SELECT JSON_EXTRACT('{"name": "Alice", "age": 30}', '$.name'); -- 'Alice'
SELECT JSON_EXTRACT('{"user": {"email": "a@b.com"}}', '$.user.email');
SELECT JSON_EXTRACT('{"items": [1, 2, 3]}', '$.items[0]'); -- 1
JSON_TYPE / JSON_TYPEOF
Returns the type of a JSON value.
SELECT JSON_TYPE('{"a": 1}'); -- 'object'
SELECT JSON_TYPE('[1, 2, 3]'); -- 'array'
SELECT JSON_TYPEOF('"hello"'); -- 'string'
SELECT JSON_TYPEOF('123'); -- 'number'
SELECT JSON_TYPEOF('true'); -- 'boolean'
SELECT JSON_TYPEOF('null'); -- 'null'
JSON_VALID
Checks if a string is valid JSON.
SELECT JSON_VALID('{"a": 1}'); -- Returns 1 (true)
SELECT JSON_VALID('not json'); -- Returns 0 (false)
JSON_KEYS
Returns the keys of a JSON object as a JSON array.
SELECT JSON_KEYS('{"a": 1, "b": 2, "c": 3}'); -- '["a","b","c"]'
JSON_ARRAY_LENGTH
Returns the number of elements in a JSON array.
SELECT JSON_ARRAY_LENGTH('[1, 2, 3, 4, 5]'); -- 5
JSON_ARRAY
Creates a JSON array from values.
SELECT JSON_ARRAY(1, 2, 3); -- '[1,2,3]'
SELECT JSON_ARRAY('a', 'b', 'c'); -- '["a","b","c"]'
JSON_OBJECT
Creates a JSON object from key-value pairs.
SELECT JSON_OBJECT('name', 'Alice', 'age', 30); -- '{"age":30,"name":"Alice"}'
Utility Functions
VERSION
Returns the Stoolap version string.
SELECT VERSION(); -- Returns version info
SLEEP
Pauses execution for a specified number of seconds.
SELECT SLEEP(1); -- Pauses for 1 second, returns 0
SELECT SLEEP(0.5); -- Pauses for 500ms
Example Queries
Data Cleaning
SELECT
id,
TRIM(UPPER(name)) AS clean_name,
COALESCE(email, 'no-email@example.com') AS email,
IFNULL(phone, 'N/A') AS phone
FROM customers;
Date Analysis
SELECT
DATE_TRUNC('month', order_date) AS month,
COUNT(*) AS orders,
ROUND(SUM(total), 2) AS revenue
FROM orders
WHERE order_date >= DATE_SUB(NOW(), 12, 'month')
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;
JSON Processing
SELECT
id,
JSON_EXTRACT(metadata, '$.category') AS category,
JSON_EXTRACT(metadata, '$.tags[0]') AS first_tag,
JSON_ARRAY_LENGTH(metadata, '$.tags') AS tag_count
FROM products
WHERE JSON_VALID(metadata) = 1;
Conditional Logic
SELECT
product_name,
price,
IIF(quantity > 0, 'In Stock', 'Out of Stock') AS availability,
GREATEST(price * 0.9, min_price) AS sale_price
FROM products;
Performance Notes
- Scalar functions execute for each row; consider filtering first to reduce row count
- Using functions in WHERE clauses may prevent index usage
- JSON functions parse JSON on each call; consider storing frequently accessed values in regular columns