Rajendra Gupta
Before and after of using SQL Server Trim function

SQL TRIM Function

April 24, 2019 by

In this article, we will review the new SQL TRIM function in SQL Server 2017 onwards as well as providing some information on strings functions that pre-date it like LTRIM AND RTRIM.

SQL Developers usually face issueS with spaceS at the beginning and/or end of a character string. We may need to trim leading and trailing characters from a character string. We might need to do string manipulations with SQL functions as well. Suppose we want to remove spaces from the trailing end of a string, we would need to use SQL LTRIM and RTRIM functions until SQL Server 2016.

In SQL Server 2017, we get a new built-in function to trim both leading and trailing characters together with a single function. SQL TRIM function provides additional functionality of removing characters from the specified string. We can still use RTRIM and LTRIM function with SQL Server 2017 as well. Let’s explore these functions with examples.

SQL LTRIM function

It removes characters from beginning (Starting from the left side) of the specified string. In the following query, we have white space before and after the string. We need to remove space from the left side of the string using the LTRIM function.

We use the SQL DATALENGTH() function to calculate data length in bytes before and after using SQL LTRIM function.

SQL LTRIM function

  • Data String size in Bytes for Original String: 24
  • Data String size in Bytes after SQL LTRIM: 18

SQL RTRIM function

It removes characters from the end ((Starting from the right side) of the specified string. Let’s execute the following query to look at the effect of SQL RTRIM function.

SQL RTRIM function

  • Data String size in Bytes for Original String: 24
  • Data String size in Bytes after SQL RTRIM: 16

TRIM function in SQL Server 2017

Before SQL Server 2017, if we need to remove both Leading and Training spaces, we need to use both LTRIM and RTRIM function. In the following query, we used both LTRIM and RTIM functions with a string variable.

In the following screenshot, we can see it removes both leading and trailing space from the string.

  • Data String size in Bytes for Original String: 24
  • Data String size in Bytes after SQL LTRIM: 11

SQL LTRIM and RTRIM function

  • Data String size in Bytes for Original String: 25
  • Data String size in Bytes after SQL LTRIM and RTRIM: 11

We need to use two functions to remove the spaces from the string. We can use a single instance of a TRIM function in SQL Server 2017 onwards to trim both leading and trailing characters, let’s explore the TRIM function in a further section of this article.

The Syntax of the TRIM Function

  • TrimCharacter: We can specify the character to remove from the string. By default, SQL Server removes spaces from both sides of a string
  • String: We need to specify the string that we want to trim

Let’s explore SQL TRIM function with examples.

TRIM Leading and Training spaces:

By default, Trim function removes the space from both sides of a string. In the following query, we have to define a string with space on both sides of the string.

Execute this query:

We did not specify any characters to remove in TRIM function. In the output, SQL TRIM function removes both leading and trailing space.

You can also notice the difference in string length before and after the TRIM function.

  • Data String size in Bytes for Original String: 24
  • Data String size in Bytes after SQL TRIM: 11

SQL TRIM Function SQL 2017

TRIM Leading characters from a string:

In the previous example, we explored that SQL TRIM is the replacement of RTRIM and LTRIM and it eliminates to delete all space character before and after a string. We can remove the characters as well from the string using TRIM. Suppose we want to remove character ON from the string, we can specify it in TRIM function as per following script.

It checks the specified characters on both the leading and trailing side and removes the particular characters. In the string Application, we have character ON in the leading side. In the following screenshot, you can look at the output after TRIM function.

SQL TRIM Function SQL 2017

  • Data String size in Bytes for Original String: 11
  • Data String size in Bytes after SQL TRIM: 9

Let’s look at one more example. This time we want to remove characters App from the leading side. We do not need to make any changes in the script. We can specify the character to remove using SQL TRIM, and it gives the required output.

SQL TRIM Function SQL 2017

  • Data String size in Bytes for Original String: 11
  • Data String size in Bytes after SQL TRIM: 8

In previous examples, we removed character either from the leading end or trailing end. If we have a character on both sides, it removes them as well.

In the following example, we want to remove character A that is on both sides of the string. Execute the script, and it removes the specified characters from both sides.

SQL TRIM Function SQL 2017

  • Data String size in Bytes for Original String: 12
  • Data String size in Bytes after SQL TRIM: 10

Suppose we have specified a character that is not present on the string. In the following example, we want to remove character A and C from leading or trailing end. We do not have character C on either side, so it does not remove it. It only removes character A and gives the output.

  • Data String size in Bytes for Original String: 12
  • Data String size in Bytes after SQL TRIM: 10

TRIM for records in a table

In the previous article, we used a variable to define a string and perform SQL TRIM operation on it. Usually, we require using TRIM on table records. Let’s use it in the following example.

In the following examples, it eliminated character R from the string at the leading and trailing end.

Before and after of using SQL Server Trim function

We can use SQL TRIM function in Update statement as well. Suppose we want to update all records in Employee table and want to eliminate characters Manager from JobTitile.

It does not treat the Manager as a single word. Trim function checks for each character in the string and eliminates it.

Update and SQL Server TRIM

We can use comma (,) to separate each character as well.

It also does the same work and removes specific characters from the string at a leading and trailing end.

Update and SQL Server TRIM

Conclusion

In this article, we explored the SQL TRIM function with SQL Server 2017 and above. We also learned about the TRIM functions LTRIM and RTRIM available on or before SQL Server 2016 as well.

Depending on the version of SQL Server you are using, you should understand these functions and be familiar with the concepts. I hope you find this article useful. Feel free to provide feedback or ask questions in the comments below.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
308 Views