How to Remove Newline Characters from String in Snowflake?

Post author:Vithal S
Post last modified:November 10, 2020
Post category:Snowflake
Reading time:5 mins read

If you store a string as a variable or a column in relational databases such SQL Server , then it can contain line breaks or newline (\n) characters. But, these newline characters have to be removed in pre-processing steps. In this article, we will check how to remove newline characters from a string or text in Snowflake.

Remove Newline Characters from String in Snowflake

Removing newline (\n), carriage return (\r) or any special characters is the common pre-processing step before storing records in any relational databases. Many databases supports built-in functions to work with special characters.

But, in Snowflake, you can use string function and regular expression function to remove newline character. The process is same as removing white spaces from a string.

Methods to Remove Newline Character from String in Snowflake

There are multiple methods that you can use. But, in this article, i will explain two commonly used methods to remove newline from a string or text.

You can use any of the following string and regex function to remove new line characters as per your requirements.

Snowflake Replace Function to Remove Newline Character
Snowflake Regexp_Replace Function to Remove Newline Character

Now let us check these two methods in brief.

Snowflake Replace Function to Remove Newline Character

You can use replace function to remove the newline character by replacing it with nothing.

For example, Consider following replace example to remove newline (\n) character

>select replace('this is \n test \n string', '\n', '') as output;
+-----------------------+
| OUTPUT                |
|-----------------------|
| this is  test  string |
+-----------------------+

You can use ASCI code as well.

C>select replace(replace('this is \n test \n string', char(10)), char(13)) as output;
+-----------------------+
| OUTPUT                |
|-----------------------|
| this is  test  string |
+-----------------------+

Note that, CHAR(10) is the character represented by ASCII code 10, which is a Line Feed (\n) so it’s a new line. And CHAR(13) is the character represented by ASCII code 13, which is a carriage return (\r).

Snowflake Regexp_Replace Function to Remove Newline Character

You can use regular expression functions to remove newline character. We will use regexp_replace function to achieve our goal.

Foe example, consider following example to remove \n.

>select regexp_replace('this is \n test \n string' ,'\n',' ',1,0) as output;
+-------------------------+
| OUTPUT                  |
|-------------------------|
| this is   test   string |
+-------------------------+