Snowflake SQL String Functions: Examples and Usage Guide

Formulas & functions · Updated November 22, 2026

Snowflake SQL string functions let you combine, inspect, search, split, extract, and clean text values in queries. Common choices include CONCAT or || for joining strings, SUBSTR and SPLIT_PART for extracting text, TRIM and REPLACE for cleanup, and regular-expression functions for pattern-based work.

How Snowflake handles strings

Snowflake stores ordinary text in VARCHAR values, and most string functions take a string expression and return another string, a number, or an array. You can use them in a SELECT list, a filter, a join condition, or a larger expression. For example, a function can standardize an email address for display, or help compare two values that arrived in different formats.

Many functions accept expressions rather than just literal text, so you can combine fields and calculations. Snowflake can convert some non-string inputs implicitly, but explicit conversion with TO_VARCHAR makes intent clearer when a value is numeric, a date, or a semi-structured value. Format choices matter: converting a date or number to text is not the same as changing the underlying value.

String operations are not automatically language-aware. Case conversion, sorting, and comparisons can depend on the expression’s collation, and default behavior may not match every language’s rules. If locale-specific sorting or case behavior matters, check Snowflake’s collation support rather than assuming that a basic uppercase or lowercase conversion handles every linguistic edge case.

Joining and formatting text

Use CONCAT or the || operator to join strings. Both are useful for building labels, identifiers, or display values. The following query puts a space between two name fields:

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

Snowflake’s CONCAT returns NULL if an input argument is NULL. That can surprise you when one name field is missing: instead of a partial name, the whole expression may become null. Use COALESCE when you want to substitute a value for missing text. With first and last names in their respective columns, this example substitutes an empty string for either null:

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

The result can still contain an extra space if one name is absent, so trim the completed expression when that is undesirable. Choose a replacement that fits the data: converting a missing middle name to an empty string may be sensible, while replacing a missing account identifier with an arbitrary value could cause incorrect matches.

For fixed-width presentation, LPAD and RPAD add characters on the left or right up to a requested length. They can format codes for display, but padding a value is not a safe way to change its meaning or guarantee a unique identifier. If the source is longer than the requested output width, verify the function’s behavior for your intended use before relying on the result.

Measuring, locating, and extracting substrings

LENGTH returns the number of characters in a string. CHAR_LENGTH is also available for character length, while OCTET_LENGTH reports the number of bytes. These measures can differ for non-ASCII text because one visible character may use multiple bytes in UTF-8. A combining mark can also make what a person sees as one displayed character consist of more than one character in the underlying text.

To extract text by position, use SUBSTR (also written SUBSTRING), LEFT, or RIGHT. For instance, LEFT(product_code, 3) returns the first three characters, while RIGHT(product_code, 4) returns the last four. SUBSTR gives more control over the starting position and length; a negative starting position counts from the end of the string.

For a known delimiter, SPLIT_PART is often easier to read than calculating character positions. Its part number is one-based, and negative numbers count from the end. In this example, with email addresses in an email column, the query returns the text after the first at sign:

SELECT SPLIT_PART(email, '@', 2) AS email_domain FROM customers;

That expression is convenient, but it does not validate that the source is a well-formed email address. A missing delimiter, multiple delimiters, or malformed input may produce a value that should not be treated as verified data. POSITION can check whether a substring occurs and returns its one-based position, or zero if it is not found. Pair such checks with extraction when data quality is uncertain.

Changing case and cleaning text

LOWER and UPPER convert text to lowercase and uppercase. INITCAP applies title-style capitalization. These are useful for display or basic normalization, but they do not prove that two values refer to the same person or entity. Names, product labels, and abbreviations may have intentional capitalization that a generic conversion changes.

TRIM removes leading and trailing spaces by default; LTRIM and RTRIM target one side. Trimming does not remove spaces between words. For example, trimming ' North Shore ' removes the outside spaces but leaves the two spaces between “North” and “Shore.” If internal whitespace must be standardized too, use a replacement or regular-expression function suited to the input.

REPLACE performs literal text replacement. It is a good fit when the text to find is known exactly, such as changing a separator or removing a fixed prefix. It is not a regular expression: pattern characters such as a period are treated as ordinary characters. For character-by-character substitutions, TRANSLATE can map characters from one set to another; for more complex rules, use a regex function.

Splitting strings and working with arrays

SPLIT divides a string at a delimiter and returns an array, rather than returning one selected piece as SPLIT_PART does. This is useful when the number of segments varies or when later SQL should work with the resulting collection. Snowflake also provides array functions for inspecting and transforming arrays, so splitting is often one step in a larger workflow.

