When LTRIM() and RTRIM() do not work

When I analyzed the data from an Access database before import into our SQL database, I saw some strings with extra spaces at the end. So I thought I could simply use the RTRIM() function, I quickly put together a script and ran -> NOTHING WORKED

Tried couple times, the result still the same. It turns out there is a note in SQL Online Book

LTRIM does not remove white-space characters such as the tab or line feed characters…

And the spaces in the data are LFs (char(10)).  The solution is to replace those char(10) with a space (char(32)) before calling RTRIM()


UPDATE dbo.Cause_1
SET Result =  (RTRIM(REPLACE(Result, CHAR(10), CHAR(32))))

A more complete solution can be found at

http://blog.programmingsolution.net/sql-server-2008/removing-leading-and-trailing-white-space-characters-in-tsql/

 

Advertisements

One thought on “When LTRIM() and RTRIM() do not work

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s