Trimming that hard to reach whitespace

Not all whitespace is created equal, and when LTRIM and RTRIM in T-SQL fails to remove leading and trailing space its easy to stare and panic.  Having worked on a lot of ETL pipelines and legacy databases lately I’ve encountered some pretty nasty data sources and eyebrow-raising moments.

In one particular case, I encountered fields with some 40 characters of whitespace padding which was throwing off some matching on natural key look-ups in my ETL pipeline. Ok, no problem, nothing LTRIM(RTRIM(….)) can’t solve, right?  Well, much to my surprise no trimming took place at all. After a moment reorganizing my eyebrows on my face I went at investigating the problem.

Some very handy T-SQL tools in this case is ACII( ) and UNICODE( ).  Taking a quick look at one of these whitespace characters quickly reveals the problem.  ASCII(RIGHT(wordExpression, 1)) shows the ASCII value of 160, a non-breaking space.  As it turns out, LTRIM and RTRIM only removes ASCII value 32.  

Technically speaking, I got off easy, as the case could have been a bunch of non-printable characters from some of my nasty data sources in which case cleansing my data sources would have been much more tedious.  Luckily, in my case I just need to handle another type of whitespace and this can easily be sorted out with a TRIM function.

CREATE FUNCTION TRIM(@string VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
	RETURN LTRIM(RTRIM(REPLACE(@string, CHAR(160), CHAR(32))))
END

One Response to “Trimming that hard to reach whitespace”

  1. Hello blogger, i found this post on 25 spot in google’s search results.
    I’m sure that your low rankings are caused by high bounce rate.
    This is very important ranking factor. One of the biggest reason for
    high bounce rate is due to visitors hitting the back button.
    The higher your bounce rate the further down the search
    results your posts and pages will end up, so having reasonably low
    bounce rate is important for improving your rankings naturally.
    There is very handy wp plugin which can help you. Just
    search in google for:
    Seyiny’s Bounce Plugin

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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: