SQL Server TRIM Function

Summary: in this tutorial, you will learn how to use the SQL Server TRIM() function to remove spaces or specified characters from both sides of a string.

SQL Server TRIM() function overview

The TRIM() function removes spaces or specified characters from both ends of a string. The following is the syntax of the TRIM() function:

TRIM([removed_characters FROM] input_string)
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• The removed_characters is a literal, variable, or table column of any non-LOB character type (NVARCHAR, VARCHAR, NCHAR, or CHAR) that contains characters which will be removed. Note that the NVARCHAR(MAX) and VARCHAR(MAX) types are not allowed. The remove_characters argument is optional. If you skip it, the TRIM() function will return a string after truncating all leading and trailing spaces from the input_string. It has the same effect as using both LTRIM() and RTRIM() function:RTRIM(LTRIM(input_string).
• The input_string is an expression of any character type (NVARCHAR, VARCHAR, NCHAR, or CHAR) where the removed_characters should be removed.

The TRIM() function returns a string where the removed_characters are removed from both left and right sides. It returns NULL if the input_string is NULL.

SQL Server TRIM() function examples

Let’s take some examples of using the TRIM() function.

A) Remove leading and trailing spaces from a string

The following example uses the TRIM() function to remove all leading and trailing spaces from the string ' Test string ';

SELECT 
    TRIM('  Test string    ');
Code language: SQL (Structured Query Language) (sql)

Here is the output:

result
-----------------
Test string

(1 row affected)

B) Using TRIM() function to remove specified characters from both sides of a string

This example uses the TRIM() function to remove the characters . and $ from the string '$$$Hello..':

SELECT 
    TRIM('.$' FROM '$$$Hello..') result
Code language: SQL (Structured Query Language) (sql)

The following shows the output:

result
----------
Hello

(1 row affected)

C) Using TRIM() function to clean up leading and trailing spaces in a column of a table

This statement uses the TRIM() function to remove all leading and trailing spaces from the values in the street column of the sales.customers table:

UPDATE 
    sales.customers
SET  
    street = TRIM(street);
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the SQL Server TRIM() function to remove spaces or specified characters from both sides of a string.