String Functions¶
Unless specified otherwise, all functions return NULL if at least one of the arguments is NULL.
- ascii(string) integer ¶
Returns unicode code point of the first character of
string
. Returns 0 ifstring
is empty.
- chr(n) varchar ¶
Returns the Unicode code point
n
as a single character string. Ifn < 0
, the result is an empty string. Ifn >= 256
, the result is equivalent to chr(n % 256
).
- 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.
- left(string, length) string ¶
Returns the leftmost length characters from the
string
. Iflength
is less or equal than 0 the result is an empty string.
- 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
- lpad(string, len, pad) string ¶
Returns
string
, left-padded with pad to a length oflen
. Ifstring
is longer thanlen
, the return value is shortened tolen
characters or bytes. Ifpad
is not specified,string
will be padded to the left with space characters if it is a character string, and with zeros if it is a byte sequence.SELECT lpad('hi', 5, '??'); -- ???hi SELECT lpad('hi', 1, '??'); -- h SELECT lpad('hi', 4); -- hi
- 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"
- overlay(input, replace, pos, len) same as input ¶
Replace a substring of
input
starting atpos
character withreplace
and going for restlen
characters ofinput
. Types ofinput
andreplace
must be the same. Supported types are: VARCHAR and VARBINARY. Wheninput
types are VARCHAR,len
andpos
are specified in characters, otherwise, bytes. Result is constructed from three parts. First part is first pos - 1 characters ofinput
whenpos
if greater then zero, otherwise, empty string. Second part isreplace
. Third part is rest ofinput
from indices pos + len which is 1-based, iflen
is negative, it will be set to size ofreplace
, if pos + len is negative, it refers to -(pos + len)th element before the end ofinput
.SELECT overlay('Spark SQL', '_', 6, -1); -- "Spark_SQL" SELECT overlay('Spark SQL', 'CORE', 7, -1); -- "Spark CORE" SELECT overlay('Spark SQL', 'ANSI ', 7, 0); -- "Spark ANSI SQL" SELECT overlay('Spark SQL', 'tructured', 2, 4); -- "Structured SQL" SELECT overlay('Spark SQL', '_', -6, 3); -- "_Sql"
- replace(string, search, replace) string ¶
Replaces all occurrences of search with replace.
SELECT replace('ABCabc', 'abc', 'DEF'); -- ABCDEF
- rpad(string, len, pad) string ¶
Returns
string
, right-padded withpad
to a length oflen
. Ifstring
is longer thanlen
, the return value is shortened tolen
characters. Ifpad
is not specified,string
will be padded to the right with space characters if it is a character string, and with zeros if it is a binary string.SELECT lpad('hi', 5, '??'); -- ???hi SELECT lpad('hi', 1, '??'); -- h SELECT lpad('hi', 4); -- hi
- 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. When the starting position is 0, the meaning is to refer to the first character.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. When the starting position is 0, the meaning is to refer to the first character. Type of ‘start’ must be an INTEGER.SELECT substring('Spark SQL', 0, 2); -- Sp SELECT substring('Spark SQL', 1, 2); -- Sp SELECT substring('Spark SQL', 5, 0); -- "" SELECT substring('Spark SQL', 5, -1); -- "" SELECT substring('Spark SQL', 5, 10000); -- "k SQL" SELECT substring('Spark SQL', -9, 3); -- "Spa" SELECT substring('Spark SQL', -10, 3); -- "Sp" SELECT substring('Spark SQL', -20, 3); -- ""
- translate(string, match, replace) varchar ¶
Returns a new translated string. It translates the character in
string
by a character inreplace
. The character inreplace
is corresponding to the character inmatch
. The translation will happen when any character instring
matching with a character inmatch
. Ifmatch's
character size is larger thanreplace's
, the extra characters inmatch
will be removed fromstring
. In addition, this function only considers the first occurrence of a character inmatch
and uses its corresponding character inreplace
for translation.SELECT translate('spark', 'sa', '12'); -- "1p2rk" SELECT translate('spark', 'sa', '1'); -- "1prk" SELECT translate('spark', 'ss', '12'); -- "1park"
- 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