Scalar Functions

Applies toBYOC

4 min read

On this page

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

SQL

1PRINTF(format[, obj]*)

Parameters

format: The format string.
obj: Optional objects to format.

Return Type

STRING. Returns NULL if format is NULL.

Example

SQL

1SELECT PRINTF('%s %d', 'aa', 2);
2-- Returns: "aa 2"

TRANSLATE

Replaces all characters in fromStr with the corresponding characters in toStr within the expression.

Syntax

SQL

1TRANSLATE(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

SQL

1SELECT TRANSLATE('aabbcc', 'a', '1');
2-- Returns: "11bbcc"

ELT

Returns the expression at the specified index.

Syntax

SQL

1ELT(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

SQL

1SELECT ELT(2, 'a', 'b', 'c');
2-- Returns: "b"

BTRIM

Removes leading and trailing characters specified in trimStr from str.

Syntax

SQL

1BTRIM(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

SQL

1SELECT BTRIM('  www.ververica.com  ');
2-- Returns: "www.ververica.com"
3
4SELECT BTRIM('/www.ververica.com/', '/');
5-- Returns: "www.ververica.com"

STARTSWITH

Returns whether expr starts with startExpr.

Syntax

SQL

1STARTSWITH(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

SQL

1SELECT STARTSWITH('headtail', 'head');
2-- Returns: true

ENDSWITH

Returns whether expr ends with endExpr.

Syntax

SQL

1ENDSWITH(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

SQL

1SELECT ENDSWITH('headtail', 'tail');
2-- 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

SQL

1URL_ENCODE(url)

Parameters

url: The URL string to encode.

Return Type

VARCHAR.

Example

SQL

1SELECT URL_ENCODE('https://homenew.console.aliyun.com/home/dashboard/ProductAndService');
2-- 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

SQL

1URL_DECODE(url)

Parameters

url: The encoded URL string to decode.

Return Type

VARCHAR.

Example

SQL

1SELECT URL_DECODE('http%3A%2F%2Fwww.example.com%3A80%2Fproduct%2Fsls');
2-- 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.

SQL

1CREATE TEMPORARY TABLE `source` (
2  i INT,
3  d DOUBLE,
4  url STRING,
5  utf_url STRING
6) WITH (
7  'connector' = 'faker',
8  'rows-per-second' = '1',
9  'fields.i.expression' = '#{number.numberBetween ''1'',''1000''}',
10  'fields.d.expression' = '#{number.randomDouble ''2'',''1'',''10000''}',
11  'fields.url.expression' = '#{Internet.url}',
12  'fields.utf_url.expression' = 'http://example.com/#{regexify ''[\u4e00-\u9fa5]{3}''}'
13);
14
15SELECT 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

SQL

1JSON_QUOTE(string)

Return Type

STRING. Returns NULL if the argument is NULL.

Example

SQL

1SELECT JSON_QUOTE('word');
2-- Returns: "\"word\""

JSON_UNQUOTE

Unquotes a JSON value and unescapes special characters.

Syntax

SQL

1JSON_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

SQL

1SELECT JSON_UNQUOTE('"word"');
2-- 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

SQL

1REGEXP_SUBSTR(str, regex)

Return Type

STRING. Returns NULL if any argument is NULL, the regex is invalid, or no match is found.

Example

SQL

1SELECT REGEXP_SUBSTR('abc-123-def', '[0-9]+');
2-- Returns: "123"

REGEXP_INSTR

Returns the 1-based position of the first substring in str that matches the regex.

Syntax

SQL

1REGEXP_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

SQL

1SELECT REGEXP_INSTR('abc-123-def', '[0-9]+');
2-- Returns: 5

REGEXP_COUNT

Returns the number of times str matches the regex pattern.

Syntax

SQL

1REGEXP_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

SQL

1SELECT REGEXP_COUNT('abc-123-def-456', '[0-9]+');
2-- Returns: 2

REGEXP_EXTRACT_ALL

Extracts all substrings in str that match the regex and correspond to the specified extractIndex.

Syntax

SQL

1REGEXP_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

SQL

1SELECT REGEXP_EXTRACT_ALL('abc-123-def-456', '([0-9]+)');
2-- Returns: ["123", "456"]

Arithmetic Functions

The following arithmetic functions are available in VERA Engine 4.3.

UNHEX

Converts a hexadecimal string to BINARY.

Syntax

SQL

1UNHEX(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

SQL

1SELECT DECODE(UNHEX('466C696E6B'), 'UTF-8');
2-- 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.

SQL

1SELECT 
2  CAST(PRINTF('%s %d', 'aa', 1 + 1) AS STRING),                -- aa2
3  CAST(TRANSLATE('aabbcc', 'a', '1') AS STRING),               -- 11bbcc
4  CAST(ELT(2, 'a', 'b', 'c') AS STRING),                       -- b
5  CAST(BTRIM(' a ') AS STRING),                                -- a
6  CAST(BTRIM('bab', 'b') AS STRING),                           -- a
7  CAST(STARTSWITH('headtail', 'head') AS STRING),              -- true
8  CAST(ENDSWITH('headtail', 'tail') AS STRING),                -- true
9  CAST(JSON_QUOTE('word') AS STRING),                          -- "word"
10  CAST(JSON_UNQUOTE('"word"') AS STRING),                      -- word
11  CAST(REGEXP_SUBSTR('abc-123-def', '[0-9]+') AS STRING),      -- 123
12  CAST(REGEXP_INSTR('abc-123-def', '[0-9]+') AS STRING),      -- 5
13  CAST(REGEXP_COUNT('abc-123-def-456', '[0-9]+') AS STRING),   -- 2
14  CAST(REGEXP_EXTRACT_ALL('abc-123-def-456', '([0-9]+)') AS STRING), -- ['123', '456']
15  CAST(UNHEX('48656C6C6F') AS STRING),                         -- Hello
16  CAST(URL_ENCODE('https://example.com') AS STRING),           -- https%3A%2F%2Fexample.com
17  CAST(URL_DECODE('http%3A%2F%2Fexample.com') AS STRING)       -- http://example.com
18FROM (VALUES(ROW(1)));

Was this helpful?

Yes No