Varchar Functions

`chr()`

Signature:


chr(n: BIGINT) -> VARCHAR

Returns the Unicode code point n as a single character varchar.

Example:


RETURN chr(65) AS character;

`concat()`

Signature:


concat(varchar1: VARCHAR, ..., varcharN: VARCHAR) -> VARCHAR

Returns the concatenation of varchar1, varchar2, …, varcharN. This function provides the same functionality as the SQL-standard concatenation operator (||).

Example:


RETURN concat('Hello', ' ', 'World');

`ends_with()`

Signature:


ends_with(varchar: VARCHAR, subvarchar: VARCHAR) -> VARCHAR

Returns whether varchar ends with subvarchar.

Example:


RETURN ends_with('Hello World', 'World');

`hamming_distance()`

Signature:


hamming_distance(varchar1: VARCHAR, varchar2: VARCHAR) -> BIGINT

Returns the Hamming distance of varchar1 and varchar2, i.e. the number of positions at which the corresponding characters are different. Note that the two varchars must have the same length.

Example:


RETURN hamming_distance('karolin', 'kathrin');

`length()`

Signature:


length(varchar: VARCHAR) -> BIGINT

Returns the length of varchar in characters.

Example:


RETURN length('Hello World');

`levenshtein_distance()`

Signature:


levenshtein_distance(varchar_1: VARCHAR, varchar_2: VARCHAR) -> BIGINT

Returns the Levenshtein edit distance of varchar_1 and varchar_2, i.e. the minimum number of single-character edits (insertions, deletions, or substitutions) needed to convert varchar_1 to varchar_2.

Example:


RETURN levenshtein_distance('kitten', 'sitting');

`lower()`

Signature:


lower(varchar: VARCHAR) -> VARCHAR

Converts varchar to lowercase.

Example:


RETURN lower('Hello World');

`lpad()`

Signature:


lpad(varchar: VARCHAR, size: BIGINT, padvarchar: VARCHAR) -> VARCHAR

Left pads varchar to size characters with padvarchar. If size is less than the length of varchar, the result is truncated to size characters. size must not be negative and padvarchar must be non-empty.

Example:


RETURN lpad('Hello', 10, '*');

`ltrim()`

Signature:


ltrim(varchar: VARCHAR) -> VARCHAR

Removes leading whitespace from varchar. See trim() for the set of recognized whitespace characters.


ltrim(varchar: VARCHAR, chars: VARCHAR) -> VARCHAR

Removes the longest subvarchar containing only characters in chars from the beginning of the varchar.

Example:


RETURN ltrim('   Hello World');
RETURN ltrim('***Hello World', '*');

`replace()`

Signature:


replace(varchar: VARCHAR, search: VARCHAR) -> VARCHAR

Removes all instances of search from varchar.


replace(varchar: VARCHAR, search: VARCHAR, replace: VARCHAR) -> VARCHAR

Replaces all instances of search with replace in varchar. If search is an empty varchar, inserts replace in front of every character and at the end of the varchar.

Example:


RETURN replace('Hello World', 'o', '');
RETURN replace('Hello World', 'o', 'a');
RETURN replace('Hello', '', '-');

`reverse()`

Signature:


reverse(varchar: VARCHAR) -> VARCHAR

Returns the input varchar with characters in reverse order.

Example:


RETURN reverse('Hello World');

`rpad()`

Signature:


rpad(varchar: VARCHAR, size: BIGINT, padvarchar: VARCHAR) -> VARCHAR

Right pads varchar to size characters with padvarchar. If size is less than the length of varchar, the result is truncated to size characters. size must not be negative and padvarchar must be non-empty.

Example:


RETURN rpad('Hello', 10, '*');

`rtrim()`

Signature:


rtrim(varchar: VARCHAR) -> VARCHAR

Removes trailing whitespace from varchar. See trim() for the set of recognized whitespace characters.


rtrim(varchar: VARCHAR, chars: VARCHAR) -> VARCHAR

Removes the longest subvarchar containing only characters in chars from the end of the varchar.

Example:


RETURN rtrim('Hello World   ');
RETURN rtrim('Hello World***', '*');

`split()`

Signature:


split(varchar: VARCHAR, delimiter: VARCHAR) -> VARCHAR

Splits varchar on delimiter and returns an array.


split(varchar: VARCHAR, delimiter: VARCHAR, limit: BIGINT) -> VARCHAR

Splits varchar on delimiter and returns an array of size at most limit. The last element in the array always contains everything left in the varchar. limit must be a positive number.

Example:


RETURN split('Hello,World,Example', ',');
RETURN split('Hello,World,Example,Test', ',', 3);

`split_part()`

Signature:


