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
instring
. Positions start with1
. 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 intrimCharacters
.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 intrimCharacters
.trimCharacters
can be empty and may contain duplicate characters.SELECT rtrim('kr', 'spark'); -- "spa"
- split(string, delimiter)¶
Splits
string
ondelimiter
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
ondelimiter
and returns an array of size at mostlimit
.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 positionstart
. Positions start with1
. 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 lengthlength
from the starting positionstart
. Positions start with1
. 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 intrimCharacters
.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