Trim in snowflake

What is TRIM(), LTRIM() AND RTRIM() function in Snowflake ?

by

Are you looking to find what is the TRIM(), LTRIM() AND RTRIM() functions within the Snowflake cloud data warehouse or maybe you are looking for a solution how to trim the leading white space or trailing white spaces from the string in the Snowflake? If you are looking for any of these problem solutions then you have landed on the correct page. I will also show you what and how to use TRIM(), LTRIM() AND RTRIM() functions. I will explain the TRIM(), LTRIM() AND RTRIM() functions by taking a practical examples. So don’t waste time let’s start step by step guide to understanding what is the TRIM(), LTRIM() AND RTRIM() expression.

What is TRIM() Function in Snowflake

Trim() function is used to remove Leading and Trailing characters from a string. Trim() function consists of two arguments which will be helps to trim the string value.

Syntax of the TRIM in Snowflake

-- Syntax : 

TRIM( expression, [ characters ])

TRIM() Argument Details :

First Input Parameter (expression)String value ( Mandatory )
Second Input Parameter Characterscharacter value ( Optional )

First Input Parameter (expression) :

A String which is required to trim.

Second Input Parameter (Character) :

This Argument helps to remove one or more characters from left side and right side of the expression. The default value is ‘ ‘. If no characters are specified then all leading and trailing blank spaces will be removed. Characters can be specified in any order.

How to use TRIM function in Snowflake with Example

--Examples :

select trim('❄-❄ABC-❄-', '❄-');

-------------------------+
 TRIM('❄-❄ABC-❄-', '❄-') |
-------------------------+
 ABC                     |
-------------------------+

What is LTRIM() Function in Snowflake?

LTrim() function is used to remove Leading characters and also white spaces from a string. LTrim() function consists of two arguments which will be helps to trim the string value.

Syntax of the LTRIM in Snowflake? 

-- Syntax : 

LTRIM( expression, [ characters ])

LTRIM() Argument Details :

First Input Parameter (expression) String value ( Mandatory )
Second Input Parameter Characters character value ( Optional )

First Input Parameter (expression):

A String which is required to trim.

Second Input Parameter (Character):

This Argument helps to remove one or more characters from left side of the expression. The default value is ‘ ‘. If no characters are specified then all leading blank spaces will be removed. Characters can be specified in any order.

How to use LTRIM function in Snowflake with Example 

--Example :

select ltrim('#000000123', '0#')

+---------------------------+
| LTRIM('#000000123', '0#') |
|---------------------------|
| 123                       |
+---------------------------+

What is RTRIM() Function in Snowflake?

RTrim() function is used to remove Leading characters and also white spaces from a string. RTrim() function consists of two arguments which will be helps to trim the string value.

Syntax of the RTRIM in Snowflake? 

-- Syntax : 

RTRIM( expression, [ characters ])

RTRIM() Argument Details :

First Input Parameter (expression)String value ( Mandatory )
Second Input Parameter Characters character value ( Optional )

First Input Parameter (expression):

A String which is required to trim.

Second Input Parameter Characters :

This Argument helps to remove one or more characters from Right side of the expression. The default value is ‘ ‘. If no characters are specified then all trailing blank spaces will be removed. Characters can be specified in any order.

How to use RTRIM function in Snowflake with Example?

--Example :

select rtrim('$125.00', '0.');

------------------------+
 RTRIM('$125.00', '0.') |
------------------------+
 $125                   |
------------------------+

How to remove leading Zeros in Snowflake

To remove the leading zeros we can use the Ltrim function of the Snowflake. You can pass the input number or string as the first parameter in the Ltrim function and then pass the 0 as the second parameter. This will remove the all the leading zero from the number or the string.

Example:

select LTRIM(00123, '0');

-- Output
+-------------------+
| LTRIM(00123, '0') |
|-------------------|
| 123               |
+-------------------+
1 Row(s) produced. Time Elapsed: 1.192s

How to remove white spaces from the string in the Snowflake?

To remove the white spaces or the blank spaces from the string TRIM function can be used. It can remove the whitespaces from the start and end both.

Example of removing whitespace in Snowflake:

select TRIM('  Snwoflake Space Remove  ', ' ');


-- Output
+-----------------------------------------+
| TRIM('  SNWOFLAKE SPACE REMOVE  ', ' ') |
|-----------------------------------------|
| Snwoflake Space Remove                  |
+-----------------------------------------+
1 Row(s) produced. Time Elapsed: 1.210s

How to remove last character from string in Snowflake?

To remove the last character from the string you can pass the string in the RTRIM function.

Example:

select RTRIM('Snwoflake Remov  ', ' e');


-- Output
+-----------------------------------------+
| TRIM('SNWOFLAKE SPACE REMOVE  ', 'E ') |
|-----------------------------------------|
| Snwoflake Space Remov                  |
+-----------------------------------------+
1 Row(s) produced. Time Elapsed: 1.210s

Snowflake remove first character of string

To remove the first character from the string you can pass the string in the LTRIM function.

Example:

select LTRIM('Snwoflake Remov  ', ' S');


-- Output
+-----------------------------------------+
| TRIM('SNWOFLAKE SPACE REMOVE  ', 'S ') |
|-----------------------------------------|
| nwoflake Space Remove                 |
+-----------------------------------------+
1 Row(s) produced. Time Elapsed: 1.210s

Trim Snowflake official Documentation Link

Final Thoughts

In this article we have learned about TRIM(), LTRIM() AND RTRIM() functions and uses with the examples explained clearly. I have also covered different scenario with practical example that could be possible. I hope the information that was provided is helped in gaining the knowledge.

Please share your comments and suggestions in the comment section below and I will try to answer all your queries as time permits.

Join Telegram Group
Download Free Azure Videos
Connect on LinkedIn

Unicode in Snowflake

Repeat function in Snowflake

Revanth Kumar