Scalar Functions
Scalar functions return a single value for every call. VERA Engine 4.3 expands the built-in SQL function library with new scalar functions for string processing, JSON handling, regular expressions, and arithmetic operations.
These functions are available for both Managed Service and BYOC deployments.
String Functions
The following string functions are available in VERA Engine 4.3.
PRINTF
Returns a formatted string from a printf-style format string.
Syntax
PRINTF(format[, obj]*)
Parameters
format: The format string.obj: Optional objects to format.
Return Type
STRING. Returns NULL if format is NULL.
Example
SELECT PRINTF('%s %d', 'aa', 2);
-- Returns: "aa 2"
TRANSLATE
Replaces all characters in fromStr with the corresponding characters in toStr within the expression.
Syntax
TRANSLATE(expr, fromStr, toStr)
Parameters
expr: The expression to translate.fromStr: The set of characters to replace.toStr: The set of characters to replace them with.
Return Type
STRING. Returns NULL if any argument is NULL.
Example
SELECT TRANSLATE('aabbcc', 'a', '1');
-- Returns: "11bbcc"
ELT
Returns the expression at the specified index.
Syntax
ELT(index, expr[, exprs]*)
Parameters
index: The 1-based index of the expression to return. Must be an integer between 1 and the number of expressions.expr: The expressions to choose from.
Return Type
STRING. Returns NULL if index is NULL or out of range.
Example
SELECT ELT(2, 'a', 'b', 'c');
-- Returns: "b"
BTRIM
Removes leading and trailing characters specified in trimStr from str.
Syntax
BTRIM(str[, trimStr])
Parameters
str: The string to trim.trimStr: The characters to remove. Defaults to whitespace if not specified.
Return Type
STRING. Returns NULL if any argument is NULL.
Example
SELECT BTRIM(' www.ververica.com ');
-- Returns: "www.ververica.com"
SELECT BTRIM('/www.ververica.com/', '/');
-- Returns: "www.ververica.com"
STARTSWITH
Returns whether expr starts with startExpr.
Syntax
STARTSWITH(expr, startExpr)
Parameters
expr: The expression to check.startExpr: The prefix to look for.
Return Type
BOOLEAN. Returns true if startExpr is empty. Returns NULL if any argument is NULL.
Example
SELECT STARTSWITH('headtail', 'head');
-- Returns: true
ENDSWITH
Returns whether expr ends with endExpr.
Syntax
ENDSWITH(expr, endExpr)
Parameters
expr: The expression to check.endExpr: The suffix to look for.
Return Type
BOOLEAN. Returns true if endExpr is empty. Returns NULL if any argument is NULL.
Example
SELECT ENDSWITH('headtail', 'tail');
-- Returns: true
JSON Functions
The following JSON functions are available in VERA Engine 4.3.
JSON_QUOTE
Quotes a string as a JSON value by wrapping it with double quotes and escaping special characters.
Syntax
JSON_QUOTE(string)
Return Type
STRING. Returns NULL if the argument is NULL.
Example
SELECT JSON_QUOTE('word');
-- Returns: "\"word\""
JSON_UNQUOTE
Unquotes a JSON value and unescapes special characters.
Syntax
JSON_UNQUOTE(string)
Return Type
STRING. Returns NULL if the argument is NULL. If the value is not a valid JSON string literal, it is returned unmodified.
Example
SELECT JSON_UNQUOTE('"word"');
-- Returns: "word"
Regular Expression Functions
The following regular expression functions are available in VERA Engine 4.3.
REGEXP_SUBSTR
Returns the first substring in str that matches the regex.
Syntax
REGEXP_SUBSTR(str, regex)
Return Type
STRING. Returns NULL if any argument is NULL, the regex is invalid, or no match is found.
Example
SELECT REGEXP_SUBSTR('abc-123-def', '[0-9]+');
-- Returns: "123"
REGEXP_INSTR
Returns the 1-based position of the first substring in str that matches the regex.
Syntax
REGEXP_INSTR(str, regex)
Return Type
INTEGER. Returns 0 if there is no match. Returns NULL if any argument is NULL or the regex is invalid.
Example
SELECT REGEXP_INSTR('abc-123-def', '[0-9]+');
-- Returns: 5
REGEXP_COUNT
Returns the number of times str matches the regex pattern.
Syntax
REGEXP_COUNT(str, regex)
Parameters
regex: A Java-style regular expression.
Return Type
INTEGER. Returns NULL if any argument is NULL or the regex is invalid.
Example
SELECT REGEXP_COUNT('abc-123-def-456', '[0-9]+');
-- Returns: 2
REGEXP_EXTRACT_ALL
Extracts all substrings in str that match the regex and correspond to the specified extractIndex.
Syntax
REGEXP_EXTRACT_ALL(str, regex[, extractIndex])
Parameters
regex: A regular expression that may contain multiple groups.extractIndex: The index of the regex group to extract (starts from 1). A value of 0 matches the entire regular expression. Defaults to 1.
Return Type
ARRAY<STRING>. Returns NULL if any argument is NULL or invalid.
Example
SELECT REGEXP_EXTRACT_ALL('abc-123-def-456', '([0-9]+)');
-- Returns: ["123", "456"]
Arithmetic Functions
The following arithmetic functions are available in VERA Engine 4.3.
UNHEX
Converts a hexadecimal string to BINARY.
Syntax
UNHEX(expr)
Parameters
expr: A hexadecimal string. If the length is odd, the first character is discarded and the result is left-padded with a null byte.
Return Type
BINARY. Returns NULL if expr is NULL or contains non-hexadecimal characters.
Example
SELECT DECODE(UNHEX('466C696E6B'), 'UTF-8');
-- Returns: "Flink"
Example
You can validate multiple functions in a single SELECT statement. The following example uses a SQL trick (FROM (VALUES(ROW(1)))) to fill the mandatory FROM part of the statement when no table is present.
SELECT
CAST(PRINTF('%s %d', 'aa', 1 + 1) AS STRING), -- aa2
CAST(TRANSLATE('aabbcc', 'a', '1') AS STRING), -- 11bbcc
CAST(ELT(2, 'a', 'b', 'c') AS STRING), -- b
CAST(BTRIM(' a ') AS STRING), -- a
CAST(BTRIM('bab', 'b') AS STRING), -- a
CAST(STARTSWITH('headtail', 'head') AS STRING), -- true
CAST(ENDSWITH('headtail', 'tail') AS STRING), -- true
CAST(JSON_QUOTE('word') AS STRING), -- "word"
CAST(JSON_UNQUOTE('"word"') AS STRING), -- word
CAST(REGEXP_SUBSTR('abc-123-def', '[0-9]+') AS STRING), -- 123
CAST(REGEXP_INSTR('abc-123-def', '[0-9]+') AS STRING), -- 5
CAST(REGEXP_COUNT('abc-123-def-456', '[0-9]+') AS STRING), -- 2
CAST(REGEXP_EXTRACT_ALL('abc-123-def-456', '([0-9]+)') AS STRING), -- ['123', '456']
CAST(UNHEX('48656C6C6F') AS STRING) -- Hello
FROM (VALUES(ROW(1)));