How to Remove Leading and Trailing Spaces in T-SQL

Problem:

You’d like to remove a spaces or a specific characters from the beginning and end of a string in T-SQL.

Example:

Our database has a table named company with data in two columns: id and name.

Let’s trim the name of each company to remove the unnecessary space at the beginning and end.

Solution:

We’ll use the TRIM function. Here’s the query you would write:

SELECT TRIM(' ' FROM name) AS new_name
FROM company;

Alternatively, you can use the shorter version without the FROM keyword and space as characters to remove; by default, TRIM will treat this as removing spaces from a string stored in a given column or expression in argument of TRIM function.

SELECT TRIM(name) AS new_name
FROM company;

Here’s the result of both queries:

Discussion:

Use the TRIM function if you want to trim a string in a table. This function allows you to remove a specific character from the beginning and end of a string. This function takes the following arguments:

• The character you want to trim from the string, by default it is a space..
• The FROM keyword, followed by the name of the string column to be trimmed.

In our example, that looks like:

TRIM(' ' FROM name)

T-SQL allows also remove space of another characters only from the beginning or only from end of a string.

The example below removes the space at the end of each company by using RTRIM() function.

SELECT RTRIM(name) AS new_name
FROM company;

But could just as well be used to trim the space at the beginning if you use the LTRIM() function instead:

SELECT LTRIM(name) AS new_name
FROM company;

The query returns the name column without a space at the end. Notice that the spaces at the beginning are left untouched.