Snowflake String Functions: Examples and Practical Uses

Formulas & functions · Updated November 22, 2026

Snowflake string functions let you combine, inspect, extract, search, and transform text in SQL. For example, use CONCAT(first_name, ' ', last_name) to join name fields, TRIM(city) to remove surrounding whitespace, and SPLIT_PART(path, '/', 2) to extract a delimited field.

What Snowflake string functions do

A string function takes text as input and returns a value, often another string. These functions are useful in queries, views, and data-cleaning steps when source data is inconsistent or stored in a format that is inconvenient for analysis. Snowflake also offers the || operator for concatenation and operators such as LIKE for matching text against a pattern.

String work is not limited to formatting names. It can include normalizing case before a comparison, extracting a code from a longer identifier, removing punctuation from a phone number, or checking whether a value follows a required pattern. The appropriate function depends on whether the task is literal, positional, or pattern-based.

  • Literal changes: REPLACE substitutes one exact sequence of characters for another.
  • Position-based work: SUBSTR extracts text by position, while POSITION locates a substring.
  • Delimited text: SPLIT_PART returns one field from a delimited string.
  • Pattern-based work: REGEXP_REPLACE and REGEXP_LIKE use regular expressions.

Common functions by task

Knowing a few groups of functions makes Snowflake SQL easier to read. Function names are generally descriptive, but choosing a simple function instead of a complicated expression also makes a transformation easier to maintain.

  • Change case: UPPER, LOWER, and INITCAP return uppercase, lowercase, or initial-capitalized text.
  • Measure: LENGTH returns the character length of a string.
  • Trim: TRIM, LTRIM, and RTRIM remove specified characters from both ends, the left, or the right.
  • Extract: SUBSTR (also called SUBSTRING), LEFT, and RIGHT return portions of a string.
  • Replace: REPLACE handles literal text, while TRANSLATE maps individual characters from one set to another.
  • Search: POSITION finds a substring, and LIKE or ILIKE checks a pattern.
  • Split: SPLIT returns an array of pieces; SPLIT_PART returns one piece as a string.
  • Format: LPAD and RPAD add padding to the left or right to reach a requested width.

For example, with no table needed, SELECT SUBSTR('Snowflake', 1, 5); returns Snowf. Snowflake string positions are counted from 1 in functions such as SUBSTR and POSITION; POSITION returns 0 when the searched text is not found.

Combining strings and handling NULL values

Use CONCAT or the || operator to join strings. For example, SELECT 'Snow' || 'flake'; returns Snowflake. With columns, CONCAT(first_name, ' ', last_name) places a space between the two values.

A key detail is how missing values behave. In Snowflake, CONCAT returns NULL if any input expression is NULL; concatenation with || also propagates NULL. That may be appropriate when the full value is required, but it can surprise you when a name or address has an optional component. Use COALESCE when you deliberately want a substitute for NULL:

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

This expression avoids a NULL result, but it can leave extra spaces if one name part is missing. If that matters, trim the completed value or build the output with explicit conditions. Decide whether NULL means unknown, not supplied, or not applicable before replacing it with an empty string; those meanings are not interchangeable in reporting.

An empty string is also different from NULL in Snowflake. A value of '' has no characters, while NULL represents the absence of a value. Functions may treat those inputs differently, so test both if a cleaning rule must cover both cases.

Extracting and splitting text

Use SUBSTR when a field has a stable character position, such as a fixed-width code. Its basic form is SUBSTR(string, start, length). For example, SELECT SUBSTR('AB-4921', 4, 4); returns 4921. If the format can vary, fixed positions can silently extract the wrong characters; a delimiter or regular expression may be a better choice.

For delimiter-separated values, SPLIT_PART is often more readable. It takes the string, delimiter, and field number:

SELECT SPLIT_PART('north|west|coast', '|', 2) AS region;

The result is west. The delimiter is a literal string, not a regular-expression pattern. Use SPLIT instead when you need the pieces as an array rather than one selected field. This distinction matters when later SQL needs to process every part, instead of simply selecting a known field.

For a simple delimiter in a known format, prefer SPLIT_PART over a regular expression: it makes the intent clear. For irregular text, changing delimiters, or optional sections, regular expressions can be more flexible, though they are harder to maintain. Neither method validates that the original value is well formed; validation is a separate requirement.

Cleaning, normalizing, and changing case

TRIM is a common first step for imported text. With no character argument, it removes whitespace from the beginning and end of a string. LTRIM and RTRIM apply the same idea to one side. Trimming does not remove spaces in the middle, so TRIM(' North Bay ') returns North Bay, not NorthBay.

When you provide characters to trim, Snowflake treats them as characters to remove from the ends, not as one exact phrase to search for. To replace a literal substring anywhere in a value, use REPLACE. For example, REPLACE('AB-12-34', '-', '') returns AB1234. Use TRANSLATE when the job is a character-by-character mapping rather than replacement of a longer sequence.

