¶Functions

ElyraSQL supports a broad set of MySQL-compatible scalar functions in expressions (SELECT list, WHERE, DEFAULT, generated columns, etc.).

¶Date & time

The niladic forms work with or without parentheses.

¶Extracting and formatting

¶Date arithmetic

DATE_ADD('2024-01-31', INTERVAL 1 MONTH)   -- 2024-02-29 (day clamped)
DATE_SUB('2024-03-15', INTERVAL 10 DAY)
ADDDATE(d, 7)                              -- numeric day form
TIMESTAMPADD(HOUR, 5, dt)

-- INTERVAL also works as an operator
SELECT * FROM events WHERE ts > NOW() - INTERVAL 7 DAY;
SELECT DATE '2024-01-31' + INTERVAL 1 MONTH;

Units: MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR. INTERVAL is supported both inside DATE_ADD/DATE_SUB/ TIMESTAMPADD and as a bare d + INTERVAL n UNIT / d - INTERVAL n UNIT operator.

¶String

CONCAT, CONCAT_WS, UPPER/UCASE, LOWER/LCASE, LENGTH/CHAR_LENGTH, OCTET_LENGTH, SUBSTRING/SUBSTR/MID, SUBSTRING_INDEX, LEFT, RIGHT, TRIM/LTRIM/RTRIM (incl. TRIM(LEADING/TRAILING 'x' FROM s)), REPLACE, REVERSE, REPEAT, SPACE, LPAD, RPAD, INSTR, LOCATE/POSITION, ASCII, ORD, FIELD, ELT, FIND_IN_SET, CHAR, INSERT, STRCMP, BIN, OCT, CONV, HEX, CRC32.

LENGTH returns the byte length and CHAR_LENGTH the character count; SUBSTRING positions are 1-based (position 0 yields the empty string).

REPEAT, SPACE, LPAD and RPAD return NULL when the result would exceed ELYRASQL_MAX_ALLOWED_PACKET (default 64 MiB) — the same behaviour as MySQL past max_allowed_packet, so SPACE(10000000000) is NULL rather than a 10 GB allocation.

Pattern matching: str LIKE pattern, and str REGEXP pattern / str RLIKE pattern (POSIX-style regular expressions, with NOT REGEXP). Compiled patterns are cached, so a constant pattern is compiled once per server rather than once per row. Case-sensitivity follows the operand's collation, as in MySQL: matching is case-insensitive under the default collation ('Hello' REGEXP 'h' is true) and case-sensitive for a _bin operand, while an inline (?-i) in the pattern overrides it. REGEXP_REPLACE and REGEXP_SUBSTR receive already-evaluated values, so they always use the default (case-insensitive) behaviour.

¶Math

ABS, CEIL/CEILING, FLOOR, ROUND(x[,d]), TRUNCATE(x,d), SIGN, SQRT, EXP, LN/LOG, LOG10, LOG2, POWER/POW, MOD, PI(), RAND(), GREATEST, LEAST, BIT_COUNT. A math domain error (e.g. SQRT(-1), LN(0)) returns NULL, and out-of-range DOUBLE results are NULL, as in MySQL.

¶Bitwise operators

a & b (AND), a | b (OR), a ^ b (XOR), a << b, a >> b, and unary ~a operate on 64-bit unsigned integers and return BIGINT UNSIGNED, matching MySQL. a DIV b is integer division (truncating toward zero; DIV 0 is NULL), and !x is the logical-NOT prefix. Example flag mask: WHERE flags & 4 > 0.

¶Conditional & null

COALESCE, IFNULL/NVL, NULLIF, ISNULL, IF(cond, a, b), and CASE expressions (both simple and searched). NULL propagates through arithmetic and follows three-valued logic in AND/OR/IN/BETWEEN (e.g. NULL AND 1 and 1 IN (NULL, 2) are NULL), as in MySQL.

¶Other

• UUID() — a random version-4 UUID string.
• CAST(x AS <type>) / CONVERT — to CHAR/text, SIGNED/integer, DECIMAL(p,s) (exact, rescaled), BINARY/bytes, DATE, DATETIME, TIME.

Decimal arithmetic (+, -, *) and SUM(DECIMAL) are computed exactly.

¶JSON

See Data types for JSON_EXTRACT, JSON_SET, JSON_ARRAY, JSON_OBJECT, and the rest of the JSON family.

¶Vector

See Vector search for VEC_DISTANCE, VEC_COSINE_DISTANCE, and VEC_INNER_PRODUCT.