Databricks SQL String Functions: Uses and Examples

Formulas & functions · Updated November 22, 2026

Databricks SQL string functions let you join, search, extract, split, compare, and clean text values directly in a query. Common choices include concat and concat_ws for combining values, substring and split_part for extracting pieces, lower and trim for normalization, and regexp_extract or regexp_replace for pattern-based work.

What Databricks SQL string functions do

These built-in SQL functions operate on string expressions, such as table columns, literals, and the results of other functions. They are useful in a SELECT list to shape output, in a WHERE condition to filter rows, or in a join condition to standardize keys before comparing them. Databricks SQL uses Spark SQL syntax for many built-ins, though the functions available can depend on the Databricks SQL or Runtime version in use.

Functions fall into practical groups rather than one single category. Some manipulate text character by character; others locate a substring, apply a regular expression, or convert text to a structured array. Choosing a function that matches the kind of input matters: a literal delimiter is not the same thing as a regular-expression pattern, and trimming spaces is not the same as removing every kind of punctuation.

  • Combine: concat, concat_ws.
  • Measure and locate: length, instr, locate.
  • Extract and split: substring, split, split_part.
  • Normalize and replace: lower, upper, trim, replace, translate.
  • Use patterns: regexp_extract, regexp_replace.

Combining strings and handling nulls

Use concat when you want to place values next to one another, and concat_ws when you want a separator between values. The “ws” means “with separator.” For example, this query constructs a display name with a space between the two columns:

SELECT concat_ws(' ', first_name, last_name) AS full_name
FROM customers;

A useful difference is null handling. In Spark SQL, concat returns null if an input is null, while concat_ws skips null value arguments. So if first_name is null and last_name is Ng, the example can produce Ng rather than a null result. Empty strings are not null, however: they can still affect separators or the final output. If a null should mean a specific fallback, express that choice explicitly with a function such as coalesce.

For a fixed separator-free key, concat may be suitable. For a readable address, label, or delimited list, concat_ws is usually clearer. Avoid assuming that joining strings automatically inserts punctuation: the separator is supplied by you, and a separator that appears in source data may make the output ambiguous if you later need to parse it.

Finding text and measuring string length

instr(str, substr) searches for a substring within a string and returns its position; it returns zero when the substring is not found. locate(substr, str[, pos]) provides a similar search with the arguments in a different order and an optional starting position. These functions can help identify whether a value contains a marker or where a known prefix ends. For a simple yes-or-no check, a predicate such as contains may be easier to read where supported by your SQL environment.

SELECT email,
       instr(email, '@') AS at_position,
       length(email) AS text_length
FROM contacts;

Here, length reports the string length, including trailing spaces. For a binary value, length is measured in bytes; for text, do not confuse it with the number of bytes used to encode the text. Databricks also provides octet_length for byte length. That distinction matters with multibyte characters: a text value may occupy more bytes than its reported character length.

Search positions are useful for diagnostics, but they are not necessarily the best extraction tool. If the text format has a stable delimiter, split_part can state the intended piece directly. If it follows a variable pattern, a regular expression may be more appropriate. When the searched text may occur more than once, check whether you need the first occurrence, a later occurrence, or all matches before choosing a function.

Extracting text with substring and split_part

substring extracts part of a string using a starting position and, optionally, a length. Its positions are one-based, so position 1 refers to the first character. For example, substring('Databricks', 1, 3) returns Dat. This is convenient when a value has a fixed layout, such as a code whose first three characters always have a known meaning.

For a known literal delimiter, split_part is often more legible than calculating character positions. It takes the string, delimiter, and one-based part number:

SELECT split_part('north/east/central', '/', 2) AS region;

The result is east. This example is different from split: split_part selects one field using a literal delimiter, while split uses a regular expression as its delimiter and returns an array. If delimiters can be missing, repeated, or inconsistent, test those cases rather than assuming every row has the same number of parts. A malformed input may yield an empty value or otherwise behave differently from the well-formed example.

Using regular expressions for flexible patterns

Regular expressions are useful when the text to extract or change is described by a pattern instead of a fixed string. regexp_extract returns a selected capture group from a match; group 0 means the full match, and group 1 means the first parenthesized group. For example, to get the digits after the literal prefix order-:

SELECT regexp_extract('order-583', 'order-([0-9]+)', 1) AS order_number;

The result is 583. If there is no match, regexp_extract returns an empty string, not a SQL null. That difference can matter in downstream filters, counts, and joins. If you need to distinguish “no match” from a genuine empty extracted value, build that rule into the query rather than treating both as interchangeable.

regexp_replace replaces text matching a regular expression. For instance, regexp_replace(phone, '[^0-9]', '') removes characters other than digits from a phone-number string. By contrast, replace(str, search, replacement) works with a literal search string, so it is simpler when the target is exact text. Regular expressions provide flexibility, but their syntax needs care; use them only when the pattern requirement justifies the extra complexity.

Cleaning and normalizing values