Case conversion can make comparisons more consistent: LOWER(email) produces a lowercase version, while UPPER(code) produces uppercase text. INITCAP capitalizes words, but it is not a universal name-correction tool; names, abbreviations, and product labels can have unusual capitalization. Preserve the source value if its original spelling matters, and apply a normalized version only for the task that needs it.

Searching text and using regular expressions

For a simple wildcard search, use LIKE. The percent sign (%) matches any sequence of characters, and underscore (_) matches one character. ILIKE performs a case-insensitive pattern match. For instance, product_name ILIKE '%snow%' can match values containing Snow, snow, or another case variation. A wildcard at the beginning is convenient, but can be more expensive to evaluate on large data than a selective prefix or exact comparison.

Use POSITION('flake', product_name) when you need the location of a literal substring rather than a true/false pattern match. Regular expressions are appropriate when a rule has structure, such as removing every character except digits:

SELECT REGEXP_REPLACE('Call 212-555-0100', '[^0-9]', '') AS digits;

The result is 2125550100. Snowflake regular-expression functions use POSIX-style regular expressions. One useful behavior to know is that REGEXP_LIKE treats the pattern as a match for the whole input, effectively anchoring it at both ends. To allow additional characters around a desired pattern, account for them in the expression. Regex is powerful, but for a fixed delimiter or exact substitution, a basic string function is usually clearer.

Choosing the right function and avoiding mistakes

Start by stating the transformation in plain language: exact replacement, extraction by position, split at a delimiter, or match against a rule. Then choose the narrowest function that expresses it. That approach reduces accidental matches and makes the SQL easier for someone else to review.

  • Use REPLACE for literal text, not for a pattern; use REGEXP_REPLACE when the pattern genuinely varies.
  • Use SPLIT_PART for a predictable delimiter and a known field number; do not assume it validates the number of fields.
  • Use TRIM for leading and trailing whitespace; use another explicit transformation if internal spaces or tabs must change.
  • Handle NULLs intentionally before concatenating or comparing values.
  • Check case rules: LIKE is case-sensitive, while ILIKE is case-insensitive.
  • Test examples with punctuation, empty strings, unexpected delimiters, and non-ASCII characters if those can occur in the data.

String length is not the same as the number of bytes used to store text, and a visible symbol can consist of multiple Unicode code points. Consequently, LENGTH is useful for character-oriented checks but should not be mistaken for a display-width or byte-size measurement. If downstream systems impose a byte limit, use the relevant encoding and storage requirements rather than assuming a character count is enough.

Performance and query design

String functions do not carry a separate per-function fee. Snowflake usage is primarily tied to the compute and other services used by a workload, so a more complex expression can matter if it increases warehouse work or query duration. The actual impact depends on data volume, warehouse size, filters, and the query plan; benchmark representative data rather than assuming every string operation is costly.

Applying a function to a table column in a filter, such as WHERE LOWER(email) = 'a@example.com', can make it harder for Snowflake to use micro-partition metadata to narrow the scan than a filter aligned with the stored values. This is not a guarantee that pruning will fail: inspect query behavior and profile for the real workload. If a normalized value is frequently queried, consider maintaining it as a deliberate derived column or normalizing consistently at ingestion. Keep the original when auditability or original formatting is important.

For large transformations, avoid doing the same expensive conversion repeatedly in multiple expressions if a clearer staged query can calculate it once. Also be careful about moving a filter or transformation if it changes which rows qualify. Correct results come first; optimize only after checking the output and measuring the query.

Frequently Asked Questions

What is the Snowflake function for joining strings?

Use CONCAT or the || operator. Both concatenate text, and a NULL input can make the combined result NULL. Use COALESCE when a missing component should be replaced deliberately.

How do I split a string in Snowflake?

Use SPLIT_PART(text, delimiter, field_number) to return one field, or SPLIT(text, delimiter) to return an array of fields. Choose based on whether you need one scalar value or all the pieces.

How do I remove spaces at the start and end of text?

Use TRIM(column_name) to remove surrounding whitespace. It leaves spaces inside the value unchanged. Use LTRIM or RTRIM when only one side should be trimmed.

Is LIKE case-sensitive in Snowflake?

Yes. LIKE is case-sensitive; use ILIKE for a case-insensitive pattern match. For exact comparisons, choose a consistent case-normalization approach if case should not distinguish values.

How can I remove punctuation from a string?

Use REGEXP_REPLACE with a pattern describing the characters to remove. For example, REGEXP_REPLACE(value, '[^0-9]', '') keeps digits only. Confirm that this is appropriate for the data, since it also removes meaningful symbols such as a plus sign or extension marker.

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.