SQL TRIM Function

One of the common challenges when performing string comparison, sorting, etc, is the inconsistent data, like unwanted spaces and symbols at the start and end of a string. The SQL Server TRIM function removes empty spaces or special characters from both the left side (start) and the right side (end) of a string expression.

The default behaviour of the SQL TRIM function removes the leading and trailing empty spaces. If you mentioned the characters to remove inside the TRIM function, it removes those specified characters. Remember, you must use the FROM clause to specify the character at the beginning.

The SQL Server 2022 version introduces three important keywords for the TRIM function they are LEADING, TRAILING, and BOTH for removing either at the start or end, or both at the beginning and end.

SQL TRIM Function Syntax

The basic syntax of the TRIM function for removing both the trailing and leading spaces from a given string is as shown below.

SELECT TRIM (Character_Expression)
FROM [Source]

SELECT TRIM ('Special_Characters' FROM Character_Expression)
FROM [Source]

The most complex syntax of the SQL TRIM function to control the trimming by passing the LEADING and TRAILING keywords is:

SELECT TRIM([ LEADING | TRAILING | BOTH ] [specialChar FROM] Character_Expression )

Arguments

  • LEADING: It removes the specified characters from the start of a string, same as LTRIM.
  • TRAILING: It removes the specified characters from the end of a string, same as RTRIM.
  • BOTH: It is the default behaviour of the SQL TRIM function and removes characters from both start and end.
  • Special Characters: It is a single or multiple characters that should be removed from the Character Expression. It accepts all kinds of string data types except varchar(max) and nvarchar(max). If you omit this argument, it considers the blank space as the default character and removes the empty spaces on both sides.
  • Character_Expression: Please specify a valid Expression on which you want to remove empty spaces or special characters from the left and right sides.

TIP: If you pass the NULL value as the expression or character, it returns NULL as output.

Remove empty spaces from both sides of a string

The default behaviour of the SQL Server TRIM function is to remove the empty spaces from both sides of the string. As you can see from the example below, we haven’t used any special_character.

SELECT TRIM('    Tutorial Gateway    ') AS t1;
t1
---------------------
Tutorial Gateway

Using a Variable

Instead of directly mentioning the expression inside the TRIM function, we declared a variable of VARCHAR. Next, use the @str variable as the TRIM function argument for deleting the extra leading and trailing spaces.

DECLARE @str VARCHAR(50)
SET @str = '   Hello World!         '

SELECT TRIM(@str) AS t2;
t2
---------------
Hello World!

Use SQL TRIM function to remove spaces from the Start

In the previous two examples, we successfully removed the spaces from both sides. However, you can use this TRIM function to remove empty spaces from the start of the string. For instance, the query below removes the leading spaces.

SELECT TRIM('   Hello') AS t3;
t3
-----------
Hello    

Delete spaces from the End

Similar to the above example, we can use this SQL TRIM function to remove empty spaces from the end of the string. It is very helpful when the users accidentally insert the data with a few empty spaces at the end of each value. For instance, the query below removes the trailing spaces in a string.

SELECT TRIM('Hello          ') AS t4;
t4
---------------
Hello      

Combining with CONCAT

When performing a string concatenation, if there are any unwanted spaces in the FirstName and LastName, it is safe to use the TRIM function before the CONCAT.

SELECT CONCAT(TRIM(FirstName), ' ',  TRIM(LastName)) AS Name

FROM customers;

SQL TRIM function dealing NULL Values

If the special character or the given expression is a NULL value, the TRIM function returns NULL as the output. The query below returns NULL as the output.

SELECT TRIM(NULL) AS ct0;
ct0
------
NULL

To handle the NULL values, use the COALESCE or the ISNULL function.

SELECT TRIM(COALESCE(NULL,'')) AS ct1;

SELECT TRIM(ISNULL(NULL,'')) AS ct1;

Trimming Specific Character(s) from the start of a string

In the above section, we used the SQL TRIM function without mentioning which character to remove, so it simply deletes the empty spaces. However, the following sections (three) will work on removing a specific character(s) from a particular position.

TIP: As mentioned earlier, LEADING, TRAILING, or BOTH are introduced in the SQL Server 2022 version. Next, you must set the database compatibility level to 160. To do so write  the below code

ALTER DATABASE DatabaseName
SET COMPATIBILITY_LEVEL = 160;

In the example below, we used ‘*’ as the first argument to specify that the TRIM function should remove * from both leading and trailing. As there are no stars (*) at the end, this will work.

SELECT TRIM('*' FROM '*** Welcome') AS ts1;
ts1
-----------
 Welcome

However, the query below removes stars from both sides, instead of leading ones.

SELECT TRIM('*' FROM '*** Welcome****') AS ts2;
ts2
--------------
 Welcome

To deal with situations, you must use the LEADING keyword to inform the SQL TRIM function to perform deletion at the beginning only.

SELECT TRIM(LEADING '*' FROM '*** Welcome****') AS ts3;
ts3
---------------
 Welcome****

Similarly, the below one deletes $ and #.

SELECT TRIM(LEADING '$#' FROM '$$$##$$ Hello$$$') AS ts4;
ts4
----------------
 Hello$$$

If you work on Numbers and string combinations, you can remove the integers or characters. For instance, a product name might look like AWO1234; you can use the TRIM function to remove AWO.

SELECT TRIM(LEADING 'AWO' FROM 'AWO1234') AS ts5;
ts5
-------
1234

If there are unwanted zeros at the beginning of a number, use the TRIM function to delete them. For this, you must CAST the number to a string and perform the deletion.

SELECT TRIM(LEADING '0' FROM '00002000') AS ts6;
ts6
--------
2000

SQL Trimming Specified Character(s) from the end of a string

As we mentioned earlier, if there are no specified characters at the beginning, you can use the below-mentioned TRIM function query to remove from the end of a string.

SELECT TRIM('#' FROM 'World####') te1;
te1
---------
World

However, if there are # symbols on both sides of the World, the above query removes the leading # and trailing.

SELECT TRIM('#' FROM '####World####') te2;
te2
-------------
World

To resolve this problem, use the TRAILING keyword at the beginning of the SQL TRIM function. It removes the specified character(s) from the end of a string.

SELECT TRIM(TRAILING '#' FROM '####World####') te3;
te3
-------------
####World

To remove multiple symbols, use the query below.

SELECT TRIM(TRAILING '# * @ !' FROM '$2000##**#@!') te3;
te3
------------
$2000

This example query removes the trailing zeros from the given number.

SELECT TRIM('.0' FROM '1234.000') te5;
te5
--------
1234

SQL Trimming leading and trailing characters in a string

In the above two examples, we used the LEADING and TRAILING keywords to remove the specified characters from the start and end of a string. In this example, we will remove the leading and trailing characters from a given string. For this, you can either explicitly mention BOTH inside the TRIM function. Otherwise, completely ignore it, as it is the default behaviour.

The first statement removes # and * symbols from both sides.

SELECT TRIM('# *' FROM '####     Welcome to this World    ** ') AS lt1; 
lt1
-------------------------------------
     Welcome to this World   

Although there are @, #, &, and $ symbols, the query below removes only the leading @ and # symbols.

SELECT TRIM('@ #' FROM ' @@@###$     We Provide Free Education at Tutorial Gateway  $@@##&      ') AS lt2;
lt2
-------------------------------------------------------
$     We Provide Free Education at Tutorial Gateway  $@@##&     

To remove all the symbols, in the above code, replace  ‘@ #’  with ‘@ # & $’. If you want to use the BOTH keyword, use the query below. It removes numbers from the start and end of a string.

SELECT TRIM(BOTH '123' FROM '123World123') AS lt4;
lt4
-----------
World

SQL TRIM function on Table Columns for INSERT, UPDATE and SELECT

In all the previous examples, we either inserted the string expression directly inside the TRIM function or used an already declared variable; however, in this example, we use it on table columns.

We may get SQL Server data with some extra spaces due to typing mistakes or other data entry issues. If this is the case, you can use this function, RTRIM or LTRIM, to remove the extra leading and trailing spaces.

The SELECT statement below uses the TRIM function to remove the extra spaces in the FirstName column and $# symbols in the LastName column.

Here, we will use LEADING and TRAILING keywords to trim only at the beginning or end of a string.

  • LeadLast removes # and $ from the beginning of a LastName.
  • TrailLast removes # and $ from the end of a LastName.
  • Trail$Last removes only $ from the end of a LastName and keeps the #.
SELECT [EmpID]
      ,[FirstName]
      ,TRIM(FirstName) AS First
      ,[LastName]
      ,TRIM('# $' FROM LastName) AS Last
      ,TRIM(LEADING '# $' FROM LastName) AS LeadLast
      ,TRIM(TRAILING '# $' FROM LastName) AS TrailLast
      ,TRIM(TRAILING '$' FROM LastName) AS Trail$Last
      ,[City]
      ,TRIM(City) AS NewCity
      ,[Country] 
  FROM [customers]

Use the TRIM function in a WHERE clause

When performing a string comparison in the WHERE clause, if there are any start or end spaces, it may return wrong results. To resolve this issue, use the SQL TRIM function to delete them before performing the string comparison. For instance, the query below displays all the customers who reside in New York City.

SELECT * FROM customers

WHERE TRIM(City) = 'New York';

It’s not all, you can use the TRIM function to remove any leading or trailing words from each row in a column before the string comparison. For instance, if the country name has both USA and US, and you want them both, the code below will work.

SELECT * FROM customers

WHERE TRIM(TRAILING 'A' FROM Country) = 'US';

NOTE: Try to avoid using the TRIM function in the WHERE and JOIN clauses.

SQL Server TRIM Function Example

UPDATE Example

Using the TRIM function when selecting the data is ok. However, updating the old content by removing unwanted spaces and special characters is crucial. There are situations where we dump data from different sources into a database table, and the data may have these kinds of issues. To handle the situation, use the TRIM function with the UPDATE statement to modify the already stored data.

UPDATE customers
SET FirstName = TRIM(FirstName),
LastName = TRIM('# $' FROM LastName)

INSERT Example

Instead of updating the wrong values or using the SQL TRIM function to select the clean form of the text, you can use it with INSERT to avoid entering extra spaces.

In the example below, we used the TRIM function multiple times inside an INSERT statement. It is to demonstrate usage, but you should be careful and use it if really necessary.

INSERT INTO [dbo].[customers]([FirstName],[LastName] ,[City],[Country])
VALUES (TRIM('  Lucy'), TRIM('Wills '), TRIM('  London'), 'UK')

NOTE: Using the TRIM function excessively will impact the query performance.

Categories SQL