Choose between the two functions based on the shape of the result you need:

  • Use SPLIT_PART(value, delimiter, n) when you need one known segment in a query result.
  • Use SPLIT(value, delimiter) when you need the set of segments as an array for subsequent processing.
  • Check how the source handles repeated or missing delimiters before using a segment as a trusted key or category.

For example, splitting a comma-separated tags field does not by itself clean spaces around each tag, validate the tags, or guarantee that commas were not part of a tag value. The format may need a proper data model or a defined escaping rule if delimiters can occur inside values.

Searching and pattern matching

For simple searches, use LIKE or ILIKE in a predicate. The percent sign (%) matches any sequence of characters, and the underscore (_) matches one character. ILIKE provides case-insensitive matching. These operators answer whether a value matches a pattern; they do not return a transformed string.

When the pattern is more complex, Snowflake supplies regular-expression functions such as REGEXP_REPLACE, REGEXP_SUBSTR, and REGEXP_INSTR. For example, with telephone text in a phone column, this expression removes characters other than digits:

SELECT REGEXP_REPLACE(phone, '[^0-9]', '') AS digits_only FROM contacts;

Snowflake’s regular-expression support follows POSIX extended regular-expression conventions, so syntax from a different regex engine may not work exactly as expected. Test patterns against representative inputs, especially for punctuation, Unicode text, and empty values. A pattern that removes formatting characters is not the same as validating a phone number or determining whether it can be dialed.

Nulls, Unicode, and common pitfalls

Before applying a string function, consider whether its input can be NULL. Many functions return null when their input is null, and concatenation specifically propagates a null argument. COALESCE lets you choose a fallback, while NULLIF can turn a particular value into null. Do not replace nulls automatically without deciding what missing text means in the analysis.

Another common misconception is that “character” always means one visible symbol. UTF-8 text can use multiple bytes for a character, and a displayed symbol can be formed from multiple code points. Consequently, length and position operations should be tested with the actual languages and symbols in your data if they are used to truncate names, calculate limits, or create identifiers.

Case normalization and trimming also do not solve every comparison problem. Two strings can look alike while differing in accents, Unicode composition, invisible characters, or punctuation. For sensitive matching, inspect representative raw values and define a deliberate normalization rule; do not assume LOWER(TRIM(value)) makes all equivalent-looking text identical.

Choosing functions and managing query work

Start with the simplest function that expresses the rule. Use literal operations for known delimiters and exact text, and regular expressions only when a genuine pattern is needed. Simpler expressions are generally easier for another analyst to review and less likely to encode assumptions that the source data does not satisfy.

  • Joining fields: CONCAT or ||, with explicit null handling if needed.
  • Extracting a fixed section: SUBSTR, LEFT, RIGHT, or SPLIT_PART.
  • Cleaning known text: TRIM, REPLACE, or TRANSLATE.
  • Matching a simple pattern: LIKE or ILIKE; matching a richer pattern: a regex function.

String functions do not have a separate per-function price, but running transformations uses query compute. Applying a function to a column in a filter may also make it harder for Snowflake to use micro-partition metadata to eliminate data, depending on the expression and table layout. For frequent, large-scale workloads, compare query profiles and consider storing a consistently normalized value during data preparation. Avoid assuming that an expression is a bottleneck without measuring it.

When several queries need the same cleanup rule, centralizing it in a view or an ingestion/transformation step can make behavior consistent. Keep the original value when it is useful for auditability, and document the normalization rule. A cleaned string can be appropriate for searching or grouping, but retaining the source helps explain unexpected matches and supports later changes to the rule.

Frequently Asked Questions

What is the Snowflake function for concatenating strings?
Use CONCAT(value1, value2) or the || operator. Remember that a null argument makes a CONCAT result null; use COALESCE when a fallback is appropriate.

How do I get text before or after a delimiter?
Use SPLIT_PART(value, delimiter, part_number). Part numbers start at one, so the first segment is 1 and the second is 2. Check the source first if the delimiter may be absent or repeated.

How do I remove spaces in Snowflake?
TRIM(value) removes leading and trailing spaces, not spaces inside the text. Use REPLACE for a known literal sequence, or a suitable regular expression when you need to normalize a broader whitespace pattern.

What is the difference between LENGTH and OCTET_LENGTH?
LENGTH counts characters, while OCTET_LENGTH counts bytes. They can differ for UTF-8 text, so use the measure that matches the actual requirement.

How can I make a string search case-insensitive?
For wildcard pattern matching, use ILIKE instead of LIKE. For exact comparisons or more specialized behavior, consider the relevant collation and test it with your data rather than assuming a pattern operator applies everywhere.

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.