Trimming text strings in SQL Server 2008

Question

Hello,

I have a table in a SQL Server 2008 database. This table has a nvarchar(256) column called 'Name'. Unfortunately, the values in this field have extra spaces included. For instance the name 'Bill' is actually stored as 'Bill ' in the table.

I want to update all of the records in this table to remove the extra spaces. However, I was surprised to learn that SQL does not have a TRIM function.

How do I update all of the records at once to remove the extra spaces?

Thank you!

Answer 1

You do have an RTRIM and an LTRIM function. You can combine them to get the trim function you want.

UPDATE Table
SET Name = RTRIM(LTRIM(Name))

Answer 2

You can use the RTrim function to trim all whitespace from the right. Use LTrim to trim all whitespace from the left. For example

UPDATE Table SET Name = RTrim(Name)

Or for both left and right trim

UPDATE Table SET Name = LTrim(RTrim(Name))

Answer 3

SQL Server does not have a TRIM function, but rather it has two. One each for specifically trimming spaces from the "front" of a string (LTRIM) and one for trimming spaces from the "end" of a string (RTRIM).

Something like the following will update every record in your table, trimming all extraneous space (either at the front or the end) of a varchar/nvarchar field:

UPDATE 
   [YourTableName]
SET
   [YourFieldName] = LTRIM(RTRIM([YourFieldName]))

(Strangely, SSIS (Sql Server Integration Services) does have a single TRIM function!)