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:

Of course, if you only want to select the data, you could also:

SQL Server 2005 has both a RTRIM and a LTRIM function, which removes spaces from the Left or the Right of the specified column.


‘London ‘

In the above example, London, has a trailing space (space at the end of the word)
In this instance, using:

Would return:

Ok, so what about a leading space?
For example,

‘ London’
Well, as above, but use

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:

The same would of course also work for UPDATE: