Snowflake TRIM functions: Clean Up Strings
In Snowflake, the TRIM functions— TRIM, LTRIM, and RTRIM —are essential for cleaning up strings. They allow you to remove unwanted characters from either end of your string data.
Best Practices
- Specify Trim Characters: Always specify which characters you need to trim if they are not just spaces. This avoids unintentional data alteration.
- Use with Cautious: When dealing with data that includes similar characters as those you intend to trim, make sure your use of TRIM functions won’t accidentally remove necessary content.
TRIM
The TRIM function is used to eliminate unwanted characters from both the beginning and the end of a string. If you don’t specify which characters to remove, it defaults to trimming whitespace.
Syntax
_10TRIM([characters FROM] string)
TRIM Example
Suppose you have a string that has asterisks and spaces around it like * Arthur Dent *. To remove both the asterisks and spaces, you would use:
Snowflake SQL Query
_10SELECT TRIM(' * ' FROM '* Arthur Dent *') AS clean_name;
Output
| clean_name |
|---|
| Arthur Dent |
LTRIM
LTRIM removes characters from the beginning (left side) of a string. Like TRIM, it defaults to removing whitespace if no characters are specified.
Syntax
_10LTRIM(string [, characters])
LTRIM Example
For a string ***Ford Prefect*** where you want to remove the asterisks on the left:
Snowflake SQL Query
_10SELECT LTRIM('***Ford Prefect***', '*') AS clean_name;
Output
| clean_name |
|---|
| Ford Prefect*** |
RTRIM
RTRIM works similarly to LTRIM but affects the end (right side) of a string.
Syntax
_10RTRIM(string [, characters])
RTRIM Example
To remove trailing hashes from Trillian###:
Snowflake SQL Query
_10SELECT RTRIM('Trillian###', '#') AS clean_name;
Output
| clean_name |
|---|
| Trillian |
