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.

Replace 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.

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Start for free