Databricks string functions are built-in SQL and PySpark operations for cleaning, combining, searching, splitting, and extracting text. In Databricks SQL, you can use functions such as concat, trim, substring, and regexp_replace in a query; in PySpark, corresponding functions are available through pyspark.sql.functions.
What string functions do in Databricks
Databricks uses Apache Spark to process data, so many of its text functions follow Spark SQL behavior. They work on string columns in tables and DataFrames, and can be combined with filters, joins, aggregations, and other expressions. They are useful for standardizing imported data, preparing identifiers, parsing fields, and matching text without moving the data into a separate application.
A function usually takes one or more values as arguments and returns a value for each row. For example, lower(customer_name) returns a lowercase version of a name, while instr(email, '@') reports where the at sign first appears. The output can be used directly in a query or assigned to a new DataFrame column.
Databricks has both SQL and language APIs. The SQL function names and PySpark function names are often similar, but the syntax is different: SQL expressions are written in a query, while PySpark expressions are composed as column operations. The underlying Spark execution model lets these expressions be optimized and run across the data rather than applying ordinary Python code one record at a time.
Common functions for combining and changing text
These functions cover many everyday transformations:
concatjoins values together. For example,concat(first_name, ' ', last_name)puts a space between two name columns.concat_wsjoins values using a separator.concat_ws('-', region, store_id)creates a hyphen-separated value; null input values are skipped.lowerandupperchange letter case. They can help standardize values before comparisons, though they do not remove spaces or punctuation.initcapchanges text to an initial-capital style. It may not produce the capitalization a person expects for every name or title, so treat it as a formatting transformation rather than a spelling authority.replacesubstitutes a literal substring. For instance,replace(phone, '-', '')removes hyphens from a phone number stored as text.lengthreturns the length of a string. Spark SQL counts trailing spaces in a string, which matters when checking padded or fixed-width data.
For example, a query could produce a normalized key with lower(concat_ws('-', country_code, account_id)). The choice to lowercase, add a separator, or remove punctuation should follow the rules for that identifier; it is not automatically safe to transform every text field the same way.
Trimming and standardizing values
Whitespace around a value is a common source of mismatched joins and duplicate-looking categories. trim(value) removes spaces from both ends, while ltrim(value) and rtrim(value) target the left and right ends. These operations do not remove spaces between words. Thus, trim(' North Shop ') becomes North Shop, but the internal space remains.
Basic trimming is not a complete cleanup policy for every source. Tabs, line breaks, nonbreaking spaces, and other Unicode whitespace can behave differently from an ordinary space. If the input contains such characters, inspect representative values and use a regular-expression replacement where appropriate. For example, a pattern such as '^\\s+|\\s+$' can target whitespace at the start or end with regexp_replace(value, pattern, ''). Regular-expression behavior follows Java regular-expression syntax, so verify the pattern against the actual data.
A sensible normalization sequence might trim a value, convert it to a consistent case, and then apply a clearly defined replacement. Keep the raw source column if the original spelling is needed for display or audit purposes. Normalized text can be useful for matching, but it can also collapse distinct values—for example, case-sensitive codes should not be lowercased unless the code system treats case as irrelevant.
Finding text, taking substrings, and splitting fields
Use instr(string, substring) to find the first occurrence of a piece of text. Its result is a position beginning at 1; if the substring is not found, the result is 0. locate offers a similar search, but its arguments are ordered as the substring first and the string second. That argument-order difference is an easy source of bugs.
substring(string, position, length) extracts part of a string. Positions in Spark SQL start at 1, not 0. For example, substring(product_code, 1, 3) takes the first three characters. This is appropriate when the field has a reliable fixed layout; it is fragile when delimiters or field lengths can vary.
For delimiter-based data, split returns an array. In Databricks SQL, element_at(split(email, '@'), 1) returns the first piece of an email address, while element_at(split(email, '@'), 2) returns the second. This simple example assumes the data contains the expected delimiter and structure. Validate malformed rows before relying on a particular array position, and remember that the delimiter passed to split is a regular expression, not always a literal character.
When the goal is to retain text before or after a delimiter, substring_index(string, delimiter, count) can be more direct. For instance, substring_index(file_path, '/', -1) returns the portion after the final slash. It is often clearer than calculating character positions by hand, especially when the delimiter may appear more than once.
Regular-expression functions
Regular expressions are useful when a field has a pattern rather than a simple fixed delimiter. regexp_replace replaces text that matches a pattern, and regexp_extract returns a capture group from a match. For example, regexp_extract(email, '^([^@]+)@([^@]+)$', 1) extracts the portion before the at sign when the value matches the expected email-like structure. Group numbers start at 1 for the first capturing group; group 0 refers to the full match.
A notable behavior is that regexp_extract returns an empty string when there is no match or the requested group does not match. An empty result is not the same as SQL NULL. If downstream logic needs to distinguish missing, malformed, and valid values, build that distinction explicitly with conditions rather than assuming a failed extraction becomes null.
Regex patterns can be harder to read and maintain than literal string operations. Use replace for a literal substitution and regexp_replace only when pattern matching is needed. Test patterns against ordinary examples, edge cases, and malformed input. Escaping matters: a period in a regex matches any character unless escaped, whereas a literal period is often intended in file names or domain-like text.
Using string functions in SQL and PySpark
In a SQL query, functions can be used in a SELECT, a filter, or a computed expression. For example: SELECT trim(lower(customer_name)) AS normalized_name FROM customers. A function can also appear in a join condition, but applying transformations to both sides of a join may affect performance and can obscure whether the underlying keys are consistent. When possible, create a well-defined normalized key upstream and use it consistently.
In PySpark, import the functions module and compose expressions using columns. The equivalent of trimming and lowercasing a name is F.lower(F.trim(F.col('customer_name'))), after from pyspark.sql import functions as F. A derived column can be assigned with df.withColumn('normalized_name', F.lower(F.trim(F.col('customer_name')))). Functions such as F.concat_ws, F.substring, and F.regexp_replace follow the same general pattern.
For DataFrame work, prefer these built-in column functions to a Python UDF when a built-in operation can express the transformation. Built-ins give Spark more information about the operation, which can allow the engine to optimize execution. A UDF may still be appropriate for specialized logic, but it adds implementation and maintenance considerations and can be less efficient. In notebooks, SQL cells and PySpark cells can also be used side by side, so choose the form that makes the transformation easiest for the team to review.
Nulls, Unicode, and other details to check
Null handling differs among functions and can change the result of a transformation. In Spark SQL, concat returns null if an input argument is null; concat_ws skips null values among the strings it joins. A missing name can therefore make a concatenated full name null, while a separator-based composite may retain the non-null part. Decide whether a missing component should be omitted, replaced with a placeholder, or cause the whole output to remain null.
Do not confuse a null with an empty string or a string containing spaces. They can display similarly in a table preview but behave differently in comparisons, counts, and joins. Use explicit null checks, and consider whether trimming an all-space value should result in an empty string or be converted to null as a separate cleanup rule.
Text length and appearance can also differ for Unicode data. A displayed symbol may be composed of multiple code points, such as a base character combined with an accent or an emoji with a modifier. Consequently, a length function is not a reliable measure of what a reader perceives as a single displayed character. Likewise, lowercasing does not perform every possible Unicode normalization. If identifiers must compare consistently across languages or systems, establish a normalization standard and test it with representative data.
Databricks SQL and Databricks Runtime evolve, and available syntax can depend on the environment and version. If a function or optional argument is unfamiliar, check the Databricks SQL function reference for the warehouse or runtime in use. Also consider the data type: values that look like numbers may need to remain strings when leading zeros are significant, as with postal codes or product identifiers.
A practical way to choose a function
Start from the shape of the input and the required result, rather than choosing a function by name alone. A short checklist helps prevent brittle parsing:
- For a literal join, choose
concatorconcat_ws, and decide what null components should do. - For unwanted edge spaces, try
trim; use regex cleanup only if the data includes whitespace or patterns that basic trimming does not handle. - For a known character position, use
substring; for a delimiter, considersplitorsubstring_index. - For a literal search or replacement, use
instrorreplace; reserve regex functions for variable patterns. - Test empty strings, nulls, missing delimiters, repeated delimiters, and non-ASCII examples before applying a transformation to a full dataset.
For a repeatable data-cleaning pipeline, document the intended output and retain enough information to diagnose exceptions. A parsing rule that works for today’s sample can fail when upstream systems add a delimiter, change capitalization, or introduce a new character. Checking counts of null, empty, and unmatched results after a transformation is a simple way to notice those changes early.
Frequently Asked Questions
What are the most-used string functions in Databricks?
Common choices include concat, concat_ws, trim, lower, upper, length, substring, instr, split, replace, and the regex functions regexp_replace and regexp_extract. The best choice depends on whether the task is literal text handling, positional extraction, or pattern matching.
Does Databricks use SQL or PySpark string functions?
It supports both. SQL functions are written in Databricks SQL expressions, while PySpark functions are generally called through pyspark.sql.functions and applied to DataFrame columns. Many operations have equivalents in both forms, though their syntax differs.
Is split in Databricks based on a literal delimiter?
Not necessarily. The delimiter argument to split is treated as a regular expression. A delimiter that has a special regex meaning, such as a period or a vertical bar, must be escaped or represented with a suitable pattern to match literally.
Why does regexp_extract return an empty value?
It returns an empty string when the regular expression does not match or the requested capture group has no match. That behavior differs from returning SQL null, so check the match condition or convert the result explicitly if your pipeline needs a different representation.
Are Databricks string positions zero-based?
For Spark SQL functions such as substring and the position returned by instr, positions begin at 1. This differs from indexing conventions in some programming languages, so pay attention when translating logic between SQL and application code.
Related
- Excel Goal Seek on Multiple Cells Explained
- JavaScript split() to Array: How It Works and Examples
- Balance of Payments: Meaning, Accounts, and Examples
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Day Timeline: Build a Daily Schedule or Date FilterAn Excel day timeline can mean either a daily schedule laid out across worksheet columns or a PivotTable…
- Excel Formula for Adding Days to a Date: ExamplesTo add days to a date in Excel, add the number of days to the date cell: with…
- Transpose in Excel Using a Formula: Step-by-Step GuideTo transpose a range in Microsoft Excel for Microsoft 365, switch its rows and columns with the TRANSPOSE…
- Half Day Attendance Formula in Excel: Examples and SetupFor a row of attendance codes in B2:F2, where P means present and H means half day, use…
- Excel XIRR With Dates: Formula, Examples, and FixesTo calculate a return from cash flows that happen on specific, uneven dates, use Excel’s XIRR function: with…
- Java String Functions: Methods, Examples, and GuideOn W3Schools, “Java string functions” refers mainly to methods of Java’s String class, such as length(), toUpperCase(), indexOf(),…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.