Skip to content
  1. Home
  2. Docs
  3. Reference
  4. SQL Reference
  5. SQL functions

SQL functions

Function Example Result Type
ADD_MONTHS SELECT ADD_MONTHS(‘2020-06-30’, 1) FROM <TABLE> LIMIT 1 2020-07-31T00:00:00.000Z timestamp
ABS SELECT ABS(-1) FROM <TABLE> LIMIT 1 1 integer
ASCII SELECT ascii(‘222’) from <TABLE> LIMIT 1 50 integer
ASIN SELECT ASIN(0) from <TABLE> LIMIT 1 0 float
CHAR SELECT char(65) from <TABLE> LIMIT 1 “A” string
CHR SELECT char(65) from <TABLE> LIMIT 1 “A” string
DATE_DIFF
SELECT datediff(‘2009-07-31’, ‘2009-07-30’)from <TABLE> LIMIT 1; 1 integer
SELECT datediff(‘2009-07-30’, ‘2009-07-31’) from <TABLE> LIMIT 1; -1 integer
DATE_FORMAT SELECT date_format(‘2020-04-08’, ‘y’) from <TABLE> LIMIT 1 2020 string
DATE_TRUNC
SELECT date_trunc(‘YEAR’, ‘2015-03-05T09:32:05.359’) from <TABLE> 1; 2015-01-01T00:00:00.000-08:00 timestamp
SELECT date_trunc(‘MM’, ‘2015-03-05T09:32:05.359’) from <TABLE> LIMIT 1; 2015-03-01T00:00:00.000-07:00 timestamp
SELECT date_trunc(‘DD’, ‘2015-03-05T09:32:05.359’) from <TABLE> LIMIT 1; 2015-03-05T00:00:00.000-07:00 timestamp
SELECT date_trunc(‘HOUR’, ‘2015-03-05T09:32:05.359’) from <TABLE> LIMIT 1; 2015-03-05T09:00:00.000-07:00 timestamp
DAYOFWEEK SELECT dayofweek(‘2009-07-30’) from <TABLE> LIMIT 1; 5 integer
DAYOFYEAR SELECT dayofyear(‘2016-04-09’) from <TABLE> LIMIT 1; 100 integer
FIND_IN_SET SELECT find_in_set(‘ab’,’abc,b,ab,c,def’) from <TABLE> LIMIT 1; 3 integer
FORMAT_NUMBER
SELECT format_number(12332.123456, 4) from <TABLE> LIMIT 1; 12,332.12 string
SELECT format_number(12332.123456, ‘##################.###’) from <TABLE> LIMIT 1; 12332.123 string
FROM_UNIXTIME SELECT from_unixtime(0, ‘yyyy-MM-dd HH:mm:ss’) from <TABLE> LIMIT 1 1969-12-31 16:00:00 string
FROM_UTC_TIMESTAMP SELECT from_utc_timestamp(‘2016-08-31’, ‘Asia/Seoul’) from <TABLE> LIMIT 1 2016-08-31T09:00:00.000-07:00 timestamp
GREATEST
SELECT greatest(10, 9, 2, 4, 3) from FORECAST_CANONICAL LIMIT 1 10 integer
SELECT greatest(“abc”, “bcd”) from FORECAST_CANONICAL LIMIT 1 bcd string
SELECT greatest(TRUE, FALSE) from FORECAST_CANONICAL LIMIT 1 TRUE boolean
SELECT greatest(FALSE, NULL) from FORECAST_CANONICAL LIMIT 1 FALSE boolean
SELECT greatest(TRUE, NULL) from FORECAST_CANONICAL LIMIT 1 TRUE boolean
HOUR SELECT hour(‘2009-07-30 12:58:59’) from <TABLE> LIMIT 1 12 integer
INITCAP SELECT initcap(‘sPark sql’) from <TABLE> LIMIT 1 Spark Sql string
LAST_DAY SELECT last_day(‘2009-01-12’) from <TABLE> LIMIT 1 2009-01-31T00:00:00.000Z timestamp
LEFT SELECT left(‘Spark SQL’, 3) from <TABLE> LIMIT 1 Spa string
MAX
SELECT max(col) FROM VALUES (10), (50), (20) AS tab(col); FAILS
SELECT max(AVG_MISDEMEANOR) from MISDEMEANOR_RATE; 11598.6
MINUTE SELECT minute(‘2009-07-30 12:58:59’) FROM <TABLE>; 58 int
MD5 SELECT md5(‘Xcalar’) FROM <TABLE>; d5997d139a9944f3ea8d2533c0929175 string
MOD
SELECT 2 % 1.8 FROM <TABLE>; 0.2 float
SELECT MOD(2, 1.8) FROM <TABLE>; 0.2 float
MONTH SELECT month(‘2016-07-30’) FROM <TABLE>; 7 int
MONTHS_BETWEEN SELECT months_between(‘1997-02-28 10:30:00’, ‘1996-10-30’) FROM <TABLE>; 3.94959677 float
NANVL SELECT nanvl(cast(‘NaN’ as double), 123) FROM <TABLE>; 123 float
NEGATIVE SELECT negative(1) FROM <TABLE>; -1 int
NEXT_DAY SELECT next_day(‘2015-01-14’, ‘TU’) FROM <TABLE>; 2015-01-20T00:00:00.000Z timestamp
NOW SELECT now() FROM MISDEMEANOR_RATE; 2020-11-13T17:20:41.292Z timestamp
NULLIF SELECT nullif(2, 2) FROM <TABLE>; FNF
NVL2 SELECT nvl2(NULL, 2, 1) FROM <TABLE>; 1 int
OCTET_LENGTH SELECT octet_length(‘Xcalar’) FROM <TABLE>; 6 int
PERCENT_RANK SELECT PERCENT_RANK(PRECINCT) FROM MISDEMEANOR_RATE; column of floats ([0.77,…]) float
PI SELECT pi() FROM <TABLE>; 3.14…. float
PMOD SELECT pmod(-10, 3) FROM <TABLE>; 2 int
POSITION SELECT position(‘bar’, ‘foobarbar’) FROM <TABLE>; 4 int
POW SELECT pow(2, 3) FROM <TABLE>; 8 float
POWER SELECT power(2, 3) FROM <TABLE>; 8 float
PRINTF SELECT printf(“Hello World %d %s”, 100, “days”) FROM <TABLE>; Hello World 100 days string
QUARTER SELECT quarter(‘2016-08-31’) FROM <TABLE>; 3 int
RADIANS SELECT radians(180) FROM <TABLE>; 3.14… float
RAND
SELECT rand() FROM <TABLE>; col of floats float
SELECT rand(0) FROM <TABLE>; col of floats float
SELECT rand(null) FROM <TABLE>; col of floats float
RANK SELECT RANK(COL) FROM <TABLE>; column of integers int
REGEXP_EXTRACT SELECT regexp_extract(‘100-200’, ‘(\\d+)-(\\d+)’, 1) FROM <TABLE>; 100 string
REGEXP_REPLACE SELECT regexp_replace(‘100-200’, ‘(\\d+)’, ‘num’) FROM <TABLE>; num-num string
REPEAT SELECT repeat(‘123’, 2) FROM <TABLE>; 123123 string
REPLACE SELECT replace(‘ABCabc’, ‘abc’, ‘DEF’) FROM <TABLE>; ABCDEF string
RIGHT SELECT right(‘Spark SQL’, 3) FROM <TABLE>; SQL string
RINT SELECT rint(12.3456) FROM <TABLE>; 12 float
RLIKE SELECT ‘SystemDriveUsersJohn’ rlike ‘SystemDriveUsers.*’ FROM <TABLE>; TRUE boolean
ROUND SELECT round(2.5, 0) FROM <TABLE>; 3 float
RPAD SELECT rpad(‘hi’, 5, ‘??’) FROM <TABLE>; hi??? string
RTRIM SELECT rtrim(‘ SparkSQL ‘) FROM <TABLE>; SparkSQL string
SCHEMA_OF_JSON SELECT schema_of_json(‘[{“col”:0}]’) FROM <TABLE>; array<struct<col:bigint>> string
SECOND SELECT second(‘2009-07-30 12:58:59’) FROM <TABLE>; 59 int
SHA SELECT sha(‘Spark’) FROM <TABLE>; 19ba73209de2712b78b800260417130a49538513 string
SHA1 SELECT sha1(‘Xcalar’) FROM <TABLE>; 19ba73209de2712b78b800260417130a49538513 string
SHIFTLEFT SELECT shiftleft(2, 1) FROM <TABLE>; 4 int
SHIFTRIGHT SELECT shiftright(4, 1) FROM <TABLE>; 2 int
SHIFTRIGHTUNSIGNED SELECT shiftrightunsigned(4, 1) FROM <TABLE>; 2 int
SIGN SELECT sign(40) FROM <TABLE>; 1 int
SIGNUM SELECT signum(40) FROM <TABLE>; 1 int
SIN SELECT sin(0) FROM <TABLE>; 0 int
SINH SELECT sinh(0) FROM <TABLE>; 0 int
SIZE SELECT size(array(‘b’, ‘d’, ‘c’, ‘a’)) FROM <TABLE>; 4 int
SMALLINT SELECT smallint(COL) FROM <TABLE>; COL of INTs INT
SOUNDEX SELECT soundex(‘Miller’) FROM <TABLE>; M460 string
SPACE SELECT concat(space(2), ‘1’) FROM <TABLE>; 1
SQRT SELECT sqrt(4) FROM <TABLE>; 2 int
STD SELECT std(COL) from <TABLE>; 20.9444…. float
STDDEV SELECT stddev(COL) from <TABLE>; 20.9444…. float
STDDEV_POP SELECT stddev_pop(COL) from <TABLE>; 20.93240805 float
STDDEV_SAMP SELECT stddev_samp(COL) from <TABLE>; 20.94440714 float
STRING SELECT STRING(1) FROM <TABLE>; 1 string
SUBSTR SELECT substr(‘Spark SQL’, 5) FROM <TABLE>; k SQL string
SUBSTRING SELECT substring(‘Spark SQL’, -3) FROM <TABLE>; SQL string
SUBSTRING_INDEX SELECT substring_index(‘www.apache.org’, ‘.’, 2) FROM <TABLE>; www.apache string
SUM SELECT SUM(COL) FROM <TABLE>; 59657 int
TAN SELECT tan(0) FROM <TABLE>; 0 float
TANH SELECT tanh(0) FROM <TABLE>; 0 float
TO_DATE SELECT to_date(‘2009-07-30 04:17:52’) FROM <TABLE>; 2009-07-30T00:00:00.000Z timestamp
TO_JSON SELECT to_json(named_struct(‘a’, 1, ‘b’, 2)) FROM <TABLE>; {“a”:1,”b”:2} string
TO_TIMESTAMP SELECT to_timestamp(‘2016-12-31 00:12:00’) FROM <TABLE>; 2016-12-31T00:12:00.000Z timestamp
TO_UNIX_TIMESTAMP SELECT to_unix_timestamp(‘2016-04-08’, ‘yyyy-MM-dd’) FROM <TABLE>; 1460073600 int
TO_UTC_TIMESTAMP SELECT to_utc_timestamp(‘2016-08-31’, ‘Asia/Seoul’) FROM <TABLE>; 2016-08-30T15:00:00.000Z timestamp
TRANSLATE SELECT translate(‘AaBbCc’, ‘abc’, ‘123’) FROM <TABLE>; A1B2C3 string
TRIM SELECT trim(TRAILING ‘SL’ FROM ‘SSparkSQLS’) FROM <TABLE>; SSparkSQ string
Was this article helpful to you? Yes No

How can we help?