SQL Server TRIM() Function

What is the TRIM() Function in SQL?

The TRIM() function in SQL is used to omit or remove the spaces or the especially mentioned characters from a specified string's beginning or end. By default, the TRIM() function removes the spaces from both sides of the actual strings, i.e. it removes the leading and the trailing spaces from the input strings. The syntax of TRIM() function is:

Syntax

Parameters

String refers to the data from which the extra spaces need to be removed. If you want to remove some specific characters from the string, then the function takes the string and the special characters as the parameters.

The syntax of the TRIM() function in that case is :

Here, characters are the specific characters that are to be removed, and the string is the input string from which these characters are to be removed.

Return Type

The TRIM() function in SQL returns a string value with extra spaces removed from the beginning and end of the input string. If the specific_characters_to_be_removed parameter is used, then the function returns a string with the specified characters removed from both the beginning and end of the input string.

How to Use the TRIM() Function in SQL?

The use of the TRIM() function comes more frequently in cases when the databases contain unwanted leading and trailing spaces around the data strings. If these aren’t removed or trimmed, the data will return incorrect matches for queries with WHERE and other clauses. So, the TRIM() function plays an important role in that situation.

As you can see from the image above, we have an input string as "I am here to learn TRIM function", and this string has space characters at the beginning and the ending of the string. As soon as we have applied the TRIM() function to this input string, then the space characters from both sides of the string have been removed, and only the actual string data is obtained. This helps us avoid any incorrect matches for the queries, and we can get the actual results.

You can also optionally specify individual characters to be trimmed, and the TRIM() function will remove them at the beginning or end of a string.

Left TRIM and Right TRIM in SQL

By default, the TRIM() function removes the spaces from both sides of the actual strings, but if you only want to remove the spaces from the beginning of the string and not from the last, then you can make use of the Left TRIM function, whose syntax is:

Similarly, to remove the spaces from the end of the strings and not from the start, we can make use of the Right TRIM function, whose syntax is:

Working of LTRIM:

As you can see from the image above, we have an input string as "I am here to learn LTRIM". This string has space characters at the beginning and the ending of the string, but as soon as we apply the LTRIM() function to this input string, then the space from the start of the input string is removed, and only the spaces at the right side of the input string are remaining.

Now, let's understand the same for the RTRIM() function:

As you can see from the image above, we have an input string as "I am here to learn RTRIM". This string has space characters at the beginning and the ending of the input string, but as soon as we apply the RTRIM() function to our input string, then the spaces at the right side of the input string have been removed, and only the spaces at the left side of the input string are remaining.

Examples

Case 1: Remove the space character from both sides of string

Syntax:

Let's take the example where we have the input_string as the parameter, and we want to remove the spaces from the starting and the ending of the input string.

Query:

Output:

We can see from the output that all the leading and trailing spaces have been removed.

Case 2: Remove specified characters from both sides of string

Let's see the example for this where we have been given the input_string as @Hello.. and the specific characters which we want to remove as .@:

Output:

We can see from the output string that all the specified characters have been removed.

Case 3: Remove specified characters from the start/end of a string (Using LEADING,TRAILING and BOTH)

Query to remove 1's from the start:

Output:

We can see from the output string that the character 1 has been removed from the start of the input string.

Query to remove 1's from the end:

Output:

We can see from the output string that the character 1 has been removed from the end of the input string.

Query to remove 1's from both start and end:

Output:

We can see from the output string that the character 1 has been removed from both the start and end of the input string.

Let's see the examples for the LTRIM() and the RTRIM() functions:

Case 4: The LTRIM() function removes whitespaces at the beginning of the string.

Output:

Case 5: The RTRIM() function removes whitespaces from the end of the string.

Output

Learn More

Learn more about advanced concepts of SQL