Celtic Coding Solutions Blog

Celtic Coding Solutions Blog - for everything computer related

SQL Server T-SQL SELECT Varchar not matching - CHAR(160)

First time I've come across this.  Imported some data from Excel 2010 into SQL Server Table.  Tried to match against some other data already in SQL Server and notice certain values where not matching even though to the naked eye these look exactly the same and the abortion risk field is the same varchar.  

So did a quick HexEdit check (thanks Visual FoxPro for this handy tool) and found the following:-

Looks after pill like in Excel or in the import process a space (last 3 characters) has been translated into:-

CHAR: 160

HEX: A0

Normally I see space in SQL as:-

CHAR: 32

HEX: 20

So a quick REPLACE(Field, CHAR(160), CHAR(32)) and now the values match.

Comments are closed