String Manipulation Techniques in Snowflake : REGEXP, CONTAINS, REPLACE, Splitting and Concatenating

String manipulation is fundamental when dealing with textual data in any database system. Snowflake offers an array of string functions that enable users to perform operations like searching, replacing, splitting, and concatenating strings with ease.

Use Cases of REGEXP, CONTAINS, and REPLACE

1. REGEXP:

Definition: The REGEXP function in Snowflake allows for pattern matching using regular expressions.

Use Case: Suppose you want to find all emails from a text column. Regular expressions would be useful here.

2. CONTAINS:

Definition: The CONTAINS function checks if a substring exists within a string.

Use Case: To find out if a certain keyword exists in product descriptions or reviews.

3. REPLACE:

Definition: This function replaces occurrences of a substring within a string.

Use Case: For example, to replace occurrences of the word “beta” with “final” in a product name.

Techniques for splitting and concatenating Strings

Splitting Strings:

The SPLIT function divides a string based on a delimiter and returns an array.

--DDL
CREATE TABLE sample_data (sentence STRING);
--Insert
INSERT INTO sample_data VALUES ('coconut,banana,cherry');
--Select
SELECT SPLIT(sentence, ',') AS fruits FROM sample_data;

+------------------------+
|        fruits          |
+------------------------+
| ["coconut","banana","cherry"] |
+------------------------+

 Concatenating strings:

Snowflake allows string concatenation using the || operator.

--DDL
CREATE TABLE names_data (first_name STRING, last_name STRING);
--Insert
INSERT INTO names_data VALUES ('Narendra', 'Dhamodar');
--Select
SELECT first_name || ' ' || last_name AS full_name FROM names_data;

+-------------------+
| full_name         |
+-------------------+
| Narendra Dhamodar |
+-------------------+

Example:

Let’s say you have a table of product descriptions and you want to:

Find descriptions that contain the word “organic”
Replace the word “small” with “tiny”
Split the description based on spaces

--DDL
CREATE TABLE products (description STRING);
--Insert
INSERT INTO products VALUES ('Fresh organic bananas'), ('Small green apples'), ('Delicious small grapes');
--Select
SELECT
    description,
    CONTAINS(description, 'organic') AS contains_organic,
    REPLACE(description, 'small', 'tiny') AS new_description,
    SPLIT(description, ' ') AS words
FROM products;

Output

+------------------------+----------------+---------------------+----------------------------------+
|     description        | contains_organic |  new_description   |              words               |
+------------------------+----------------+---------------------+----------------------------------+
| Fresh organic bananas  |      TRUE      | Fresh organic bananas  | ["Fresh", "organic", "bananas"]  |
| Small green apples     |     FALSE      | Tiny green apples      | ["Small", "green", "apples"]     |
| Delicious small grapes |     FALSE      | Delicious tiny grapes  | ["Delicious", "small", "grapes"] |
+------------------------+----------------+---------------------+----------------------------------+

Refer more on 
Cloud Database
cloud_storage
Snowflake
snowflake_interview