If you have imported a large amount of data, or had some rogue users, helpfully putting a space at the beginning and / or end of a string, then this simple update will clear that out:
UPDATE TableName SET ColumnName = RTRIM(ColumnName)
Of course, if you only want to select the data, you could also:
SELECT RTRIM(ColumnName) FROM TableNames
SQL Server 2005 has both a RTRIM and a LTRIM function, which removes spaces from the Left or the Right of the specified column.
Examples:
Cities
‘London ‘
‘Paris’
‘Milan’
In the above example, London, has a trailing space (space at the end of the word)
In this instance, using:
[
SELECT RTRIM(Cities) FROM TableName
Would return:
Cities
‘London’
‘Paris’
‘Milan’
Ok, so what about a leading space?
For example,
‘ London’
Well, as above, but use
SELECT LTRIM(Cities) FROM TableName
to return ‘London’
But what if we have:
‘ London ‘
That’s unfortunate. Somehow, you have data with a leading AND a trailing space.
Well, your in luck.
You can run RTRIM together with LTRIM
Like so:
SELECT LTRIM(RTRIM((ColumnName)) FROM TableName
The same would of course also work for UPDATE:
UPDATE TableName SET ColumnName = LTRIM(RTRIM(ColumnName))