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