split_part(varchar: VARCHAR, delimiter: VARCHAR, index: BIGINT) -> VARCHAR

Splits varchar on delimiter and returns the part at index. Field indexes start with 1. If the index is larger than the number of fields, then null is returned.

Example:


RETURN split_part('Hello,World,Example', ',', 2);
RETURN split_part('Hello,World,Example', ',', 5);

`starts_with()`

Signature:


starts_with(varchar: VARCHAR, subvarchar: VARCHAR) -> BOOLEAN

Returns whether varchar starts with subvarchar.

Example:


RETURN starts_with('Hello World', 'Hello');

`strpos()`

Signature:


strpos(varchar: VARCHAR, subvarchar: VARCHAR) -> BIGINT

Returns the starting position of the first instance of subvarchar in varchar. Positions start with 1. If not found, 0 is returned.


strpos(varchar: VARCHAR, subvarchar: VARCHAR, instance: BIGINT) -> BIGINT

Returns the position of the N-th instance of substring in string. instance must be a positive number. Positions start with 1. If not found, 0 is returned. It takes into account overlapping strings when counting occurrences.

Example:


RETURN strpos('Hello World', 'World');
RETURN strpos('ababab', 'ab', 2);

`strrpos()`

Signature:


strrpos(varchar: VARCHAR, subvarchar: VARCHAR) -> BIGINT

Returns the starting position of the last instance of subvarchar in varchar. Positions start with 1. If not found, 0 is returned.


strrpos(varchar: VARCHAR, subvarchar: VARCHAR, instance: BIGINT) -> BIGINT

Returns the position of the N-th instance of subvarchar in varchar starting from the end of the varchar. instance must be a positive number. Positions start with 1. If not found, 0 is returned. It takes into account overlapping varchars when counting occurrences.

Example:


RETURN strrpos('Hello World World', 'World');
RETURN strrpos('Hello World', 'world');

`substr()`

Signature:


substr(varchar: VARCHAR, start: BIGINT) -> VARCHAR

Returns the rest of varchar from the starting position start. Positions start with 1. A negative starting position is interpreted as being relative to the end of the varchar. Returns an empty varchar if the absolute value of start is greater than the length of the varchar.


substr(varchar: VARCHAR, start: BIGINT, length: BIGINT) -> VARCHAR

Returns a subvarchar from varchar of length length from the starting position start. Positions start with 1. A negative starting position is interpreted as being relative to the end of the varchar. Returns an empty varchar if the absolute value of start is greater than the length of the varchar.

Example:


RETURN substr('Hello World', 7);
RETURN substr('Hello World', 7, 5);

`trim()`

Signature:


trim(varchar: VARCHAR) -> VARCHAR

Removes starting and ending whitespaces from varchar.


trim(varchar: VARCHAR, chars: VARCHAR) -> VARCHAR

Removes the longest subvarchar containing only characters in chars from the beginning and end of the varchar.

Recognized Whitespace Characters:

9: TAB (horizontal tab)
10: LF (NL line feed, new line)
11: VT (vertical tab)
12: FF (NP form feed, new page)
13: CR (carriage return)
28: FS (file separator)
29: GS (group separator)
30: RS (record separator)
31: US (unit separator)
32: Space
U+1680: Ogham Space Mark
U+2000: En Quad
U+2001: Em Quad
U+2002: En Space
U+2003: Em Space
U+2004: Three-Per-Em Space
U+2005: Four-Per-Em Space
U+2006: Six-Per-Em Space
U+2008: Punctuation Space
U+2009: Thin Space
U+200a: Hair Space
U+2028: Line Separator
U+2029: Paragraph Separator
U+205f: Medium Mathematical Space
U+3000: Ideographic Space

Example:


RETURN trim('  Hello World  ');
RETURN trim('---Hello World---', '-');

`upper()`

Signature:


upper(varchar: VARCHAR) -> VARCHAR

Converts varchar to uppercase.

Example:


RETURN upper('Hello World');

`word_stem()`

Signature:


word_stem(word: VARCHAR) -> VARCHAR

Returns the stem of word in the English language. If the word is not an English word, the word in lowercase is returned.


word_stem(word: VARCHAR, lang: VARCHAR) -> VARCHAR

Returns the stem of word in the lang language. This function supports the following languages:

lang	Language
ca	Catalan
da	Danish
de	German
en	English
es	Spanish
eu	Basque
fi	Finnish
fr	French
hu	Hungarian
hy	Armenian
ir	Irish
it	Italian
lt	Lithuanian
nl	Dutch
no	Norwegian
pt	Portuguese
ro	Romanian
ru	Russian
sv	Swedish
tr	Turkish

Example:


RETURN word_stem('running');
RETURN word_stem('running', 'en');