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

Last updated