Built-in functions for Lindorm SQL (Spark SQL dialect), covering type conversion, date and time, string manipulation, and encoding/decoding.
Type conversion functions
| Function | Description | Notes |
|---|---|---|
| CAST | Converts a value to a specified data type. Returns NULL on failure. | |
| TRY_CAST | Converts a value to a specified data type. Returns NULL on failure without raising an error. | Safe version of CAST |
| COALESCE | Returns the first non-NULL value in a list of expressions. | Uses short-circuit evaluation |
Date and time functions
| Function | Description |
|---|---|
| CURRENT_DATE | Returns the current date. |
| DATE_FORMAT | Formats a date or timestamp value as a string. |
| DATE_ADD | Adds a number of days to a date. |
| DATEDIFF | Returns the number of days between two dates. |
| UNIX_TIMESTAMP | Converts a date or timestamp to a Unix timestamp in seconds. |
| UNIX_MILLIS | Converts a timestamp to a Unix timestamp in milliseconds. |
String functions
| Function | Description |
|---|---|
| CONCAT | Concatenates two or more strings. |
| SUBSTRING / SUBSTR | Extracts a substring starting at a given position. |
Encoding and decoding functions
| Function | Description | Notes |
|---|---|---|
| HEX | Converts a value to its hexadecimal string representation. | Accepts string, binary, and numeric types |
| UNHEX | Converts a hexadecimal string to binary data. | Inverse of HEX |
| BASE64 | Encodes binary data as a Base64 string. | |
| UNBASE64 | Decodes a Base64 string to binary data. | Inverse of BASE64 |
CAST
Converts an expression to a specified data type. If the conversion fails, CAST returns NULL.
See also: TRY_CAST — also returns NULL on failure, without raising an error.
Syntax
CAST(expression AS target_data_type)Parameters
Required:
expression : The value to convert. Accepts any data type.
target_data_type : The target data type (for example, INT, STRING, DATE).
Returns
The converted value in target_data_type. Returns NULL if the conversion fails or if the input is NULL.
Examples
Convert a string to an integer:
SELECT CAST('123' AS INT);123Convert a floating-point number to a string:
SELECT CAST(3.14 AS STRING);'3.14'TRY_CAST
Converts an expression to a specified data type. If the conversion fails, TRY_CAST returns NULL instead of raising an error.
See also: CAST — also returns NULL on failure.
Syntax
TRY_CAST(expression AS target_data_type)Parameters
Required:
expression : The value to convert. Accepts any data type.
target_data_type : The target data type.
Returns
The converted value in target_data_type. Returns NULL if the conversion fails or if the input is NULL.
Examples
Invalid conversion — returns NULL instead of an error:
SELECT TRY_CAST('abc' AS INT);NULLValid conversion:
SELECT TRY_CAST('2023-01-01' AS DATE);2023-01-01COALESCE
Returns the first non-NULL value in the argument list. If all arguments are NULL, returns NULL.
COALESCE uses short-circuit evaluation: it evaluates arguments from left to right and stops at the first non-NULL value. All arguments must share the same data type or have types that support implicit type conversion.
In Spark SQL, this function is often used to fill in missing values or select values from multiple columns based on priority.
Syntax
COALESCE(expr1, expr2, ..., exprN)Parameters
Required:
expr1, expr2, ..., exprN : At least two expressions to evaluate, separated by commas. Accepts any data type, provided all arguments are type-compatible.
Returns
The first non-NULL value among the arguments, or NULL if all arguments are NULL.
Example
SELECT COALESCE(NULL, NULL, 'Hello', 'World') AS result;'Hello'CURRENT_DATE
Returns the current date.
Syntax
CURRENT_DATE()Returns
The current date as a DATE value.
Example
SELECT CURRENT_DATE();2023-08-15DATE_FORMAT
Formats a date or timestamp value as a string using the specified format pattern.
Syntax
DATE_FORMAT(date_value, format_pattern)Parameters
Required:
date_value : The date or timestamp to format. Supported types: DATE, TIMESTAMP.
format_pattern : The output format string. Supported type: STRING.
Returns
A STRING representation of date_value in the given format.
Examples
Format as year-month-day:
SELECT DATE_FORMAT('2023-08-15', 'yyyy-MM-dd');'2023-08-15'Format as month/day/year:
SELECT DATE_FORMAT('2023-08-15', 'MM/dd/yyyy');'08/15/2023'DATE_ADD
Adds a number of days to a date and returns the resulting date.
Syntax
DATE_ADD(start_date, days)Parameters
Required:
start_date : The starting date. Supported types: DATE, TIMESTAMP.
days : The number of days to add. Supported type: INT.
Returns
The resulting DATE after adding days to start_date.
Examples
Add 10 days to a date:
SELECT DATE_ADD('2023-08-15', 10);2023-08-25Calculate an expiration date 30 days from today:
SELECT DATE_ADD(CURRENT_DATE(), 30);2023-09-14DATEDIFF
Returns the number of days between two dates, calculated as end_date minus start_date.
Syntax
DATEDIFF(end_date, start_date)Parameters
Required:
end_date : The end date. Supported types: DATE, TIMESTAMP.
start_date : The start date. Supported types: DATE, TIMESTAMP.
Returns
An INT representing the number of days between the two dates. Returns NULL if either argument is NULL.
Examples
Calculate the difference between two dates:
SELECT DATEDIFF('2023-08-20', '2023-08-15');5Estimate age in years:
SELECT DATEDIFF(CURRENT_DATE(), '1990-05-10') / 365;33UNIX_TIMESTAMP
Converts a date, timestamp, or string to a Unix timestamp in seconds. If no argument is provided, returns the current Unix timestamp.
See also: UNIX_MILLIS — returns milliseconds instead of seconds.
Syntax
UNIX_TIMESTAMP(date_expr)Parameters
Optional:
date_expr : The value to convert. Supported types: DATE, TIMESTAMP, STRING. If omitted, the function returns the current timestamp.
Returns
A LONG value representing the Unix timestamp in seconds. Returns NULL if the input is NULL.
Examples
Get the current Unix timestamp:
SELECT UNIX_TIMESTAMP();1692072000Convert a datetime string to a Unix timestamp:
SELECT UNIX_TIMESTAMP('2023-08-15 12:00:00');1692072000UNIX_MILLIS
Converts a timestamp to a Unix timestamp in milliseconds.
UNIX_MILLISaccepts DATE and TIMESTAMP types only. To convert a string, first cast it to TIMESTAMP:UNIX_MILLIS(CAST('2020-01-01 00:00:00' AS TIMESTAMP)).
See also: UNIX_TIMESTAMP — returns seconds instead of milliseconds.
Syntax
UNIX_MILLIS(date_expr)Parameters
Required:
date_expr : The timestamp to convert. Supported types: DATE, TIMESTAMP.
Returns
A LONG value representing the Unix timestamp in milliseconds.
Examples
Get the current Unix timestamp in milliseconds:
SELECT UNIX_MILLIS(CURRENT_TIMESTAMP());1712345678901Convert a string to a timestamp, then get the millisecond Unix timestamp:
SELECT UNIX_MILLIS(CAST('2020-01-01 00:00:00' AS TIMESTAMP));1577836800000CONCAT
Concatenates two or more strings into a single string.
Syntax
CONCAT(str1, str2, ..., strN)Parameters
Required:
str1, str2, ..., strN : At least two string expressions to concatenate, separated by commas. Supported type: STRING.
Returns
A single STRING containing all input strings joined in order.
Examples
Concatenate two strings:
SELECT CONCAT('Hello', ' World');'Hello World'Build a full name from separate columns:
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;'John Doe'SUBSTRING / SUBSTR
Extracts a substring from a string, starting at a given position. Both SUBSTRING and SUBSTR are equivalent.
Syntax
SUBSTRING(str, pos [, len])SUBSTR(str, pos [, len])Parameters
Required:
str : The source string. Supported type: STRING.
pos : The starting position. Positions are 1-indexed (the first character is at position 1). Supported type: INT.
Optional:
len : The number of characters to extract. If omitted, the function returns all characters from pos to the end of the string. Supported type: INT.
Returns
A STRING containing the extracted substring.
Examples
Extract from position 7 to end:
SELECT SUBSTRING('Spark SQL', 7);'SQL'Extract 5 characters starting at position 1:
SELECT SUBSTR('Spark SQL', 1, 5);'Spark'HEX
Converts a value to its hexadecimal string representation.
See also: UNHEX — converts a hexadecimal string back to binary data.
Syntax
HEX(input)Parameters
Required:
input : The value to convert. Supported types: STRING, BINARY, and all numeric types.
Returns
A STRING containing the hexadecimal representation of the input.
Examples
Convert a string to hexadecimal (each character maps to its ASCII hex code — for example, S=0x53, p=0x70, a=0x61, r=0x72, k=0x6B):
SELECT HEX('Spark');'537061726B'Convert an integer to hexadecimal:
SELECT HEX(12345);'3039'Convert binary data to hexadecimal:
SELECT HEX(BINARY('Spark'));'537061726B'UNHEX
Converts a hexadecimal string to binary data.
See also: HEX — converts a value to its hexadecimal string representation.
Syntax
UNHEX(hex_string)Parameters
Required:
hex_string : The hexadecimal string to convert. Supported type: STRING.
Returns
A BINARY value representing the decoded data.
Examples
Decode a hexadecimal string to binary:
SELECT UNHEX('537061726B');[83 112 97 114 107]Decode and cast to a string:
SELECT CAST(UNHEX('48656C6C6F') AS STRING);'Hello'BASE64
Encodes binary data as a Base64 string.
See also: UNBASE64 — decodes a Base64 string back to binary data.
Syntax
BASE64(binary_data)Parameters
Required:
binary_data : The binary data to encode. Supported type: BINARY.
Returns
A STRING containing the Base64-encoded representation of the input.
Examples
Encode binary data:
SELECT BASE64(BINARY('Spark'));'U3Bhcms='Cast a string to binary, then encode:
SELECT BASE64(CAST('Hello' AS BINARY));'SGVsbG8='UNBASE64
Decodes a Base64 string to binary data.
See also: BASE64 — encodes binary data as a Base64 string.
Syntax
UNBASE64(str)Parameters
Required:
str : The Base64 string to decode. Supported type: STRING.
Returns
A BINARY value containing the decoded data.
Examples
Decode a Base64 string to binary:
SELECT UNBASE64('U3Bhcms=');[83 112 97 114 107]Decode and cast to a string:
SELECT CAST(UNBASE64('SGVsbG8=') AS STRING);'Hello'