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. (URL functions are available in VERA Engine 4.0+).
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
URL Functions
The following URL functions are available in VERA Engine 4.0+.
URL_ENCODE
Encodes a URL string by converting special characters and Unicode text into percent-encoded format.
Syntax
URL_ENCODE(url)
Parameters
url: The URL string to encode.
Return Type
VARCHAR.
Example
SELECT URL_ENCODE('https://homenew.console.aliyun.com/home/dashboard/ProductAndService');
-- Returns: "https%3A%2F%2Fhomenew.console.aliyun.com%2Fhome%2Fdashboard%2FProductAndService"
URL_DECODE
Decodes a percent-encoded URL string back to its original, human-readable form.
Syntax
URL_DECODE(url)
Parameters
url: The encoded URL string to decode.
Return Type
VARCHAR.
Example
SELECT URL_DECODE('http%3A%2F%2Fwww.example.com%3A80%2Fproduct%2Fsls');
-- Returns: "http://www.example.com:80/product/sls"
Streaming Example
The following example demonstrates both functions using a faker connector source that generates random URLs, including URLs containing Unicode characters.
CREATE TEMPORARY TABLE `source` (
i INT,
d DOUBLE,
url STRING,
utf_url STRING
) WITH (
'connector' = 'faker',
'rows-per-second' = '1',
'fields.i.expression' = '#{number.numberBetween ''1'',''1000''}',
'fields.d.expression' = '#{number.randomDouble ''2'',''1'',''10000''}',
'fields.url.expression' = '#{Internet.url}',
'fields.utf_url.expression' = 'http://example.com/#{regexify ''[\u4e00-\u9fa5]{3}''}'
);
SELECT URL_ENCODE(url), URL_DECODE(utf_url) FROM `source`;
In this example:
URL_ENCODE(url)encodes randomly generated URLs, converting characters like:,/, and?into their percent-encoded equivalents.URL_DECODE(utf_url)decodes URLs that contain Unicode characters (in this case, simulated Chinese characters), returning the original readable text.
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
CAST(URL_ENCODE('https://example.com') AS STRING), -- https%3A%2F%2Fexample.com
CAST(URL_DECODE('http%3A%2F%2Fexample.com') AS STRING) -- http://example.com
FROM (VALUES(ROW(1)));