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
REPLACEwhen you have a literal substring to replace, like changing a specific product code. - Use
REGEXP_REPLACEwhen 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:
- Data cleaning: Remove line breaks or tabs from imported text. For example,
REPLACE(column, CHAR(10), ' ')replaces newline characters with spaces. - Format standardization: Convert phone numbers from ‘(555) 123-4567’ to ‘555-123-4567’ by removing parentheses and spaces:
REPLACE(REPLACE(phone, '(', ''), ') ', '-'). - Redaction: Mask email domains:
REPLACE(email, '@example.com', '@domain.com'). - Unit conversion: Replace ‘kg’ with ‘lbs’ in a product description column.
- 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
REPLACEon 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_REPLACEwith a single pattern that matches all of them, rather than nestedREPLACEcalls. - 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
REPLACEreturns NULL if any argument is NULL. For example,REPLACE(NULL, 'a', 'b')returns NULL. UseCOALESCEif 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:
SUBSTRandSPLIT_PARTto extract parts of a string.CONCATor the||operator to build new strings.TRANSLATEfor character-level mapping.TRIM,LTRIM,RTRIMto remove whitespace.POSITIONorCHARINDEXto 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
- Snowflake Split String Into Rows: SQL Examples and Tips
- Snowflake SPLIT: How to Split Strings by Delimiter
- Snowflake SQL SPLIT: Syntax, Examples, and Alternatives
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Max Value Based on Criteria: Formula GuideTo find the maximum value in Excel based on one or more criteria, use MAXIFS (for Excel 2019…
- SUMIF Excel Cell Value: Criteria, Examples & FixesTo make SUMIF use a value stored in a cell, put the cell reference in its criteria argument.…
- Excel INDEX MATCH Lookup: Formula, Examples, and TipsIn Microsoft Excel for Microsoft 365, INDEX and MATCH work together to look up a value: MATCH finds…
- Excel Formula for Number of Years From a Date, ExplainedTo calculate completed years from a date in Excel, use =DATEDIF(A2,TODAY(),"Y"), where A2 contains the starting date. For…
- Excel Formula for Tax Calculation: The Complete GuideThe core Excel formula for tax calculation is =A2*B2, where A2 contains the taxable amount and B2 contains…
- Excel LAMBDA in Name Manager: How to Create and Use ItYes. In Microsoft 365 Excel for Windows, you can save a LAMBDA formula as a defined name in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.