Built-in functions

Applies to

• Hive SQL

• Spark SQL

• Databricks SQL

For more information about built-in functions and their Snowflake equivalents, also see Common built-in functions.

Built-in Functions

This article provides an alphabetically-ordered list of built-in functions and operators in Databricks. (Databricks SQL Language Reference Built-in functions).

Spark SQL - Databricks SQL	Snowflake
ABS	ABS
ACOS	ACOS
ACOSH	ACOSH
ADD_MONTHS	ADD_MONTHS
ANY_VALUE	ANY_VALUE
ANY	BOOLOR_AGG
APPROX_COUNT_DISTINCT	APPROX_COUNT_DISTINCT
APPROX_PERCENTILE	APPROX_PERCENTILE
ARRAY_AGG	ARRAY_AGG
ARRAY_APPEND	ARRAY_APPEND
ARRAY_COMPACT	ARRAY_COMPACT
ARRAY_CONTAINS	ARRAY_CONTAINS
ARRAY_DISTINCT	ARRAY_DISTINCT
ARRAY_EXCEPT	ARRAY_EXCEPT
ARRAY_INSERT	ARRAY_INSERT_UDF Note: A User Defined Function is created to replicate the source behaviour.
ARRAY_INTERSECT	ARRAY_INTERSECTION
ARRAY_JOIN	ARRAY_TO_STRING
ARRAY_MAX	ARRAY_MAX
ARRAY_MIN	ARRAY_MIN
ARRAY_POSITION(array, element)	ARRAY_POSITION(element, array) Note: Parameters are inverted.
ARRAY_PREPEND	ARRAY_PREPEND
ARRAY_REMOVE	ARRAY_REMOVE
ARRAY_SIZE	ARRAY_SIZE
ARRAY	ARRAY_CONSTRUCT
ARRAYS_OVERLAP	ARRAYS_OVERLAP
ARRAYS_ZIP	ARRAYS_ZIP
ASCII	ASCII
ASIN	ASIN
ASINH	ASINH
ATAN	ATAN
ATAN2	ATAN2
ATANH	ATANH
AVG	AVG
BIT_COUNT	BITCOUNT
BIT_GET	GETBIT
BOOL_AND	BOOLAND_AGG
BOOL_OR	BOOLOR_AGG
BTRIM	TRIM
CBRT	CBRT
CEIL	CEIL
CEILING	CEIL
CHAR_LENGTH	LENGTH
CHARACTER_LENGTH	LENGTH
CHR	CHR
COALESCE	COALESCE
COLLECT_LIST	ARRAY_AGG
CONCAT_WS	CONCAT_WS_UDF Note: A User Defined Function is created to emulate the source behaviour.
CONCAT	CONCAT
CONTAINS	CONTAINS
CORR	CORR
COS	COS
COSH	COSH
COT	COT
COUNT_IF	COUNT_IF
COUNT	COUNT
COVAR_POP	COVAR_POP
COVAR_SAMP	COVAR_SAMP
CUME_DIST	CUME_DIST
CURDATE	CURRENT_DATE
CURRENT_DATABASE	CURRENT_DATABASE
CURRENT_DATE	CURRENT_DATE
CURRENT_SCHEMA	CURRENT_SCHEMA
CURRENT_TIMESTAMP	CURRENT_TIMESTAMP
CURRENT_USER	CURRENT_USER
DATE_ADD	DATEADD
DATE_DIFF	DATEDIFF
DATE_TRUNC	DATE_TRUNC
DATE	DATE
DAY	DAY
DAYNAME	DAYNAME
DAYOFWEEK	DAYOFWEEK
DAYOFYEAR	DAYOFYEAR
DECODE	DECODE
DEGREES	DEGREES
DENSE_RANK	DENSE_RANK
ENDSWITH	ENDSWITH
EVERY	BOOLAND_AGG
EXP	EXP
FIRST_VALUE	FIRST_VALUE
FLOOR	FLOOR
GET	GET
GETBIT	GETBIT
GETDATE	CURRENT_TIMESTAMP
GREATEST	GREATEST
GROUPING	GROUPING
HASH	HASH
HEX	HEX_ENCODE
HLL_SKETCH_ESTIMATE	HLL_ESTIMATE
HOUR	HOUR
HOUR	HOUR
IF	IFF
IFF	IFF
IFNULL	IFNULL
INITCAP	INITCAP
KURTOSIS	KURTOSIS
LAG	LAG
LAST_DAY	LAST_DAY
LAST_DAY	LAST_DAY
LAST_VALUE	LAST_VALUE
LCASE	LOWER
LEAD	LEAD
LEAST	LEAST
LEFT	LEFT
LEN	LEN
LENGTH	LENGTH
LEVENSHTEIN	EDITDISTANCE
LISTAGG	LISTAGG
LN	LN
LOCATE	CHARINDEX
LOG	LOG
LOWER	LOWER
LPAD	LPAD
LTRIM	LTRIM
MAP_KEYS	OBJECT_KEYS
MAP(key, value, ...)	OBJECT_CONSTRUCT(key, value, ...) Note: The keys are casted to VARCHAR since Snowflake does not allow another type as keys.
MAX_BY	MAX_BY
MAX	MAX
MD5	MD5
MEAN	AVG
MEDIAN	MEDIAN
MIN_BY	MIN_BY
MIN	MIN
MINUTE	MINUTE
MOD	MOD
MODE	MODE
MONTH	MONTH
MONTHS_BETWEEN	MONTHS_BETWEEN
NAMED_STRUCT	OBJECT_CONSTRUCT
NOW	CURRENT_TIMESTAMP
NTH_VALUE	NTH_VALUE
NTILE	NTILE
NULLIF	NULLIF
NULLIFZERO	NULLIFZERO
NVL	NVL
NVL2	NVL2
OCTET_LENGTH	OCTET_LENGTH
PARSE_JSON	PARSE_JSON
PERCENT_RANK	PERCENT_RANK
PERCENTILE_APPROX	APPROX_PERCENTILE
PERCENTILE_CONT	PERCENTILE_CONT
PERCENTILE_DISC	PERCENTILE_DISC
PI	PI
POSITION	POSITION
POW	POW
POWER	POWER
QUARTER	QUARTER
RADIANS	RADIANS
RANDOM	RANDOM
RANK	RANK
REGEXP_COUNT	REGEXP_COUNT
REGEXP_INSTR	REGEXP_INSTR
REGEXP_REPLACE	REGEXP_REPLACE
REGEXP_SUBSTR	REGEXP_SUBSTR
REGR_AVGX	REGR_AVGX
REGR_AVGY	REGR_AVGY
REGR_COUNT	REGR_COUNT
REGR_INTERCEPT	REGR_INTERCEPT
REGR_R2	REGR_R2
REGR_SLOPE	REGR_SLOPE
REGR_SXX	REGR_SXX
REGR_SXY	REGR_SXY
REGR_SYY	REGR_SYY
REPEAT	REPEAT
REPLACE	REPLACE
REVERSE	REVERSE
RIGHT	RIGHT
ROUND	ROUND
ROW_NUMBER	ROW_NUMBER
RPAD	RPAD
RTRIM	RTRIM
SECOND	SECOND
SESSION_USER	CURRENT_USER
SHA1	SHA1
SHA2	SHA2
SHIFTLEFT	BITSHIFTLEFT
SHIFTRIGHT	BITSHIFTRIGHT
SIGN	SIGN
SIGNUM	SIGN
SIN	SIN
SINH	SINH
SKEWNESS	SKEW
SOME	BOOLOR_AGG
SOUNDEX	SOUNDEX
SPACE	SPACE
SPLIT_PART	SPLIT_PART
SQRT	SQRT
STARTSWITH	STARTSWITH
STD	STDDEV_SAMP
STDDEV_POP	STDDEV_POP
STDDEV_SAMP	STDDEV_SAMP
STDDEV	STDDEV_SAMP
STRING	TO_VARCHAR
STRUCT	OBJECT_CONSTRUCT
SUBSTR	SUBSTR
SUBSTRING	SUBSTRING
SUM	SUM
TAN	TAN
TANH	TANH
TIMESTAMP	TO_TIMESTAMP
TO_CHAR	TO_CHAR
TO_DATE	TO_DATE
TO_NUMBER	TO_NUMBER
TO_TIMESTAMP	TO_TIMESTAMP
TO_VARCHAR	TO_VARCHAR
TRANSLATE	TRANSLATE
TRIM	TRIM
TRUNC	TRUNC
TRUNC	TRUNC
TRY_AVG	AVG
TRY_CAST	TRY_CAST
TRY_SUM	TRY_SUM
TRY_TO_NUMBER	TRY_TO_NUMBER
TRY_TO_TIMESTAMP	TRY_TO_TIMESTAMP
TYPEOF	TYPEOF
UCASE	UPPER
UPPER	UPPER
USER	CURRENT_USER
UUID	UUID_STRING
VAR_POP	VAR_POP
VAR_SAMP	VAR_SAMP
VARIANCE_POP	VARIANCE_POP
VARIANCE_SAMP	VARIANCE_SAMP
VARIANCE	VARIANCE
WIDTH_BUCKET	WIDTH_BUCKET
YEAR	YEAR
ZEROIFNULL	ZEROIFNULL