Databricks SQL includes a broad set of built-in functions for working with text, numbers, dates, arrays, JSON, aggregates, and more. You can call them directly in SQL expressions—for example, SELECT upper(trim(customer_name)) FROM customers;—without installing a separate library; exact availability and behavior can depend on the Databricks runtime or SQL warehouse.
What built-in functions are
A built-in function is an operation provided by Databricks SQL and its Spark SQL foundation. It accepts inputs, called arguments, and returns a value or set of rows. Functions can appear in a SELECT list, a filter, a join condition, a grouping expression, or another function’s arguments. For instance, trim removes surrounding spaces, while upper changes text to uppercase.
Functions are not the same thing as SQL statements or operators. SELECT is a statement, and + is an operator; abs and coalesce are functions. Nor does “built-in” mean that every function is available in every runtime version. Databricks adds and updates functionality, so check the SQL language reference for the workspace and compute you use when a query depends on a particular function.
Built-ins also differ from user-defined functions (UDFs). A UDF is created by a user or organization to implement custom logic. A built-in is supplied by the platform. Both can be called in a query, but their ownership, portability, permissions, and execution characteristics may differ.
Common function categories
The catalog is large, so it is often more useful to start with the kind of data or task involved than to memorize function names. Common categories include:
- String functions:
lower,upper,trim,concat,substring, andregexp_extracthelp normalize, combine, select, or extract text. - Numeric and mathematical functions:
abs,round,ceil,floor, andsqrtoperate on numbers. Use decimal types and appropriate rounding when precision matters. - Date and time functions:
current_date,current_timestamp,date_trunc, anddatediffsupport date calculations and time-based grouping. - Conditional and null-handling functions:
coalesce,nullif, andiflet a query choose values or handle missing data. SQL also has theCASEexpression. - Aggregate functions:
count,sum,avg,min, andmaxsummarize groups of rows. - Complex-type and semi-structured functions: array, map, and JSON functions parse, inspect, transform, or expand nested data.
- Window functions:
row_number,rank, and aggregate functions used with anOVERclause calculate values across related rows without reducing each group to one row.
These categories overlap in real queries. A report might parse a JSON field, convert its date, group by month, and sum a numeric value. Each function handles one part of the transformation.
Examples in Databricks SQL
Functions are ordinary expressions, so they can be nested. This example produces a cleaned version of a name:
SELECT upper(trim(customer_name)) AS clean_name FROM customers;
For dates, date_trunc can align timestamps to a reporting period. For example, the following groups orders by month and calculates the total for each month:
SELECT date_trunc('MONTH', order_ts) AS month_start, sum(order_total) AS revenue FROM orders GROUP BY date_trunc('MONTH', order_ts);
The expression used in the SELECT list and the GROUP BY clause should represent the same grouping. If order_ts is a timestamp, the resulting month value remains a timestamp truncated to the start of that month. Check the column type and session time-zone behavior when interpreting timestamp boundaries.
Conditional functions can make a query more resilient. coalesce(discounted_price, list_price) returns the first non-NULL argument, which is useful when a fallback value is appropriate. It does not treat an empty string or zero as NULL; those values need separate handling if the business rule considers them missing.
NULLs, types, and date behavior
Many SQL functions return NULL when an input is NULL, though the precise rule is function-specific. Aggregates commonly ignore NULL values in their input: for example, sum adds non-NULL values, while count(*) counts rows and count(column_name) counts non-NULL values in that column. Knowing which version of a count you need prevents misleading totals.
SQL uses three-valued logic: a comparison involving NULL generally evaluates to unknown rather than true or false. Consequently, write IS NULL or IS NOT NULL to test for missing values; column = NULL is not the right test. coalesce is useful when substituting a default is logically correct, but it can conceal missing data if used indiscriminately.
Types matter as much as function names. A string that looks like a date is still text until parsed or cast. Implicit conversions can produce errors or unexpected results, so use explicit casts when the intended type is important and verify the result. For divisions that may have a zero denominator, a pattern such as numerator / nullif(denominator, 0) avoids dividing by zero by turning that denominator into NULL. Decide whether NULL is an acceptable result or should be handled separately.
Arrays, JSON, and nested data
Databricks SQL can work with complex values rather than flattening every record first. For an array, higher-order functions apply an expression to its elements. For example, transform(scores, x -> x * 2) returns an array with each score doubled. The lambda variable x represents the current element. Functions such as filter can retain elements that meet a condition.
JSON functions are useful when a column contains JSON text. from_json can parse that text into a structured value when given a schema; to_json serializes a value, and get_json_object can extract a value using a JSON path. Parsing into a declared schema is often preferable for repeated analysis because it makes expected fields and types explicit. Malformed input and fields that are absent from some records still need consideration.
To turn array elements into rows, use the table-valued function explode. For example, SELECT explode(array('red', 'blue')) AS color; returns one row for each array element. This changes the row count: a record with several elements can become several output rows. That is useful for analysis, but a later sum or count can be inflated if the query accidentally counts expanded rows as though each still represented one original record.
Aggregates and window functions
Aggregate functions collapse a group into a result. With a GROUP BY clause, sum(amount) returns a total for each group; without grouping, it summarizes the selected input as a whole. Every selected expression in a grouped query must either be grouped or be aggregated, subject to the SQL rules that apply to the query.
Window functions answer a different question: they calculate across a defined set of related rows while retaining individual rows. For example, row_number() OVER (PARTITION BY customer_id ORDER BY order_ts DESC) assigns a sequence number to each customer’s orders, newest first. Filtering for row number one is a common way to select a latest record, but ties and NULL timestamps require a deliberate ordering rule if the result must be deterministic.
Some aggregate functions can also be used as window functions by adding an OVER clause. The partition and frame determine which rows contribute to each result. A window total over a customer partition, for example, can show each order alongside that customer’s total, whereas a grouped total produces one row per customer.
How to find and choose the right function
Use the Databricks SQL language reference as the authoritative lookup for a function’s signature, argument types, return type, examples, and version notes. Search by task or category, then confirm whether the function is supported by the target SQL warehouse or runtime. A function name alone is not enough: two similar functions can have different argument order, NULL behavior, or return types.
Before using a function in a production query, consider these checks:
- Confirm the input and output types, including whether a conversion is implicit or explicit.
- Check what happens for NULL values, empty inputs, malformed text, and boundary dates.
- Test a small set of representative rows, including unusual or invalid cases.
- Look for a simpler built-in expression before creating a custom UDF, especially if portability or query optimization matters.
- Review the query plan and data volume when a function is applied to a large table or prevents effective filtering.
Function names can also be searched in the SQL editor’s documentation or in the official function reference. For details specific to an object in a workspace, Databricks SQL supports function-description commands such as DESCRIBE FUNCTION; consult the command reference for the exact syntax and the level of detail available in your environment.
Compatibility, performance, and common mistakes
Databricks SQL and PySpark expose overlapping capabilities, but their interfaces are not interchangeable. A SQL query uses SQL syntax, while PySpark code calls functions through the DataFrame API. Names, argument conventions, and available features can differ, even where both interfaces ultimately use Spark functionality. When translating a query between them, validate the result rather than assuming a one-to-one rewrite.
Another common misconception is that a function returning the right answer on a small sample will necessarily be efficient at scale. Scalar expressions are often straightforward, but repeated parsing, regular-expression work, or expansion of large arrays can add cost. Avoid applying expensive transformations before filters that could reduce the data, when the query’s meaning permits that order. Check the query profile and execution plan to understand where time and resources are spent.
Be careful with time zones and timestamp types. A timestamp displayed in a notebook or client may be formatted differently from its stored or interpreted value, and date truncation near midnight can change which reporting day or month a record belongs to. Establish the intended time zone and test records around boundaries, daylight-saving transitions, and month-end.
Finally, a built-in function is not automatically the best semantic choice. For example, replacing NULL with zero may make a calculation run but can change its meaning; extracting a JSON field as text may lose useful type information; and expanding an array may alter the grain of a dataset. Choose a function based on the data model and the question the query is meant to answer.
Frequently Asked Questions
Does Databricks SQL have built-in functions?
Yes. It provides built-ins for common SQL tasks, including string and numeric operations, date and time handling, aggregates, windows, and complex data. The official function reference documents supported functions and their signatures.
Are Databricks SQL functions the same as Spark SQL functions?
Databricks SQL builds on Spark SQL functionality, and many functions overlap. Availability and behavior can vary by Databricks release, SQL warehouse, or runtime, so check the documentation for the environment where the query will run.
How do I list or look up available functions?
Search the Databricks SQL function reference by name or category, and use the SQL editor’s function documentation where available. For function-specific details in a workspace, consult the syntax for DESCRIBE FUNCTION.
Can I use a SQL built-in function in PySpark?
Often there is a corresponding DataFrame API function, but you must use the PySpark interface and confirm its name, arguments, and behavior. SQL expressions and PySpark function calls are related, not identical syntaxes.
Should I use a built-in function or create a UDF?
Prefer a built-in when it expresses the required logic, since it is directly supported by the platform and is generally easier to read and maintain. Use a UDF when the needed logic is genuinely custom, and evaluate its compatibility and performance for your workload.
Related
- Bulls Eye 1-2-3 Data Sheet: Specs, Uses and Safety
- Y Balance Score: How to Calculate and Interpret It
- D&A on the Balance Sheet: Where It Appears and Why
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Find XLOOKUP in Excel and Use It in a FormulaTo find XLOOKUP in Excel for Microsoft 365 on Windows, open the Formulas tab and choose Lookup &…
- Excel Formula List: Essential Functions With ExamplesAn Excel formula list is a practical reference for choosing the right calculation: use =SUM(B2:B6) to total a…
- Date Difference in Minutes in Excel: Formulas That WorkIf A2 contains a start date and time and B2 contains an end date and time, enter =(B2-A2)*1440…
- JavaScript Return String From Function: How-To & ExamplesTo return a string from a JavaScript function, use the return keyword followed by the string value or…
- Excel LAMBDA in Name Manager: How to Create and Use ItYes. In Microsoft 365 Excel for Windows, you can save a LAMBDA formula as a defined name in…
- CLEAN in Excel: Formula, Examples, and Common LimitsIn Excel, use =CLEAN(A2) to remove nonprinting ASCII control characters from the text in cell A2. CLEAN can…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.