In a data warehouse, you will receive data from multiple sources. You may have to pre-process the data before loading it to target table. The pre-process step such as removing white spaces from data is commonly used. In this article, we will check how to remove spaces in a string using Snowflake built-in functions.
Remove Spaces in a String in snowflake
Nowadays, data is required everywhere. Many organizations automatically capture the data using tools or machines. Machines may introduce the unwanted data such as white space when it captures the actual data. These junk data is of no use in reporting, thus you need to remove them before loading into the target table.
Snowflake provides many built-in functions to remove white space or any unwanted data from a string.
You can use any of the following string functions as per your requirements.
Now, let us check these functions with an example.
Remove Space using Snowflake Replace String Function
The replace function is one of the easiest functions to remove white spaces or special characters from a string.
For example, consider following replace code snippet.
>SELECT REPLACE('AB C D ', ' ', '') as output;
+--------+
| OUTPUT |
|--------|
| ABCD |
+--------+For more information on the Snowflake replace function, read
Remove Leading and Trailing White Space using TRIM Functions
Snowflake supports TRIM, LTRIM, and RTRIM functions.
- TRIM: Remove leading and trailing white spaces
- LTRIM: Remove leading white spaces
- RTRIM: Remove trailing white spaces
For example, consider following examples
>SELECT TRIM(' AB C D ') as output;
+---------+
| OUTPUT |
|---------|
| AB C D |
+---------+
>SELECT LTRIM(' AB C D ') as output;
+----------+
| OUTPUT |
|----------|
| AB C D |
+----------+
>SELECT RTRIM(' AB C D ') as output;
+----------+
| OUTPUT |
|----------|
| AB C D |
+----------+Note that, TRIM functions remove only leading and trailing white spaces. However, you can use any other methods to remove white spaces from within a string.
Remove Space using Snowflake Translate Function
You can use Snowflake translate function to translate or replace one or more characters into another set of characters.
For example, consider following example
C>SELECT TRANSLATE('AB C D ', ' ', '') as output;
+--------+
| OUTPUT |
|--------|
| ABCD |
+--------+For more information on Snowflake translate function, read
Remove White Spaces using REGEXP_REPLACE Function
The Regexp_replace remove all occurrences of white space in a string.
For example, consider following regexp_replace example to replace all spaces in the string with nothing.
>select REGEXP_REPLACE('AB C D ','( ){1,}','') as output;
+--------+
| OUTPUT |
|--------|
| ABCD |
+--------+For more information on Snowflake regular expression functions, read
Hope this helps 🙂