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.

Solution 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.

Solution 2

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

Solution 3

Get the Maximum value for e.g. any S.No or ID from your Examination table.Let us consider ID as u r serial number.

declare @TotCount int

select @Totcount=MAX(id) from @t
WHILE(@Totcount > 0)
BEGIN

IF EXISTS(SELECT * FROM EXAMINATION WHERE ID=@Totcount )
BEGIN
  UPDATE Examination
  Set Name=Rtrim(LTRIM(NAME))
  WHERE id=@Totcount
END
SET @Totcount = @Totcount - 1

END

SELECT * FROM Examination (NOLOCK)