String Functions

Unless specified otherwise, all functions return NULL if at least one of the arguments is NULL.

ascii(string) → integer

Returns the numeric value of the first character of string.

chr(n) → varchar

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

contains(left, right) → boolean

Returns true if ‘right’ is found in ‘left’. Otherwise, returns false.

SELECT contains('Spark SQL', 'Spark'); -- true
SELECT contains('Spark SQL', 'SPARK'); -- false
SELECT contains('Spark SQL', null); -- NULL
SELECT contains(x'537061726b2053514c', x'537061726b'); -- true
endswith(left, right) → boolean

Returns true if ‘left’ ends with ‘right’. Otherwise, returns false.

SELECT endswith('js SQL', 'SQL'); -- true
SELECT endswith('js SQL', 'js'); -- false
SELECT endswith('js SQL', NULL); -- NULL
instr(string, substring) → integer

Returns the starting position of the first instance of substring in string. Positions start with 1. Returns 0 if ‘substring’ is not found.

length(string) → integer

Returns the length of string in characters.

lower(string) → string

Returns string with all characters changed to lowercase.

SELECT lower('SparkSql'); -- sparksql
ltrim(string) → varchar

Removes leading 0x20(space) characters from string.

SELECT ltrim('  data  '); -- "data  "
ltrim(trimCharacters, string) → varchar

Removes specified leading characters from string. The specified character is any character contained in trimCharacters. trimCharacters can be empty and may contain duplicate characters.

SELECT ltrim('ps', 'spark'); -- "ark"
replace(string, search, replace) → string

Replaces all occurrences of search with replace.

SELECT replace('ABCabc', 'abc', 'DEF'); -- ABCDEF
rtrim(string) → varchar

Removes trailing 0x20(space) characters from string.

SELECT rtrim('  data  '); -- "  data"
rtrim(trimCharacters, string) → varchar

Removes specified trailing characters from string. The specified character is any character contained in trimCharacters. trimCharacters can be empty and may contain duplicate characters.

SELECT rtrim('kr', 'spark'); -- "spa"
split(string, delimiter)

Splits string on delimiter and returns an array.

SELECT split('oneAtwoBthreeC', '[ABC]'); -- ["one","two","three",""]
SELECT split('one', ''); -- ["o", "n", "e", ""]
SELECT split('one', '1'); -- ["one"]
split(string, delimiter, limit)

Splits string on delimiter and returns an array of size at most limit.

SELECT split('oneAtwoBthreeC', '[ABC]', -1); -- ["one","two","three",""]
SELECT split('oneAtwoBthreeC', '[ABC]', 0); -- ["one", "two", "three", ""]
SELECT split('oneAtwoBthreeC', '[ABC]', 2); -- ["one","twoBthreeC"]
startswith(left, right) → boolean

Returns true if ‘left’ starts with ‘right’. Otherwise, returns false.

SELECT startswith('js SQL', 'js'); -- true
SELECT startswith('js SQL', 'SQL'); -- false
SELECT startswith('js SQL', null); -- NULL
substring(string, start) → varchar

Returns the rest of string from the starting position start. Positions start with 1. A negative starting position is interpreted as being relative to the end of the string. Type of ‘start’ must be an INTEGER.

substring(string, start, length) → varchar

Returns a substring from string 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 string. Type of ‘start’ must be an INTEGER.

SELECT substring('Spark SQL', 5, 1); -- k
SELECT substring('Spark SQL', 5, 0); -- ""
SELECT substring('Spark SQL', 5, -1); -- ""
SELECT substring('Spark SQL', 5, 10000); -- "k SQL"
trim(string) → varchar

Removes leading and trailing 0x20(space) characters from string.

SELECT trim('  data  '); -- "data"
trim(trimCharacters, string) → varchar

Removes specified leading and trailing characters from string. The specified character is any character contained in trimCharacters. trimCharacters can be empty and may contain duplicate characters.

SELECT trim('sprk', 'spark'); -- "a"
upper(string) → string

Returns string with all characters changed to uppercase.

SELECT upper('SparkSql'); -- SPARKSQL