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 |
![Y42 SQL completion user interface](/learn/_next/image?url=%2Flearn%2Fimages%2Fcta%2Fsql-completion.webp&w=3840&q=100)