Trimming in SQL Server

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))

Leave a Reply

Your email address will not be published. Required fields are marked *