Snowflake REPLACE: Find and Replace Parts of a String
The REPLACE function in Snowflake is like a find-and-replace tool for text. It allows you to search for specific parts of a string and replace them with something else or remove them entirely if no replacement is specified. It's handy for cleaning up data, updating information, or even censoring unwanted text.
What it does
REPLACE searches within a string (the "subject") for occurrences of a specified pattern (the "substring") and replaces each occurrence with another string (the "replacement"). If you don't provide a replacement string, it simply removes the substring from the subject.
Syntax
Here’s how to use the REPLACE function:
_10REPLACE(`<subject>`, `<substring>` [ , `<replacement>` ])
where:
<subject>: The string where replacements occur. This could be a column in a table or a specific text string.<substring>: The part of the subject you want to replace.<replacement>: (Optional) What to replace the substring with. If not specified, the substring is just removed.
Common use cases
- Correcting typos in text data: Suppose you've collected survey data and found that many respondents have misspelled a word
_10SELECT_10 REPLACE (response_text, 'teh', 'the') AS corrected_response_10FROM_10 survey_responses;
- Updating brand names in product listings: If a product brand changes its name, you might need to update that across all your listings
_10SELECT_10 REPLACE (product_description, 'OldBrand', 'NewBrand') AS updated_description_10FROM_10 products;
- Removing unwanted characters: To clean up text data that contains unnecessary punctuation or symbols
_10SELECT_10 REPLACE (comment, '!', '') AS clean_comment_10FROM_10 user_comments;
- Censoring sensitive information: For privacy or sensitivity reasons, you might want to censor certain words in texts
_10SELECT_10 REPLACE (text_content, 'sensitive_word', '****') AS censored_text_10FROM_10 messages;
Examples
Remove a substring
Input
Table: hitchhikers_quotes
| id | quote | keyword |
|---|---|---|
| 1 | Don’t panic. It’s just a phase I’m going through. | just a phase |
| 2 | Time is an illusion. Lunchtime doubly so, but Don’t panic. | illusion |
| 3 | The ships hung in the sky in much the same way that bricks don’t. Don’t panic. | sky |
Snowflake SQL Query
_10SELECT_10 id,_10 quote,_10 keyword,_10 REPLACE (quote, 'Don’t panic', '') AS modified_quote_10FROM_10 hitchhikers_quotes;
Output
| id | quote | keyword | modified_quote |
|---|---|---|---|
| 1 | Don't panic. It’s just a phase I’m going through. | just a phase | . It’s just a phase I’m going through. |
| 2 | Time is an illusion. Lunchtime doubly so, but Don't panic. | illusion | Time is an illusion. Lunchtime doubly so, but . |
| 3 | The ships hung in the sky in much the same way that bricks don't. Don’t panic. | sky | The ships hung in the sky in much the same way that bricks don’t. . |
Replace a substring with a specific substring
Input
Table: hitchhikers_quotes
| id | quote | keyword |
|---|---|---|
| 1 | Don’t panic. It’s just a phase I’m going through. | just a phase |
| 2 | Time is an illusion. Lunchtime doubly so, but Don’t panic. | illusion |
| 3 | The ships hung in the sky in much the same way that bricks don’t. Don’t panic. | sky |
Snowflake SQL Query
_10SELECT_10 id,_10 quote,_10 keyword,_10 REPLACE (quote, 'Don’t panic', 'Stay calm') AS modified_quote_10FROM_10 hitchhikers_quotes;
Output
| id | quote | keyword | modified_quote |
|---|---|---|---|
| 1 | Don't panic. It’s just a phase I’m going through. | just a phase | Stay calm. It’s just a phase I’m going through. |
| 2 | Time is an illusion. Lunchtime doubly so, but Don't panic. | illusion | Time is an illusion. Lunchtime doubly so, but Stay calm. |
| 3 | The ships hung in the sky in much the same way that bricks don't. Don’t panic. | sky | The ships hung in the sky in much the same way that bricks don’t. Stay calm. |
Replacing a substring based on a column values
Input
Table: hitchhikers_quotes
| id | quote | keyword |
|---|---|---|
| 1 | Don’t panic. It’s just a phase I’m going through. | just a phase |
| 2 | Time is an illusion. Lunchtime doubly so, but Don’t panic. | illusion |
| 3 | The ships hung in the sky in much the same way that bricks don’t. Don’t panic. | sky |
Snowflake SQL Query
_10SELECT_10 id,_10 quote,_10 keyword,_10 REPLACE (quote, keyword, 'something remarkable') AS modified_quote_10FROM_10 hitchhikers_quotes;
Output
| id | quote | keyword | modified_quote |
|---|---|---|---|
| 1 | Don't panic. It’s just a phase I’m going through. | just a phase | Don’t panic. It’s something remarkable I’m going through. |
| 2 | Time is an illusion. Lunchtime doubly so, but Don't panic. | illusion | Time is something remarkable. Lunchtime doubly so, but Don’t panic. |
| 3 | The ships hung in the sky in much the same way that bricks don't. Don’t panic. | sky | The ships hung in the something remarkable in much the same way that bricks don’t. Don’t panic. |
