Articles Cookbook
Back to list
MS SQL Server

How to remove leading and/or trailing spaces of a string in T-SQL

Database:

Operators:

TRIM, FROM, LTRIM, RTRIM

Problem:

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

Example:

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

idname
1' Super Market '
2'Green shop '
3' Modern Bookshop'

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:

new_name
'Super Market'
'Green shop'
'Modern Bookshop'

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;
new_name
' Super Market'
'Green shop'
' Modern Bookshop'

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.

new_name
'Super Market '
'Green shop '
'Modern Bookshop'

Recommended courses:

Recommended articles:

See also:

go to top