Trim on a table column in SQL Server 2005

Question

I have a table called Examination and a column Name. In this Name column lot of values are with leading spaces and trailing spaces. I want to know how to update this column so that there are no spaces in the values.

Answer 1

If you want to fix the existing data:

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

Generally in your application layer you should "clean" the data before it goes into the database.

The RTRIM function will remove trailing space on the right, and LTRIM will remove it on the left.

In C#, you can trim both the left and the right using the Trim method on string, which will return a new string trimmed.

Answer 2

UPDATE Examination SET [Name] = LTRIM(RTRIM([Name]))