Replace Function in Snowflake: How to Use It

Formulas & functions · Updated November 22, 2026

The REPLACE function in Snowflake replaces every occurrence of a specified substring within a string with a new substring. Its syntax is REPLACE(subject, pattern, replacement), and it is case-sensitive by default. For example, SELECT REPLACE('snowflake', 'snow', 'ice') returns 'iceflake'.

What Is REPLACE and Why Does It Matter?

In SQL, string manipulation is a daily task—cleaning up data, standardizing formats, or redacting sensitive information. Snowflake’s REPLACE function is a built-in string function that performs a simple find-and-replace operation on text. It is part of Snowflake’s extensive set of string functions, alongside SUBSTR, CHARINDEX, and REGEXP_REPLACE. Unlike some databases that require complex expressions, Snowflake’s REPLACE is straightforward and optimized for large-scale data processing.

The function is particularly useful in data transformation pipelines, where you might need to normalize inconsistent values—for instance, replacing outdated country codes with new ones, or removing unwanted characters from user input. Because Snowflake is a cloud data warehouse, the function executes on the server side, so you don’t need to pull data to your local machine to perform replacements.

How the REPLACE Function Works

The basic syntax is:

REPLACE(subject, pattern, replacement)

All three arguments are required. The function scans the subject string for every non-overlapping occurrence of pattern and replaces it with replacement. If pattern is an empty string, the function returns the original subject unchanged. If replacement is an empty string, it effectively deletes all occurrences of pattern.

Here are a few concrete examples to illustrate:

  • SELECT REPLACE('Hello World', 'l', 'L') → 'HeLLo WorLd' (replaces every lowercase ‘l’ with uppercase ‘L’).
  • SELECT REPLACE('2023-01-01', '-', '/') → '2023/01/01' (changes date separators).
  • SELECT REPLACE('apple,banana,apple', 'apple', 'orange') → 'orange,banana,orange' (replaces all occurrences).
  • SELECT REPLACE('abc', '', 'X') → 'abc' (empty pattern returns original).
  • SELECT REPLACE('abc', 'b', '') → 'ac' (empty replacement deletes the pattern).

The function works with any string data type, including VARCHAR, CHAR, and even BINARY (though binary replacement is rarely used). It is case-sensitive: REPLACE('Snowflake', 'snow', 'ice') returns 'Snowflake' because the lowercase ‘snow’ does not match the uppercase ‘S’ in the subject. If you need case-insensitive replacement, you can combine it with LOWER or UPPER to normalize both subject and pattern.

REPLACE vs. REGEXP_REPLACE: Which One to Use?

Snowflake also offers REGEXP_REPLACE, which uses regular expressions for pattern matching. The key difference is that REPLACE only matches literal substrings, while REGEXP_REPLACE can match complex patterns like digits, word boundaries, or optional characters.

For example, to replace all digits in a string with ‘X’, you would use:

SELECT REGEXP_REPLACE('Order 12345', '[0-9]', 'X')

This returns 'Order XXXXX'. With REPLACE, you would need to know the exact digits to replace, which is impractical. On the other hand, REPLACE is faster for simple, fixed substitutions because it doesn’t incur the overhead of regular expression parsing. As a rule of thumb:

  • Use REPLACE when you have a literal substring to replace, like changing a specific product code.
  • Use REGEXP_REPLACE when you need pattern matching, like masking all phone numbers in a text.

Another related function is TRANSLATE, which replaces individual characters one-for-one. For instance, TRANSLATE('abc', 'ab', 'XY') returns 'XYc'. Unlike REPLACE, which replaces whole substrings, TRANSLATE maps each character in the second argument to the corresponding character in the third argument.

Common Use Cases and Examples

Here are some practical scenarios where you might use REPLACE in Snowflake:

  1. Data cleaning: Remove line breaks or tabs from imported text. For example, REPLACE(column, CHAR(10), ' ') replaces newline characters with spaces.
  2. Format standardization: Convert phone numbers from ‘(555) 123-4567’ to ‘555-123-4567’ by removing parentheses and spaces: REPLACE(REPLACE(phone, '(', ''), ') ', '-').
  3. Redaction: Mask email domains: REPLACE(email, '@example.com', '@domain.com').
  4. Unit conversion: Replace ‘kg’ with ‘lbs’ in a product description column.
  5. JSON cleanup: Replace escaped quotes in a JSON string stored as text.

