Scalar Functions
Oxibase 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
Oxibase 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"}'
Sequence Functions
Functions for interacting with explicitly defined sequences.
NEXTVAL(sequence_name)
Atomically increments the sequence and returns its next value. This operation bypasses MVCC boundaries to prevent locking and is not rolled back if the transaction aborts.
Example:
SELECT NEXTVAL('user_id_seq');
CURRVAL(sequence_name)
Returns the value most recently obtained by NEXTVAL for the specified sequence within the current session. It will throw an error if NEXTVAL has not yet been called in the current session.
Example:
SELECT CURRVAL('user_id_seq');
SETVAL(sequence_name, value, [is_called])
Sets the sequence’s current value. If is_called is true (the default), the next NEXTVAL advances the sequence before returning a value. If false, the next NEXTVAL returns the exact value given.
Example:
SELECT SETVAL('user_id_seq', 1000);
Utility Functions
VERSION
Returns the Oxibase 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