Built-in functions
Applies to: Databricks SQL Databricks Runtime
This article presents links to and descriptions of built-in operators and functions for strings and binary types, numeric scalars, aggregations, windows, arrays, maps, dates and timestamps, casting, CSV data, JSON data, XPath manipulation, and other miscellaneous functions.
For use cases that are not supported by existing built-in functions, consider defining a custom function. See What are user-defined functions (UDFs)?.
Also see:
Operators and predicates
For information on how operators are parsed with respect to each other, see Operator precedence.
Operator |
Syntax |
Description |
---|---|---|
|
Returns the bitwise |
|
|
Returns the logical |
|
|
Returns |
|
|
Returns true if |
|
|
Returns the logical |
|
|
Tests whether |
|
|
Returns |
|
|
Returns value at |
|
|
Returns the bitwise exclusive |
|
|
Returns fields extracted from the |
|
|
Casts the value |
|
|
Casts the value |
|
|
Returns the integral part of the division of |
|
|
Returns a |
|
|
Returns a |
|
|
Returns |
|
|
Returns |
|
|
Returns |
|
|
Returns |
|
|
Returns true if |
|
|
Returns true if |
|
|
Returns true if |
|
|
Returns |
|
|
Returns |
|
|
Tests whether the arguments do (not) have different values where |
|
|
Tests whether |
|
|
Returns |
|
|
Tests whether |
|
|
Returns true if |
|
|
Returns true if |
|
|
Returns the same result as the |
|
|
Returns |
|
|
Returns |
|
|
Returns |
|
|
Returns the subtraction of |
|
|
Returns the logical |
|
|
Returns the logical |
|
|
Returns the remainder after |
|
|
Returns the concatenation of |
|
|
Returns the bitwise |
|
|
Returns the sum of |
|
|
Returns true if |
|
|
Returns true if |
|
|
Returns true if |
|
|
Returns |
|
|
Returns the bitwise |
Operator precedence
Precedence |
Operator |
---|---|
1 |
|
2 |
|
3 |
|
4 |
|
5 |
|
6 |
|
7 |
|
8 |
|
9 |
|
10 |
|
11 |
|
12 |
|
String and binary functions
Function |
Description |
---|---|
Returns the concatenation of |
|
Decrypts a binary |
|
Encrypts a binary |
|
Returns the ASCII code point of the first character of |
|
Converts |
|
Returns the binary representation of |
|
Casts the value of |
|
Returns the bit length of string data or number of bits of binary data. |
|
Returns the number of bits set in a |
|
Returns |
|
Returns the character at the supplied UTF-16 code point. |
|
Returns the character length of string data or number of bytes of binary data. |
|
Returns the character length of string data or number of bytes of binary data. |
|
Returns the position of the first occurrence of |
|
Returns the character at the supplied UTF-16 code point. |
|
Attaches an explicit collation |
|
Returns the collation attached to |
|
Returns the concatenation of the arguments. |
|
Returns the concatenation strings separated by |
|
Returns |
|
Returns a cyclic redundancy check value of |
|
Translates binary |
|
Returns the binary representation of a string using the |
|
Returns |
|
Returns the position of a string within a comma-separated list of strings. |
|
Formats |
|
Formats |
|
Returns a formatted string from printf-style format strings. |
|
Converts |
|
Returns true if |
|
Returns |
|
Returns the (1-based) index of the first occurrence of |
|
Returns |
|
Returns the leftmost |
|
Returns the character length of string data or number of bytes of binary data. |
|
Returns the character length of string data or number of bytes of binary data. |
|
Returns the Levenshtein distance between the strings |
|
Returns true if |
|
Returns the position of the first occurrence of |
|
Returns |
|
Returns |
|
Returns |
|
mask(str[, upperChar[, lowerChar[, digitChar[, otherChar]]]]) |
Returns a masked version of the input |
Returns an MD5 128-bit checksum of |
|
Returns the byte length of string data or number of bytes of binary data. |
|
Replaces |
|
Extracts a part from |
|
Returns the position of the first occurrence of |
|
Returns the position of the first occurrence of |
|
Returns a formatted string from printf-style format strings. |
|
Returns true if |
|
Returns true if |
|
Returns the number of times |
|
Extracts the first string in |
|
Extracts the all strings in |
|
Returns the position of the first substring in |
|
Replaces all substrings of |
|
Returns the first substring in |
|
Returns the string that repeats |
|
Replaces all occurrences of |
|
Returns a reversed string or an array with reverse order of elements. |
|
Returns the rightmost |
|
Returns true if |
|
Returns |
|
Returns |
|
Splits |
|
Returns a sha1 hash value as a hex string of |
|
Returns a sha1 hash value as a hex string of |
|
Returns a checksum of the SHA-2 family as a hex string of |
|
Returns the soundex code of the string. |
|
Returns a string consisting of |
|
Splits |
|
Splits |
|
Returns |
|
Casts the value |
|
Returns the substring of |
|
Returns the substring of |
|
Returns the substring of |
|
Returns the substring of |
|
Returns the substring of |
|
Returns |
|
Returns |
|
Returns |
|
Returns an |
|
Trim characters from a string. |
|
Decrypts a binary |
|
Returns |
|
Translates a string back from application/x-www-form-urlencoded format, and returns |
|
Returns value decompressed with Zstandard compression, or |
|
Returns |
|
Returns a decoded base64 string as binary. |
|
Converts hexadecimal |
|
Returns |
|
Translates a string back from application/x-www-form-urlencoded format. |
|
Translates a string into application/x-www-form-urlencoded format. |
|
Returns value compressed with Zstandard compression. |
|
Returns value decompressed with Zstandard compression. |
Numeric scalar functions
Function |
Description |
---|---|
Returns the bitwise |
|
Returns |
|
Returns the bitwise |
|
Returns the negated value of |
|
Returns the subtraction of |
|
Returns the value of |
|
Returns the sum of |
|
Returns the remainder after |
|
Returns the bitwise exclusive |
|
Returns the bitwise |
|
Returns |
|
Returns the absolute value of the numeric value in |
|
Returns the inverse cosine (arccosine) of |
|
Returns the inverse hyperbolic cosine of |
|
Returns the inverse sine (arcsine) of |
|
Returns the inverse hyperbolic sine of |
|
Returns the inverse tangent (arctangent) of |
|
Returns the angle in radians between the positive x-axis of a plane and the point specified by the coordinates ( |
|
Returns inverse hyperbolic tangent of |
|
Casts the value |
|
Returns the number of bits set in the argument. |
|
Returns the value of a bit in a binary representation of an integral numeric. |
|
Returns the value obtained by reversing the order of the bits in the argument. |
|
Returns the 0-based bit position of a given |
|
Returns the bitmap bucket number for a given |
|
Returns the rounded |
|
Returns the cube root of |
|
Returns the smallest number not smaller than |
|
Returns the smallest number not smaller than |
|
Converts |
|
Converts the |
|
Returns the cosine of |
|
Returns the hyperbolic cosine of |
|
Returns the cotangent of |
|
Returns the cosecant of |
|
Casts the value |
|
Converts radians to degrees. |
|
Returns the integral part of the division of |
|
Casts the value |
|
Returns the constant |
|
Returns |
|
Returns |
|
Returns the factorial of |
|
Casts the value |
|
Returns the largest number not smaller than |
|
Returns the value of a bit in a binary representation of an integral numeric. |
|
Returns |
|
Casts the value |
|
Returns |
|
Returns the natural logarithm (base |
|
Returns the logarithm of |
|
Returns |
|
Returns the logarithm of |
|
Returns the logarithm of |
|
Returns the remainder after |
|
Returns |
|
Returns the negated value of |
|
Returns |
|
Returns pi. |
|
Returns the positive remainder after |
|
Returns the value of |
|
Raises |
|
Raises |
|
Converts |
|
Returns a random value between 0 and 1. |
|
Returns a random value from a standard normal distribution. |
|
Returns a random value between 0 and 1. |
|
Returns |
|
Returns the rounded |
|
Returns the secant of |
|
Returns a bitwise left shifted by |
|
Returns a bitwise signed signed integral number right shifted by |
|
Returns a bitwise unsigned signed integral number right shifted by |
|
Returns -1.0, 0.0, or 1.0 as |
|
Returns -1.0, 0.0, or 1.0 as |
|
Returns the sine of |
|
Returns the hyperbolic sine of |
|
Casts the value |
|
Returns the square root of |
|
Returns the tangent of |
|
Returns the hyperbolic tangent of |
|
Casts |
|
Returns |
|
Returns the sum of |
|
Returns |
|
Returns the remainder after |
|
Returns |
|
Returns the subtraction of |
|
Returns |
|
Returns the bucket number for a value in an equi-width histogram. |
|
Returns |
Aggregate functions
Function |
Description |
---|---|
Returns true if at least one value of |
|
Returns some value of |
|
Returns the estimated number of distinct values in |
|
Returns the approximate percentile of the |
|
Returns the top |
|
Returns an array consisting of all values in |
|
Returns the mean calculated from values of a group. |
|
Returns the bitwise |
|
Returns the bitwise |
|
Returns the bitwise |
|
Returns the bitwise |
|
Returns the bitwise |
|
Returns true if all values in |
|
Returns true if at least one value in |
|
Returns an array consisting of all values in |
|
Returns an array consisting of all unique values in |
|
Returns Pearson coefficient of correlation between a group of number pairs. |
|
Returns the total number of retrieved rows in a group, including rows containing null. |
|
Returns the number of rows in a group for which the supplied expressions are all non-null. |
|
Returns the number of true values for the group in |
|
Returns a count-min sketch of all values in the group in |
|
Returns the population covariance of number pairs in a group. |
|
Returns the sample covariance of number pairs in a group. |
|
Returns true if all values of |
|
Returns the first value of |
|
Returns the first value of |
|
Computes a histogram on |
|
Returns a HyperLogLog sketch used to approximate a distinct values count. |
|
Aggregates HyperLogLog sketches for a group of rows. |
|
Returns the kurtosis value calculated from values of a group. |
|
Returns the last value of |
|
Returns the last value of |
|
Returns the maximum value of |
|
Returns the value of an |
|
Returns the mean calculated from values of a group. |
|
Returns the median calculated from values of a group. |
|
Returns the minimum value of |
|
Returns the value of an |
|
Returns the most frequent, not |
|
Returns the exact percentile value of |
|
Returns the approximate percentile of the |
|
Returns the interpolated percentile of the |
|
Returns the discrete percentile of the |
|
Returns the mean of |
|
Returns the mean of |
|
Returns the number of non-null value pairs |
|
Returns the intercept of the uni-variate linear regression line in a group where |
|
Returns the coefficient of determination from values of a group where |
|
Returns the slope of the linear regression line of non-null value pairs |
|
Returns the sum of squares of the |
|
Returns the sum of products of |
|
Returns the sum of squares of the |
|
Returns the combined schema of |
|
Returns the combined schema of all |
|
Returns the skewness value calculated from values of a group. |
|
Returns true if at least one value of |
|
Returns the sample standard deviation calculated from the values within the group. |
|
Returns the sample standard deviation calculated from the values within the group. |
|
Returns the population standard deviation calculated from values of a group. |
|
Returns the sample standard deviation calculated from values of a group. |
|
Returns the sum calculated from values of a group. |
|
Returns the mean calculated from values of a group, |
|
Returns the sum calculated from values of a group, |
|
Returns the population variance calculated from values of a group. |
|
Returns the sample variance calculated from values of a group. |
|
Returns the sample variance calculated from values of a group. |
Ranking window functions
Function |
Description |
---|---|
Returns the rank of a value compared to all values in the partition. |
|
Divides the rows for each window partition into n buckets ranging from 1 to at most |
|
Computes the percentage ranking of a value within the partition. |
|
Returns the rank of a value compared to all values in the partition. |
|
Assigns a unique, sequential number to each row, starting with one, according to the ordering of rows within the window partition. |
Analytic window functions
Function |
Description |
---|---|
Returns the position of a value relative to all values in the partition. |
|
Returns the value of |
|
Returns the value of |
|
Returns the value of |
Array functions
Function |
Description |
---|---|
Returns element at position |
|
Aggregates elements in an array using a custom aggregator. |
|
Returns an array with the elements in |
|
Returns |
|
Removes NULL values from |
|
Returns true if |
|
Removes duplicate values from |
|
Returns an array of the elements in |
|
Returns an expanded |
|
Returns an array of the elements in the intersection of |
|
Concatenates the elements of |
|
Returns the maximum value in |
|
Returns the minimum value in |
|
Returns the position of the first occurrence of |
|
Returns |
|
Removes all occurrences of |
|
Returns an array containing |
|
Returns the number of elements in |
|
Returns |
|
Returns an array of the elements in the union of |
|
Returns true if the intersection of |
|
Returns a merged array of structs in which the nth struct contains all Nth values of input arrays. |
|
Returns the size of |
|
Returns the concatenation of the arguments. |
|
Returns the element of an |
|
Returns true if |
|
Returns rows by un-nesting |
|
Returns rows by un-nesting |
|
Filters the array in |
|
Transforms an array of arrays into a single array. |
|
Tests whether |
|
Returns the element of an |
|
Explodes an array of structs into a table. |
|
Explodes an array of structs into a table with outer semantics. |
|
Returns rows by un-nesting the array with numbering of positions. |
|
Returns rows by un-nesting the array with numbering of positions using |
|
Aggregates elements in an array using a custom aggregator. |
|
Returns a reversed string or an array with reverse order of elements. |
|
Generates an array of elements from |
|
Returns a random permutation of the array in |
|
Returns the cardinality of |
|
Returns a subset of an array. |
|
Returns the array in |
|
Transforms elements in an array in |
|
Returns the element of an |
|
Merges the arrays in |
Map functions
Function |
Description |
---|---|
Returns value at |
|
Returns the size of |
|
Returns the value of |
|
Returns rows by un-nesting |
|
Returns rows by un-nesting |
|
Creates a map with the specified key-value pairs. |
|
Returns the union of all |
|
Returns |
|
Returns an unordered array of all entries in |
|
Filters entries in the map in |
|
Creates a map with a pair of the |
|
Creates a map created from the specified array of entries. |
|
Returns an unordered array containing the keys of |
|
Returns an unordered array containing the values of |
|
Merges |
|
Returns the cardinality of |
|
Returns a map after splitting |
|
Transforms keys in a map in |
|
Transforms values in a map in |
|
Returns the value of |
Date, timestamp, and interval functions
For information on date and timestamp formats, see Datetime patterns.
Function |
Description |
---|---|
Returns interval divided by |
|
Returns the negated value of |
|
Returns the subtraction of |
|
Returns the subtraction of |
|
Returns the value of |
|
Returns the sum of |
|
Returns |
|
Returns the absolute value of the interval value in |
|
Returns the date that is |
|
Returns the current date at the start of query evaluation. |
|
Returns the current date at the start of query evaluation. |
|
Returns the current timestamp at the start of query evaluation. |
|
Returns the current session local timezone. |
|
Casts the value |
|
Returns the date |
|
Adds |
|
Returns the difference between two timestamps measured in |
|
Converts a timestamp to a string in the format |
|
Creates a date from the number of days since |
|
Extracts a part of the date, timestamp, or interval. |
|
Returns the date |
|
Returns timestamp truncated to the unit specified in |
|
Returns the date |
|
Adds |
|
Returns the number of days from |
|
Returns the difference between two timestamps measured in |
|
Returns the day of month of the date or timestamp. |
|
Returns the day of month of the date or timestamp. |
|
Returns the day of week of the date or timestamp. |
|
Returns the day of year of the date or timestamp. |
|
Returns the integral part of the division of interval |
|
Returns |
|
Returns |
|
Returns the timestamp at |
|
Returns the current timestamp at the start of query evaluation. |
|
Returns the hour component of a timestamp. |
|
Returns the last day of the month that the date belongs to. |
|
Creates a date from |
|
Creates an day-time interval from |
|
make_interval(years, months, weeks, days, hours, mins, secs) |
Deprecated: Creates an interval from |
Creates a timestamp from |
|
Creates a year-month interval from |
|
Returns the minute component of the timestamp in |
|
Returns the month component of the timestamp in |
|
Returns the number of months elapsed between dates or timestamps in |
|
Returns the first date which is later than |
|
Returns the current timestamp at the start of query evaluation. |
|
Returns the quarter of the year for |
|
Returns the second component of the timestamp in |
|
Creates a session-window over a timestamp expression. |
|
Returns -1.0, 0.0, or 1.0 as interval |
|
Returns -1.0, 0.0, or 1.0 as interval |
|
Returns the difference between two timestamps measured in |
|
Casts |
|
Creates a timestamp |
|
Creates a timestamp |
|
Creates timestamp |
|
Adds |
|
Returns the difference between two timestamps measured in |
|
Returns |
|
Returns |
|
Returns the timestamp in |
|
Returns the timestamp at |
|
Returns a date with the a portion of the date truncated to the unit specified by the format model |
|
Returns the sum of |
|
Returns |
|
Returns |
|
Returns the subtraction of |
|
Returns |
|
Returns the number of days since |
|
Returns the number of microseconds since |
|
Returns the number of milliseconds since |
|
Returns the number of seconds since |
|
eturns the UNIX timestamp of current or specified time. |
|
Returns the day of the week of |
|
Returns the week of the year of |
|
Returns the year component of |
|
Creates a hopping based sliding-window over a timestamp expression. |
|
Returns the inclusive end time of a sliding-window produced by the window or session_window functions. |
H3 geospatial functions
For information about H3 geospatial functions, see H3 geospatial functions.
Cast functions and constructors
For information on casting between types, see cast function and try_cast function.
Function |
Description |
---|---|
Returns an array with the elements in |
|
Casts the value |
|
Casts the value of |
|
Casts |
|
Casts the value |
|
Casts the value |
|
Casts the value |
|
Casts the value |
|
Casts the value |
|
Casts the value |
|
Casts the value |
|
Creates a date from |
|
Creates an day-time interval from |
|
make_interval(years, months, weeks, days, hours, mins, secs) |
Creates an interval from |
Creates a timestamp from |
|
Creates a year-month interval from |
|
Creates a map with the specified key-value pairs. |
|
Creates a struct with the specified field names and values. |
|
Casts the value |
|
Casts the value |
|
Creates a |
|
Casts |
|
Casts |
|
Returns |
|
Returns |
|
Returns |
|
Returns |
|
Returns |
|
Casts the value |
|
Returns |
CSV and Avro functions
Function |
Description |
---|---|
Returns a struct value based on |
|
Returns a struct value with the |
|
Returns the schema of a CSV string in DDL format. |
|
Returns an Avro binary value with the specified struct value. |
|
Returns a CSV string with the specified struct value. |
JSON functions
Function |
Description |
---|---|
Returns fields extracted from the |
|
Returns a struct value with the |
|
Extracts a |
|
Returns the number of elements in the outermost |
|
Returns all the keys of the outermost |
|
Returns multiple |
|
Returns a |
|
Returns the schema of a |
|
Returns the combined schema of |
|
Returns a JSON string with the |
VARIANT functions
Function |
Description |
---|---|
Returns fields extracted from the |
|
Tests whether |
|
Returns a |
|
Returns the schema of a |
|
Returns the combined schema of all |
|
Returns a JSON string with the |
|
Returns a |
|
Extracts a value of |
|
Returns a set of rows by un-nesting |
|
Returns a set of rows by un-nesting |
|
Extracts a value of |
XPath and XML functions
Function |
Description |
---|---|
Returns a struct value parsed from the |
|
Returns the schema of a |
|
Returns values within the nodes of |
|
Returns |
|
Returns a |
|
Returns a |
|
Returns a |
|
Returns a |
|
Returns a |
|
Returns a |
|
Returns the contents of the first XML node that matches the XPath expression. |
Read functions
Function |
Description |
---|---|
Reads data files on cloud storage and returns it in tabular format. |
|
Reads records from an Apache Kafka cluster and returns it in tabular format. |
|
Returns a table with records read from Kinesis from one or more streams. |
|
A table valued function for reading records from Pub/Sub from a topic. |
|
Returns a table with records read from Pulsar. |
|
Returns a table with rows that represent the metadata of a streaming query state. |
|
Returns records from the state store of streaming queries. |
Miscellaneous functions
Function |
Description |
---|---|
Returns an error if |
|
Returns |
|
Returns |
|
Returns the file-level state of an autoloader |
|
Returns the first non-null argument. |
|
Returns the list of available collations. |
|
Creates a multi-dimensional cube using the specified expression columns. |
|
Returns the current catalog. |
|
Returns the current schema. |
|
Returns the current Unity Catalog Metastore id. |
|
Returns a property for the current recipient in a view shared with Delta Sharing. |
|
Returns the current schema. |
|
Returns the user executing the statement. |
|
Returns the current version of Databricks. |
|
Returns the value matching the key. |
|
Returns the nth expression. |
|
Returns |
|
Returns a table of the refresh history for a materialized view, streaming table, or DLT pipeline. |
|
Returns the largest value of all arguments, skipping null values. |
|
Indicates whether a specified column in a |
|
Returns the level of grouping for a set of columns. |
|
Returns a hashed value of the arguments. |
|
Estimates number of distinct values collected in a HyperLogLog sketch. |
|
Combines two HyperLogLog sketches. |
|
Calls a method with reflection. |
|
Returns |
|
Returns |
|
Returns |
|
Returns the length in bytes of the block being read. |
|
Returns the start offset in bytes of the block being read. |
|
Returns the name of the file being read, or empty string if not available. |
|
Returns true if the current user is a member of group at the account level. |
|
Returns true if the current user is a member of group at the workspace level. |
|
Returns |
|
Returns |
|
Returns the smallest value of all arguments, skipping null values. |
|
Returns the keys in all or one scope which the user is authorized to see from Databricks secret service. |
|
Returns |
|
Returns monotonically increasing 64-bit integers. |
|
Returns |
|
Returns |
|
Returns |
|
Throws an exception with |
|
Returns a table of values within a specified range. |
|
Returns a table of values within a specified range. |
|
Calls a method with reflection. |
|
Extracts a secret value with the given |
|
Returns the user connected to Databricks. |
|
Returns the current partition ID. |
|
Returns the set of SQL keywords in Databricks. |
|
Separates |
|
Returns a log of changes to a Delta Lake table with Change Data Feed enabled. |
|
Calls a method with reflection, returning |
|
Extracts a secret value with the given |
|
Return a DDL-formatted type string for the data type of |
|
Returns the user executing the statement. |
|
Returns an universally unique identifier (UUID) string. |
|
Creates a hopping based sliding-window over a timestamp expression. |
|
Returns a 64-bit hashed value of the arguments. |
|
Returns the Apache Spark version. |