Snowflake
Find and Replace Substrings

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:


_10
REPLACE(`<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

_10
SELECT
_10
REPLACE (response_text, 'teh', 'the') AS corrected_response
_10
FROM
_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

_10
SELECT
_10
REPLACE (product_description, 'OldBrand', 'NewBrand') AS updated_description
_10
FROM
_10
products;

  • Removing unwanted characters: To clean up text data that contains unnecessary punctuation or symbols

_10
SELECT
_10
REPLACE (comment, '!', '') AS clean_comment
_10
FROM
_10
user_comments;

  • Censoring sensitive information: For privacy or sensitivity reasons, you might want to censor certain words in texts

_10
SELECT
_10
REPLACE (text_content, 'sensitive_word', '****') AS censored_text
_10
FROM
_10
messages;

Examples

Remove a substring

Input

Table: hitchhikers_quotes

idquotekeyword
1Don’t panic. It’s just a phase I’m going through.just a phase
2Time is an illusion. Lunchtime doubly so, but Don’t panic.illusion
3The ships hung in the sky in much the same way that bricks don’t. Don’t panic.sky

Snowflake SQL Query


_10
SELECT
_10
id,
_10
quote,
_10
keyword,
_10
REPLACE (quote, 'Don’t panic', '') AS modified_quote
_10
FROM
_10
hitchhikers_quotes;

Output

idquotekeywordmodified_quote
1Don't panic. It’s just a phase I’m going through.just a phase. It’s just a phase I’m going through.
2Time is an illusion. Lunchtime doubly so, but Don't panic.illusionTime is an illusion. Lunchtime doubly so, but .
3The ships hung in the sky in much the same way that bricks don't. Don’t panic.skyThe 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

idquotekeyword
1Don’t panic. It’s just a phase I’m going through.just a phase
2Time is an illusion. Lunchtime doubly so, but Don’t panic.illusion
3The ships hung in the sky in much the same way that bricks don’t. Don’t panic.sky

Snowflake SQL Query


_10
SELECT
_10
id,
_10
quote,
_10
keyword,
_10
REPLACE (quote, 'Don’t panic', 'Stay calm') AS modified_quote
_10
FROM
_10
hitchhikers_quotes;

Output

idquotekeywordmodified_quote
1Don't panic. It’s just a phase I’m going through.just a phaseStay calm. It’s just a phase I’m going through.
2Time is an illusion. Lunchtime doubly so, but Don't panic.illusionTime is an illusion. Lunchtime doubly so, but Stay calm.
3The ships hung in the sky in much the same way that bricks don't. Don’t panic.skyThe 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

idquotekeyword
1Don’t panic. It’s just a phase I’m going through.just a phase
2Time is an illusion. Lunchtime doubly so, but Don’t panic.illusion
3The ships hung in the sky in much the same way that bricks don’t. Don’t panic.sky

Snowflake SQL Query


_10
SELECT
_10
id,
_10
quote,
_10
keyword,
_10
REPLACE (quote, keyword, 'something remarkable') AS modified_quote
_10
FROM
_10
hitchhikers_quotes;

Output

idquotekeywordmodified_quote
1Don't panic. It’s just a phase I’m going through.just a phaseDon’t panic. It’s something remarkable I’m going through.
2Time is an illusion. Lunchtime doubly so, but Don't panic.illusionTime is something remarkable. Lunchtime doubly so, but Don’t panic.
3The ships hung in the sky in much the same way that bricks don't. Don’t panic.skyThe ships hung in the something remarkable in much the same way that bricks don’t. Don’t panic.
Y42 SQL completion user interface

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Get Started