tencent cloud

ASCII(<str> string)

> SELECT ascii('222');
 50

-- SparkSQL
BASE64(<str> string|binary)
-- Presto
BASE64(<str> binary)

> SELECT base64('tencent');
 dGVuY2VudA==

BIT_LENGTH(<expr> string|binary)

> select bit_length('tencent');
 56
> select bit_length(binary('tencent'));
 56

CHAR(<expr> integer)

> SELECT char(65);
 A

CHR(<expr> integer)

> SELECT chr(65);
 A

CHAR_LENGTH(<expr> string|binary)

> select char_length(binary('tencent'));
 7
> select char_length('tencent');
 7

CHARACTER_LENGTH(<expr> string|binary)

CONCAT_WS(<sep> string[, <s> string|array<string>}]+)

> SELECT concat_ws(' ', 'tencent', 'dlc');
tencent dlc

DECODE(<expr> binary|string, <charset>string)

> SELECT decode(encode('abc', 'utf-8'), 'utf-8');
 abc

ELT(<n> integer, <s1> string, <s2> string, ...)

> SELECT elt(1, 'scala', 'java');
 scala

ENCODE(<expr> binary|string, <charset> string)

> SELECT encode('abc', 'utf-8');
 abc
> SELECT encode(x'616263', 'utf-8');
 abc

FIND_IN_SET(<str> string, <str_array> string)

> SELECT find_in_set('ab','abc,b,ab,c,def');
 3

FORMAT_NUMBER(<expr1> integer|double|decimal, <expr2> string|integer)

> SELECT format_number(12332.123456, 4);
 12,332.1235
> SELECT format_number(12332.123456, '##################.###');
 12332.123

FORMAT_STRING(<str> string, obj <T>, ...)

> SELECT format_string("Hello World %d %s", 100, "days");
 Hello World 100 days

INITCAP(<str> string)

> SELECT initcap('sPark sql');
 Spark Sql

INSTR(<str> string, <substr> string)

> SELECT instr('SparkSQL', 'SQL');
 6

LCASE(<str> string)

> SELECT lcase('SparkSQL');
 sparksql

LENGTH(<expr> string|binary)

> SELECT length('Spark SQL ');
 10

LEVENSHTEIN(<s1> string, <s2> string)

> SELECT levenshtein('kitten', 'sitting');
 3

LIKE(<s1> str, <s2> pattern)
<str> like <pattern>[ ESCAPE <escape>]

> 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(<str> string)

> SELECT lower('TENCENT');
 tencent

LOCATE(<substr> string, <str> string[, <pos> integer])

> SELECT locate('bar', 'foobarbar');
 4
> SELECT locate('bar', 'foobarbar', 5);
 7

OCTET_LENGTH(<expr> string|binary)

> SELECT octet_length('Spark SQL');
 9

LPAD(<str> string, <len> integer[, <pad> string])

> SELECT lpad('hi', 5, '??');
 ???hi
> SELECT lpad('hi', 1, '??');
 h
> SELECT lpad('hi', 5);
  hi

LTRIM(<str> string)

> SELECT ltrim('    SparkSQL   ');
 SparkSQL   

PARSE_URL(<url> string, <path> string[, <key> string])

> 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(<substr> string, <str> string[, <pos> integer])

> SELECT position('bar', 'foobarbar');
 4
> SELECT position('bar', 'foobarbar', 5);
 7
> SELECT POSITION('bar' IN 'foobarbar');
 4

PRINTF(<str> string, obj <T>, ...)

> SELECT printf("Hello World %d %s", 100, "days");
 Hello World 100 days

REPEAT(<str> string, <n> integer)

> SELECT repeat('123', 2);
 123123

REPLACE(<str> string, <search> string[, <replace> string])

> SELECT replace('ABCabc', 'abc', 'DEF');
 ABCDEF

OVERLAY(<input> string, <replace> string, <pos> integer[, <len> integer])

> 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(<str> string, <len> integer[, <pad> string])

> SELECT rpad('hi', 5, '??');
 hi???
> SELECT rpad('hi', 1, '??');
 h
> SELECT rpad('hi', 5);
 hi

RTRIM(<str> string)

> SELECT rtrim('    SparkSQL   ');
   SparkSQL

SENTENCES(<str> string[, <lang> string, <country> string])

> SELECT sentences('Hi there! Good morning.');
 [["Hi","there"],["Good","morning"]]

SOUNDEX(<str> string)

> SELECT soundex('Miller');
 M460

SPACE(<n> integer)

> SELECT concat(space(2), '1');
 1

SPLIT(<str> string, <regex> string, <limit> integer)

> SELECT split('oneAtwoBthreeC', '[ABC]');
 ["one","two","three",""]
> SELECT split('oneAtwoBthreeC', '[ABC]', -1);
 ["one","two","three",""]
> SELECT split('oneAtwoBthreeC', '[ABC]', 2);
 ["one","twoBthreeC"]

SUBSTRING(<str> string, <pos> integer[, <len> integer])
SUBSTRING(<str> FROM <pos>[ FOR <len>])

> 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(<str> string, <pos> integer[, <len> integer])
SUBSTR(<str> FROM <pos>[ FOR <len>])

> 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(<str> string, <len> integer)

> SELECT left('tencent', 3);
 ten

RIGHT(<str> string, <len> integer)

> SELECT left('tencent', 3);
 ten

SUBSTRING_INDEX(<str> string, <delim> string, <count> integer)