You can also nest REPLACE calls to perform multiple replacements in one expression. For instance, to replace both commas and semicolons with a pipe in a string:

SELECT REPLACE(REPLACE('a,b;c', ',', '|'), ';', '|')

This returns 'a|b|c'.

Performance and Best Practices

Snowflake’s REPLACE is highly optimized, but there are a few things to keep in mind for large datasets:

  • Avoid using REPLACE on very large text columns (e.g., megabytes of text) in a WHERE clause, as it may slow down queries. Use it in SELECT or in a transformation step instead.
  • If you need to replace many different patterns, consider using REGEXP_REPLACE with a single pattern that matches all of them, rather than nested REPLACE calls.
  • When replacing in a column that is used frequently, consider creating a view or a materialized table with the replaced values to avoid repeated computation.
  • Remember that REPLACE returns NULL if any argument is NULL. For example, REPLACE(NULL, 'a', 'b') returns NULL. Use COALESCE if you need to handle NULLs gracefully.

Alternatives and Related Functions

Besides REPLACE and REGEXP_REPLACE, Snowflake provides other string functions that might suit your needs better:

  • SUBSTR and SPLIT_PART to extract parts of a string.
  • CONCAT or the || operator to build new strings.
  • TRANSLATE for character-level mapping.
  • TRIM, LTRIM, RTRIM to remove whitespace.
  • POSITION or CHARINDEX to find the location of a substring before deciding to replace it.

For example, if you only want to replace the first occurrence of a pattern, you can combine REPLACE with SUBSTR and CONCAT to manually build the result. However, Snowflake does not have a built-in ‘replace first occurrence’ function, so you’d need a workaround.

Frequently Asked Questions

Is REPLACE case-sensitive in Snowflake?

Yes, REPLACE is case-sensitive. To perform a case-insensitive replacement, you can normalize both the subject and pattern using LOWER or UPPER, but note that this will also change the case of the replacement. For example, to replace ‘snow’ regardless of case, you could use REPLACE(LOWER(col), 'snow', 'ice'), but the output will be all lowercase. For more sophisticated case-insensitive replacement, consider using REGEXP_REPLACE with the ‘i’ flag (e.g., REGEXP_REPLACE(col, 'snow', 'ice', 1, 0, 'i')).

Can REPLACE handle NULL values?

If any argument is NULL, the function returns NULL. For instance, REPLACE(NULL, 'a', 'b') returns NULL. If you want to treat NULL as an empty string, use REPLACE(COALESCE(col, ''), 'a', 'b').

What is the difference between REPLACE and TRANSLATE?

REPLACE replaces whole substrings, while TRANSLATE replaces individual characters. For example, TRANSLATE('abc', 'ab', 'XY') returns 'XYc' because ‘a’ becomes ‘X’ and ‘b’ becomes ‘Y’. REPLACE('abc', 'ab', 'XY') returns 'XYc' as well in this case, but if you try TRANSLATE('abc', 'a', 'XY'), it fails because the replacement string must be the same length as the pattern. REPLACE can replace a short pattern with a longer string.

How do I replace a newline character in Snowflake?

You can use REPLACE(col, CHAR(10), ' ') to replace newline (LF) with a space. For carriage return (CR), use CHAR(13). To remove both, nest: REPLACE(REPLACE(col, CHAR(13), ''), CHAR(10), ' ').

Does REPLACE work with numbers or dates?

No, REPLACE expects string arguments. If you pass a number or date, Snowflake will implicitly cast it to a string. For example, REPLACE(12345, '2', '9') returns '19345' because the number is converted to the string ‘12345’ before replacement. This can be handy, but be aware of implicit casting behavior.

Can I use REPLACE in a WHERE clause?

Yes, you can use REPLACE in a WHERE clause to filter rows based on a transformed value. For example, SELECT * FROM table WHERE REPLACE(name, ' ', '') = 'JohnDoe'. However, using a function on a column in a WHERE clause can prevent the use of indexes (Snowflake uses micro-partitions, so it’s less of an issue, but it still adds computation). It’s often better to filter on the original column if possible.

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.