String Functions
Concat
Concatinates two or more string values together.
This function tries and convert non string values into the string type, example:
| Input | Type | Output |
|---|---|---|
| 'hello' | String | 'hello' |
| 13 | Int | '13' |
| 13.4 | Float | '13.4' |
| true | Bool | 'true' |
If any argument is null, the return value will always be null.
SQL Usage
SELECT c1 || ' hello ' || c2 FROM ...
Lower
Returns the input string in all lowercase characters. If any other type than string is entered, the function will return 'null'.
SQL Usage
SELECT lower(c1) FROM ...
Upper
Returns the input string in all uppercase characters. If any other type than string is entered, the function will return 'null'.
SQL Usage
SELECT upper(c1) FROM ...
Trim
Remove whitespaces from both sides of a string
SQL Usage
SELECT trim(c1) FROM ...
LTrim
Remove whitespaces from the start of a string
SQL Usage
SELECT ltrim(c1) FROM ...
RTrim
Remove whitespaces from the end of a string
SQL Usage
SELECT rtrim(c1) FROM ...
To String
No substrait definition exists for this function
Converts different types to a string type.
Example output:
| Input | Type | Output |
|---|---|---|
| 'hello' | String | 'hello' |
| 13 | Int | '13' |
| 13.4 | Float | '13.4' |
| true | Bool | 'true' |
SQL Usage
SELECT to_string(c1) FROM ...
Starts with
Returns true or false if a string starts with another string. If the data type of either argument is not string, false will be returned.
SQL Usage
SELECT starts_with(c1, 'text') FROM ...
Substring
Returns a substring where the first argument is the input. The second argument is the start index, and an optional third argument is the length of the substring.
SQL Usage
SELECT substring(c1, 1) FROM ...
SELECT substring(c1, 1, 5) FROM ...
Like
Implements sql like expression. This follows SQL Servers implementation of like: SQL Server Like.
SQL Usage
... WHERE c1 LIKE '%te' -- starts with
... WHERE c1 LIKE 'te%' -- ends with
... WHERE c1 LIKE '%te%' -- contains
... WHERE c1 LIKE '_te' -- any character
... WHERE c1 LIKE '!_te' ESCAPE '!' -- set escape character, _ is escaped.
... WHERE c1 LIKE '[ab]te' -- match character a or b.
Replace
Replaces all occurences of the substring defined in the second variable with the value defined in the third argument for the string in the first argument.
SQL Usage
--- Replaces the word 'hello' with the word 'world' in the string c1.
SELECT replace(c1, 'hello', 'world') FROM ...
String Base64 Encode
Accepts a string as a parameter and will return the base64 encoding as a string.
SQL Usage
SELECT string_base64_encode(c1) FROM ...
String Base64 Decode
Accepts a string in base64 encoding and will return a decoded string.
SQL Usage
SELECT string_base64_decode(c1) FROM ...
Char Length
Returns the number of characters in a string, if the input is not a string or null, a null value is returned.
SQL Usage
SELECT LEN(c1) FROM ...
Strpos
Finds the index of a substring in another string. This function follows the substrait implementation and returns index 1 for the first character.
SQL Usage
SELECT strpos(c1, 'abc') FROM ...