ASCII
Function statement:
Supported engines: SparkSQL and Presto
Usage instructions: Return the numeric value of the first character of str.
Return type: integer
Example:
> SELECT ascii('222');
50
BASE64
Function statement:
-- SparkSQL
BASE64(<str> string|binary)
-- Presto
BASE64(<str> binary)
Supported engines: SparkSQL and Presto
Usage instructions: Convert the parameter to a base64 string.
Return type: string
Example:
> SELECT base64('tencent');
dGVuY2VudA==
BIT_LENGTH
Function statement:
BIT_LENGTH(<expr> string|binary)
Supported engines: SparkSQL and Presto
Usage instructions: Return the bit length of string data or the number of bits in binary data.
Return type: integer
Example:
> select bit_length('tencent');
56
> select bit_length(binary('tencent'));
56
CHAR
Function statement:
Supported engines: SparkSQL and Presto
Usage instructions: Return the ASCII character of expr. If expr is greater than 256, then expr = expr %256.
Return type: string
Example:
CHR
Function statement:
Supported engine: SparkSQL
Usage instructions: Return the ASCII character of expr. If expr is greater than 256, then expr = expr %256.
Return type: string
Example:
CHAR_LENGTH
Function statement:
CHAR_LENGTH(<expr> string|binary)
Supported engines: SparkSQL and Presto
Usage instructions: Return the character length of the string data or the number of bytes in the binary data. The length of the string data includes trailing spaces. The length of the binary data includes binary zeros.
Return type: integer
Example:
> select char_length(binary('tencent'));
7
> select char_length('tencent');
7
CHARACTER_LENGTH
Function statement:
CHARACTER_LENGTH(<expr> string|binary)
Supported engines: SparkSQL and Presto
Usage instructions: Return the character length of the string data or the number of bytes in the binary data. The length of the string data includes trailing spaces. The length of the binary data includes binary zeros.
Return type: integer
Example:
> select character_length(binary('tencent'));
7
> select character_length('tencent');
7
CONCAT_WS
Function statement:
CONCAT_WS(<sep> string[, <s> string|array<string>}]+)
Supported engines: SparkSQL and Presto
Usage instructions: Return a string separated by sep.
Return type: string
Example:
> SELECT concat_ws(' ', 'tencent', 'dlc');
tencent dlc
DECODE
Function statement:
DECODE(<expr> binary|string, <charset>string)
Supported engines: SparkSQL and Presto
Usage instructions: Decode the first parameter using the character set specified by the second parameter.
Return type: string
Example:
> SELECT decode(encode('abc', 'utf-8'), 'utf-8');
abc
ELT
Function statement:
ELT(<n> integer, <s1> string, <s2> string, ...)
Supported engines: SparkSQL and Presto
Usage instructions: Return the nth element.
Return type: string
Example:
> SELECT elt(1, 'scala', 'java');
scala
ENCODE
Function statement:
ENCODE(<expr> binary|string, <charset> string)
Supported engines: SparkSQL and Presto
Usage instructions: Encode the first parameter using the character set specified by the second parameter.
Return type: string
Example:
> SELECT encode('abc', 'utf-8');
abc
> SELECT encode(x'616263', 'utf-8');
abc
FIND_IN_SET
Function statement:
FIND_IN_SET(<str> string, <str_array> string)
Supported engines: SparkSQL and Presto
Usage instructions: Return the index (starting from 1) of the given string str in the comma-separated list atr_array. If the string is not found or if str contains a comma, 0 is returned.
Return type: integer
Example:
> SELECT find_in_set('ab','abc,b,ab,c,def');
3
Function statement:
FORMAT_NUMBER(<expr1> integer|double|decimal, <expr2> string|integer)
Supported engines: SparkSQL and Presto
Usage instructions: Format expr1 as '#,####,####.##' and round to expr2 decimal places. If expr2 is 0, the result will have no decimal point or decimal part.
Return type: string
Example:
> SELECT format_number(12332.123456, 4);
12,332.1235
> SELECT format_number(12332.123456, '##################.###');
12332.123
Function statement:
FORMAT_STRING(<str> string, obj <T>, ...)
Supported engines: SparkSQL and Presto
Usage instructions: Return the formatted string from a printf-style format string.
Return type: string
Example:
> SELECT format_string("Hello World %d %s", 100, "days");
Hello World 100 days
INITCAP
Function statement:
Supported engines: SparkSQL and Presto
Usage instructions: Capitalize the first letter of each word, with all other letters in lowercase.
Return type: string
Example:
> SELECT initcap('sPark sql');
Spark Sql
INSTR
Function statement:
INSTR(<str> string, <substr> string)
Supported engines: SparkSQL and Presto
Usage instructions: Return the index (starting from 1) of the first occurrence of substr in str.
Return type: integer
Example:
> SELECT instr('SparkSQL', 'SQL');
6
LCASE
Function statement:
Supported engines: SparkSQL and Presto
Usage instructions: Convert str to lowercase.
Return type: string
Example:
> SELECT lcase('SparkSQL');
sparksql
LENGTH
Function statement:
LENGTH(<expr> string|binary)
Supported engines: SparkSQL and Presto
Usage instructions: Return the character length of the string data or the number of bytes in the binary data. The length of the string data includes trailing spaces. The length of the binary data includes binary zeros.
Return type: integer
Example:
> SELECT length('Spark SQL ');
10
LEVENSHTEIN
Function statement:
LEVENSHTEIN(<s1> string, <s2> string)
Supported engines: SparkSQL and Presto
Usage instructions: Return the Levenshtein distance between the two given strings.
Return type: integer
Example:
> SELECT levenshtein('kitten', 'sitting');
3
LIKE
Function statement:
LIKE(<s1> str, <s2> pattern)
<str> like <pattern>[ ESCAPE <escape>]
Supported engines: SparkSQL and Presto
Usage instructions: Return true if str matches the pattern with the escape character; return null if any parameter is null; otherwise, return false.
Return type: boolean
Example:
> SELECT like('Spark', '_park');
true
> SET spark.sql.parser.escapedStringLiterals=true;
spark.sql.parser.escapedStringLiterals true
> SELECT '%SystemDrive%\\Users\\John' like '\\%SystemDrive\\%\\\\Users%';
true
> SET spark.sql.parser.escapedStringLiterals=false;
spark.sql.parser.escapedStringLiterals false
> SELECT '%SystemDrive%\\\\Users\\\\John' like '\\%SystemDrive\\%\\\\\\\\Users%';
false
> SELECT '%SystemDrive%/Users/John' like '/%SystemDrive/%//Users%' ESCAPE '/';
true
LOWER
Function statement:
Supported engines: SparkSQL and Presto
Usage instructions: Return str with all characters converted to lowercase.
Return type: string
Example:
> SELECT lower('TENCENT');
tencent
LOCATE
Function statement:
LOCATE(<substr> string, <str> string[, <pos> integer])
Supported engines: SparkSQL and Presto
Usage instructions: Return the position of the first occurrence of substr in str after the pos position.
Return type: integer
Example:
> SELECT locate('bar', 'foobarbar');
4
> SELECT locate('bar', 'foobarbar', 5);
7
OCTET_LENGTH
Function statement:
OCTET_LENGTH(<expr> string|binary)
Supported engines: SparkSQL and Presto
Usage instructions: Return the character length of the string data or the number of bytes in the binary data.
Return type: integer
Example:
> SELECT octet_length('Spark SQL');
9
LPAD
Function statement:
LPAD(<str> string, <len> integer[, <pad> string])
Supported engines: SparkSQL and Presto
Usage instructions: Return str, left-padded with pad to a length of len. If str is longer than len, the returned value is truncated to len characters. If pad is not specified, str will be padded with space characters.
Return type: string
Example:
> SELECT lpad('hi', 5, '??');
???hi
> SELECT lpad('hi', 1, '??');
h
> SELECT lpad('hi', 5);
hi
LTRIM
Function statement:
Supported engines: SparkSQL and Presto
Usage instructions: Remove leading space characters from str.
Return type: string
Example:
> SELECT ltrim(' SparkSQL ');
SparkSQL
PARSE_URL
Function statement:
PARSE_URL(<url> string, <path> string[, <key> string])
Supported engines: SparkSQL and Presto
Usage instructions: Extract the path from the URL.
Return type: string
Example:
> SELECT parse_url('http://spark.apache.org/path?query=1', 'HOST');
spark.apache.org
> SELECT parse_url('http://spark.apache.org/path?query=1', 'QUERY');
query=1
> SELECT parse_url('http://spark.apache.org/path?query=1', 'QUERY', 'query');
1
POSITION
Function statement:
POSITION(<substr> string, <str> string[, <pos> integer])
Supported engines: SparkSQL and Presto
Usage instructions: Return the position of the first occurrence of substr in str after the pos position.
Return type: integer
Example:
> SELECT position('bar', 'foobarbar');
4
> SELECT position('bar', 'foobarbar', 5);
7
> SELECT POSITION('bar' IN 'foobarbar');
4
PRINTF
Function statement:
PRINTF(<str> string, obj <T>, ...)
Supported engines: SparkSQL and Presto
Usage instructions: Return the formatted string from a printf-style format string.
Return type: string
Example:
> SELECT printf("Hello World %d %s", 100, "days");
Hello World 100 days
REPEAT
Function statement:
REPEAT(<str> string, <n> integer)
Supported engines: SparkSQL and Presto
Usage instructions: Return a string that repeats the given string n times.
Return type: string
Example:
> SELECT repeat('123', 2);
123123
REPLACE
Function statement:
REPLACE(<str> string, <search> string[, <replace> string])
Supported engines: SparkSQL and Presto
Usage instructions: Replace all occurrences of search in str with replace.
Return type: string
Example:
> SELECT replace('ABCabc', 'abc', 'DEF');
ABCDEF
OVERLAY
Function statement:
OVERLAY(<input> string, <replace> string, <pos> integer[, <len> integer])
Supported engine: SparkSQL
Usage instructions: Replace input with replace, starting from pos and spanning len.
Return type: string
Example:
> SELECT overlay('Spark SQL' PLACING '_' FROM 6);
Spark_SQL
> SELECT overlay('Spark SQL' PLACING 'CORE' FROM 7);
Spark CORE
> SELECT overlay('Spark SQL' PLACING 'ANSI ' FROM 7 FOR 0);
Spark ANSI SQL
> SELECT overlay('Spark SQL' PLACING 'tructured' FROM 2 FOR 4);
Structured SQL
> SELECT overlay(encode('Spark SQL', 'utf-8') PLACING encode('_', 'utf-8') FROM 6);
Spark_SQL
> SELECT overlay(encode('Spark SQL', 'utf-8') PLACING encode('CORE', 'utf-8') FROM 7);
Spark CORE
> SELECT overlay(encode('Spark SQL', 'utf-8') PLACING encode('ANSI ', 'utf-8') FROM 7 FOR 0);
Spark ANSI SQL
> SELECT overlay(encode('Spark SQL', 'utf-8') PLACING encode('tructured', 'utf-8') FROM 2 FOR 4);
Structured SQL
RPAD
Function statement:
RPAD(<str> string, <len> integer[, <pad> string])
Supported engines: SparkSQL and Presto
Usage instructions: Return str, right-padded with pad to a length of len. If str is longer than len, the returned value is truncated to len characters. If pad is not specified, str will be padded with space characters.
Return type: string
Example:
> SELECT rpad('hi', 5, '??');
hi???
> SELECT rpad('hi', 1, '??');
h
> SELECT rpad('hi', 5);
hi
RTRIM
Function statement:
Supported engines: SparkSQL and Presto
Usage instructions: Remove trailing space characters from str.
Return type: string
Example:
> SELECT rtrim(' SparkSQL ');
SparkSQL
SENTENCES
Function statement:
SENTENCES(<str> string[, <lang> string, <country> string])
Supported engines: SparkSQL and Presto
Usage instructions: Split str into an array of words.
Return type: array <string>.
Example:
> SELECT sentences('Hi there! Good morning.');
[["Hi","there"],["Good","morning"]]
SOUNDEX
Function statement:
Supported engines: SparkSQL and Presto
Usage instructions: Return the Soundex code of the string.
Return type: string
Example:
> SELECT soundex('Miller');
M460
SPACE
Function statement:
Supported engines: SparkSQL and Presto
Usage instructions: Return a string consisting of n spaces.
Return type: string
Example:
> SELECT concat(space(2), '1');
1
SPLIT
Function statement:
SPLIT(<str> string, <regex> string, <limit> integer)
Supported engines: SparkSQL and Presto
Usage instructions: Split str using the string that matches the regex as the delimiter, and return an array with a maximum length of limit.
Return type: array <string>.
Example:
> SELECT split('oneAtwoBthreeC', '[ABC]');
["one","two","three",""]
> SELECT split('oneAtwoBthreeC', '[ABC]', -1);
["one","two","three",""]
> SELECT split('oneAtwoBthreeC', '[ABC]', 2);
["one","twoBthreeC"]
SUBSTRING
Function statement:
SUBSTRING(<str> string, <pos> integer[, <len> integer])
SUBSTRING(<str> FROM <pos>[ FOR <len>])
Supported engines: SparkSQL and Presto
Usage instructions: Return the substring of str starting from pos with a length of len, or the byte array slice starting from pos with a length of len.
Return type: string
Example:
> SELECT substring('Spark SQL', 5);
k SQL
> SELECT substring('Spark SQL', -3);
SQL
> SELECT substring('Spark SQL', 5, 1);
k
> SELECT substring('Spark SQL' FROM 5);
k SQL
> SELECT substring('Spark SQL' FROM -3);
SQL
> SELECT substring('Spark SQL' FROM 5 FOR 1);
k
SUBSTR
Function statement:
SUBSTR(<str> string, <pos> integer[, <len> integer])
SUBSTR(<str> FROM <pos>[ FOR <len>])
Supported engines: SparkSQL and Presto
Usage instructions: Return the substring of str starting from pos with a length of len, or the byte array slice starting from pos with a length of len.
Return type: string
Example:
> SELECT substr('Spark SQL', 5);
k SQL
> SELECT substr('Spark SQL', -3);
SQL
> SELECT substr('Spark SQL', 5, 1);
k
> SELECT substr('Spark SQL' FROM 5);
k SQL
> SELECT substr('Spark SQL' FROM -3);
SQL
> SELECT substr('Spark SQL' FROM 5 FOR 1);
k
LEFT
Function statement:
LEFT(<str> string, <len> integer)
Supported engines: SparkSQL and Presto
Usage instructions: Return the leftmost len characters of the string str. If len is less than or equal to 0, the result is a null character string.
Return type: string
Example:
> SELECT left('tencent', 3);
ten
RIGHT
Function statement:
RIGHT(<str> string, <len> integer)
Supported engines: SparkSQL and Presto
Usage instructions: Return the leftmost len characters of the string str. If len is less than or equal to 0, the result is a null character string.
Return type: string
Example:
> SELECT left('tencent', 3);
ten
SUBSTRING_INDEX
Function statement:
SUBSTRING_INDEX(<str> string, <delim> string, <count> integer)
Supported engines: SparkSQL and Presto
Usage instructions: Return the substring of str before the count occurrence of delim. If count is positive, return all content to the left of the final delimiter (counted from the left). If count is negative, return all content to the right of the final delimiter (counted from the right). The function is case-sensitive when matching delim.
Return type: string
Example:
> SELECT substring_index('cloud.tencent.com', '.', 2);
cloud.tencent
TRANSLATE
Function statement:
TRANSLATE(<input> string, <from> string, <to> string)
Supported engine: SparkSQL
Usage instructions: Transform the input string by replacing characters in the from string with the corresponding characters in the to string.
Return type: string
Example:
> SELECT translate('AaBbCc', 'abc', '123');
A1B2C3
TRIM
Function statement:
TRIM(<str> string)
trim(BOTH FROM str)
trim(LEADING FROM str)
trim(TRAILING FROM str)
trim(trimStr FROM str)
trim(BOTH trimStr FROM str)
trim(LEADING trimStr FROM str)
trim(TRAILING trimStr FROM str)
Supported engines: SparkSQL and Presto
Usage instructions:
trim(str) - Remove leading and trailing space characters from str.
trim(BOTH FROM str): Remove leading and trailing space characters from str.
trim(LEADING FROM str): Remove leading space characters from str.
trim(TRAILING FROM str): Remove trailing space characters from str.
trim(trimStr FROM str): Remove leading and trailing trimStr characters from str.
trim(BOTH trimStr FROM str): Remove leading and trailing trimStr characters from str.
trim(LEADING trimStr FROM str): Remove leading trimStr characters from str.
trim(TRAILING trimStr FROM str): Remove trailing trimStr characters from str.
Return type: string
Example:
> SELECT trim(' SparkSQL ');
SparkSQL
> SELECT trim(BOTH FROM ' SparkSQL ');
SparkSQL
> SELECT trim(LEADING FROM ' SparkSQL ');
SparkSQL
> SELECT trim(TRAILING FROM ' SparkSQL ');
SparkSQL
> SELECT trim('SL' FROM 'SSparkSQLS');
parkSQ
> SELECT trim(BOTH 'SL' FROM 'SSparkSQLS');
parkSQ
> SELECT trim(LEADING 'SL' FROM 'SSparkSQLS');
parkSQLS
> SELECT trim(TRAILING 'SL' FROM 'SSparkSQLS');
SSparkSQ
BTRIM
Function statement:
BTRIM(<str> string[, <trimStr> string])
Supported engines: SparkSQL and Presto
Usage instructions: Remove leading and trailing trimStr characters (default is space) from str.
Return type: string
Example:
> SELECT btrim(' SparkSQL ');
SparkSQL
> SELECT btrim(encode(' SparkSQL ', 'utf-8'));
SparkSQL
> SELECT btrim('SSparkSQLS', 'SL');
parkSQ
> SELECT btrim(encode('SSparkSQLS', 'utf-8'), encode('SL', 'utf-8'));
parkSQ
UCASE
Function statement:
Supported engines: SparkSQL and Presto
Usage instructions: Return str with all characters converted to uppercase.
Return type: string
Example:
> SELECT ucase('SparkSQL');
SPARKSQL
UNBASE64
Function statement:
Supported engines: SparkSQL and Presto
Usage instructions: Convert str from a base64 string to binary.
Return type: binary
Example:
> SELECT unbase64('U3BhcmsgU1FM');
Spark SQL
UNHEX
Function statement:
Supported engines: SparkSQL and Presto
Usage instructions: Convert the hexadecimal str to binary.
Return type: binary
Example:
> select unhex('74656E63656E74');
tencent
UPPER
Function statement:
Supported engines: SparkSQL and Presto
Usage instructions: Return str with all characters converted to uppercase.
Return type: string
Example:
> SELECT upper('tencent');
TENCENT
UUID
Function statement:
Supported engines: SparkSQL and Presto
Usage instructions: Return a 36-character UUID.
Return type: string
Example:
> SELECT uuid();
46707d92-02f4-4817-8116-a4c3b23e6266
XPATH
Function statement:
XPATH(<xml> string, <xpath> string)
Supported engines: SparkSQL and Presto
Usage instructions: Return an array of strings that matches the XPath expression in the XML node.
Return type: array <string>
Example:
> SELECT xpath('<a><b>b1</b><b>b2</b><b>b3</b><c>c1</c><c>c2</c></a>','a/b/text()');
["b1","b2","b3"]
XPATH_BOOLEAN
Function statement:
XPATH_BOOLEAN(<xml> string, <xpath> string)
Supported engines: SparkSQL and Presto
Usage instructions: Return true if the evaluation of the XPath expression is true or if a matching node is found.
Return type: boolean
Example:
> SELECT xpath_boolean('<a><b>1</b></a>','a/b');
true
XPATH_DOUBLE
Function statement:
XPATH_DOUBLE(<xml> string, <xpath> string)
Supported engines: SparkSQL and Presto
Usage instructions: Return a double value; if no match is found, return zero; if a match is found but the value is not a number, return NaN.
Return type: double
Example:
> SELECT xpath_double('<a><b>1</b><b>2</b></a>', 'sum(a/b)');
3.0
XPATH_NUMBER
Function statement:
XPATH_NUMBER(<xml> string, <xpath> string)
Supported engines: SparkSQL and Presto
Usage instructions: Return a double value; if no match is found, return zero; if a match is found but the value is not a number, return NaN.
Return type: double
Example:
> SELECT xpath_number('<a><b>1</b><b>2</b></a>', 'sum(a/b)');
3.0
XPATH_FLOAT
Function statement:
XPATH_FLOAT(<xml> string, <xpath> string)
Supported engines: SparkSQL and Presto
Usage instructions: Return a float value; if no match is found, return zero; if a match is found but the value is not a number, return NaN.
Return type: float
Example:
> SELECT xpath_float('<a><b>1</b><b>2</b></a>', 'sum(a/b)');
3.0
XPATH_INT
Function statement:
XPATH_INT(<xml> string, <xpath> string)
Supported engines: SparkSQL and Presto
Usage instructions: Return an int value; if no match is found, return zero; if a match is found but the value is not a number, return NaN.
Return type: integer
Example:
> SELECT xpath_int('<a><b>1</b><b>2</b></a>', 'sum(a/b)');
3
XPATH_LONG
Function statement:
XPATH_LONG(<xml> string, <xpath> string)
Supported engines: SparkSQL and Presto
Usage instructions: Return a bigint value; if no match is found, return zero; if a match is found but the value is not a number, return NaN.
Return type: bigint
Example:
> SELECT xpath_long('<a><b>1</b><b>2</b></a>', 'sum(a/b)');
3
XPATH_SHORT
Function statement:
XPATH_SHORT(<xml> string, <xpath> string)
Supported engines: SparkSQL and Presto
Usage instructions: Return the value of short type; if no match is found, return zero; if a match is found but the value is not a number, return NaN.
Return type: short
Example:
> SELECT xpath_short('<a><b>1</b><b>2</b></a>', 'sum(a/b)');
3
XPATH_STRING
Function statement:
XPATH_STRING(<xml> string, <xpath> string)
Supported engines: SparkSQL and Presto
Usage instructions: Return the text content of the first XML node that matches the XPath expression.
Return type: string
Example:
> SELECT xpath_string('<a><b>b</b><c>cc</c></a>','a/c');
cc
Function statement:
REGEXP_EXTRACT(<str> string, <regexp> string[, <idx> integer])
Supported engines: SparkSQL and Presto
Usage instructions: Extract the first string in str that matches the regexp expression and corresponds to the regex group show idx.
Return type: string
Example:
> SELECT regexp_extract('100-200', '(\\\\d+)-(\\\\d+)', 1);
100
Function statement:
REGEXP_EXTRACT_ALL(<str> string, <regexp> string[, <idx> integer])
Supported engines: SparkSQL and Presto
Usage instructions: Extract all strings in str that match the regexp expression and correspond to the regex hidden Index.
Return type: array <string>
Example:
> SELECT regexp_extract_all('100-200, 300-400', '(\\\\d+)-(\\\\d+)', 1);
["100","300"]
REGEXP_REPLACE
Function statement:
REGEXP_REPLACE(<str> string, <regexp> string, <rep> string[, <position> integer])
Supported engines: SparkSQL and Presto
Usage instructions: Replace all substrings in str that match the regexp with rep.
Return type: string
Example:
> SELECT regexp_replace('100-200', '(\\\\d+)', 'num');
num-num
REGEXP_LIKE
Function statement:
REGEXP_LIKE(<str> string, <regexp> string)
Supported engine: SparkSQL
Usage instructions: Return true if the string matches the regular expression; otherwise, return false.
Return type: boolean
Example:
> SELECT regexp_like('%SystemDrive%\\Users\\John', '%SystemDrive%\\Users.*');
true
REGEXP
Function statement:
REGEXP(<str> string, <regexp> string)
Supported engine: SparkSQL
Usage instructions: Return true if the string matches the regular expression; otherwise, return false.
Return type: boolean
Example:
> SELECT regexp('%SystemDrive%\\Users\\John', '%SystemDrive%\\Users.*');
true
CONCAT
Function statement:
CONCAT(<s1> string, <s2> string, ...)
Supported engines: SparkSQL and Presto
Usage instructions: Concatenate s1, s2, ...
Return type: string
Example:
> SELECT concat('Spark', 'SQL');
SparkSQL
STR_TO_MAP
Function statement:
str_to_map(<text> string[, <pairDelim> string[, <keyValueDelim> string]])
Supported engines: SparkSQL and Presto
Usage instructions: Create a map by splitting text into key/value pairs using the delimiter. The default delimiter for pairDelim is a comma and for keyValueDelim is a colon. Both pairDelim and keyValueDelim are treated as regular expressions.
Return type: map <string, string>
Example:
> SELECT str_to_map('a:1,b:2,c:3', ',', ':');
{"a":"1","b":"2","c":"3"}
> SELECT str_to_map('a');
{"a":null}
REVERSE
Function statement:
Supported engines: SparkSQL and Presto
Usage instructions: Return a reversed string.
Return type: string
Example:
> SELECT reverse('Spark SQL');
LQS krapS
RLIKE
Function statement:
RLIKE(<str> string, <regexp> string)
Supported engines: SparkSQL and Presto
Usage instructions: Return true if str matches regexp; otherwise, return false.
Return type: boolean
Example:
> SELECT rlike('%SystemDrive%\\Users\\John', '%SystemDrive%\\Users.*');
true
FROM_CSV
Function statement:
FROM_CSV(<cvsStr> string, <schema> string, <options> map<string, string>)
Supported engine: SparkSQL
Usage instructions: Return a structured value with the given csvStr and schema.
Return type: struct
Example:
> SELECT from_csv('1, 0.8', 'a INT, b DOUBLE');
{"a":1,"b":0.8}
> SELECT from_csv('26/08/2015', 'time Timestamp', map('timestampFormat', 'dd/MM/yyyy'));
{"time":2015-08-26 00:00:00}
SCHEMA_OF_CSV
Function statement:
SCHEMA_OF_CSV(<csvStr> string[, options map<string, string>])
Supported engine: SparkSQL
Usage instructions: Return the schema of the csv string.
Return type: string
Example:
> SELECT schema_of_csv('1,abc');
STRUCT<`_c0`: INT, `_c1`: STRING>
TO_CSV
Function statement:
TO_CSV(<expr> struct[, <options> map<string, string>])
Supported engine: SparkSQL
Usage instructions: Return a CSV string with the given structured value.
Return type: string
Example:
> SELECT to_csv(named_struct('a', 1, 'b', 2));
1,2
> SELECT to_csv(named_struct('time', to_timestamp('2015-08-26', 'yyyy-MM-dd')), map('timestampFormat', 'dd/MM/yyyy'));
26/08/2015
NGRAMS
Function statement:
NGRAMS(<a> array<array<string>>, <N> integer, <K> integer, <pf> integer)
Supported engine: Presto
Usage instructions: Return the top k N-grams from a set of tokenized sentences.
Return type: array <struct <string,double>>
CONTEXT_NGRAMS
Function statement:
CONTEXT_NGRAMS((array <array <string>>, array <string>, int, int))
Supported engine: Presto
Usage instructions: Given a contextual N-grams, return the top k contextual N-grams from a set of tokenized sentences.
Return type: array <struct <string, double>>
