I was writing a query today and came across a set of data with an annoying leading space. Or what I thought was a leading space. My first instinct was to throw this around my field:

LTRIM(RTRIM([SKU]))

But the space was still there. OMGWTFBBQ! After a bit of googling, I started to wonder if this was REALLY a space. I decided to try this instead:

REPLACE([SKU], SUBSTRING([SKU], PATINDEX('%[^a-zA-Z0-9 '''''']%', [SKU]), 1), '')

Success! But what was the issue? Apparently, what looked like a leading space was more likely a carriage return, line feed, or some other non-printable ASCII character.

One thing to note: In this case, the need to use patindex or ltrim rtrim around a unique identifier like a SKU is a big red flag that something unexpected is happening during ETL. So while this solution might work in a pinch, if you find yourself in a similar situation, clean up that data!

Source: MSDN – PATINDEX