Cleaning is often a combination of small, explicit operations. trim removes leading and trailing whitespace, while ltrim and rtrim target one side. lower and upper change letter case; initcap capitalizes the first letter of words. These functions can make inconsistent text easier to compare or display, but they do not validate whether a value is meaningful.

SELECT lower(trim(customer_email)) AS normalized_email
FROM customers;

This expression removes surrounding whitespace and converts the remaining text to lowercase. It does not remove spaces inside the address, repair misspellings, or establish that the address exists. Likewise, lowercasing a person’s name may be appropriate for a comparison key but not for a presentation field. It is often better to keep the original column and calculate a normalized expression for the specific purpose.

For literal character substitutions, use replace. For character-by-character translation, translate maps characters in one set to corresponding characters in another; characters with no corresponding destination can be removed. For example, punctuation removal may be expressible with translation when the character set is fixed, whereas a regular expression is more concise when the rule is “remove everything except these categories.” Do not apply broad cleanup to identifiers without checking whether punctuation or spaces carry meaning.

Splitting delimited text into pieces

split(str, regex[, limit]) divides a string using a regular-expression delimiter and returns an array. It is useful when a row contains a list or a variable number of fields that need further SQL processing. Because the delimiter is a regex, characters such as a period have special meaning unless escaped. A comma delimiter is straightforward; a delimiter such as a vertical bar should be treated with extra care because it has a special regex meaning.

The optional limit controls how many pieces are produced. A positive limit restricts the number of results, with the remaining text kept in the final piece. This can be useful when only the first few separators matter. If each record is expected to have a fixed number of fields, consider whether storing those fields as separate columns would be more reliable than repeatedly parsing a delimited string.

For one known field, prefer split_part when its literal-delimiter behavior matches the task. For an array of all pieces, use split. Keep in mind that an array is not the same as a row of separate columns: additional expressions are needed to select, transform, or expand its elements. Check the Databricks SQL documentation for the array operation you plan to use and for version-specific syntax.

Nulls, whitespace, and Unicode details

Null, empty string, and whitespace-only string are three different values. A null means the value is absent or unknown; '' is a present string with no characters; and ' ' contains spaces. trim can turn the last example into an empty string, but it does not turn it into null by itself. This distinction affects grouping and filtering, so define what “missing text” means for the dataset before standardizing it.

Text can also contain characters that look alike but have different Unicode representations, combining marks, or characters outside basic English. A function that changes case or counts length does not necessarily perform every normalization a human reader expects. If data comes from multiple languages or systems, test representative values from those scripts. For reliable identity matching, a simple lower(trim(value)) rule may be insufficient; the right normalization depends on the data and the matching requirement.

Collation and comparison behavior can also affect text matching. Do not assume that every query, column, or environment treats case and accents identically. When case-insensitive comparison is required, make the rule explicit or use a supported collation appropriate to the Databricks environment. Validate the behavior with a small query and confirm that the chosen rule is consistent between filtering, joins, and any persisted comparison key.

Choosing functions and avoiding common mistakes

A practical selection process is to start with the shape of the problem, then test edge cases. Use a literal function when the target is exact text; use a regular expression only when you need a pattern. Prefer a function that expresses the intended operation directly instead of layering position calculations that are difficult to maintain.

  • For combining optional fields, decide whether null inputs should be skipped or should make the result null.
  • For splitting, decide whether the delimiter is literal or a regular expression and whether the delimiter can appear inside the data.
  • For extraction, test missing markers, repeated markers, empty fields, and unexpected formats.
  • For comparison, distinguish case normalization from trimming, punctuation removal, and Unicode normalization.
  • For output, preserve source values when cleaning could discard meaningful information.

Text expressions in a filter or join are evaluated as part of the query, and complicated regular expressions can be more expensive than simple literal operations. The actual impact depends on the query plan, data size, and storage layout, so inspect the plan and measure representative workloads instead of assuming a function is always costly or always free. If a normalized key is used repeatedly, it may be worth producing it during a well-defined transformation step, while retaining the original value for traceability.

Frequently Asked Questions

What is the difference between concat and concat_ws in Databricks SQL?
concat joins expressions directly and returns null if an input is null. concat_ws inserts a supplied separator and skips null value arguments, making it useful for optional name or address parts.

Does substring start at zero or one?
Databricks SQL’s substring positions are one-based: position 1 is the first character. For example, substring('cloud', 2, 3) returns lou.

How do I replace a literal string rather than a regex match?
Use replace(value, search, replacement) for a literal search string. Use regexp_replace when the search rule is a regular expression, such as matching a range of characters.

Why does regexp_extract return an empty string?
It returns an empty string when the pattern does not match. It can also return an empty value when the selected capture group matches no characters, so check the match condition if those cases need different treatment.

How can I remove spaces from both ends of a value?
Use trim(value) to remove leading and trailing whitespace. It does not remove spaces inside the value; use a separate replacement rule only if internal spaces should actually be changed.

Should I use split or split_part?
Use split_part to retrieve one field using a literal delimiter. Use split when you need the pieces as an array or need a regex delimiter. Confirm the delimiter and malformed-input behavior against your data.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.