T-SQL unable to CAST text to a number – Byte order mark – Dodgy character at start

Scenario

Imported some data from Excel in form “156 x 54 x 16mm”. Parsed the text to get the 156. However this would not convert to a numeric.

Error converting data type nvarchar to numeric

After some exploration I discovered that the cursor required to steps through before the number. So a character was there that SQL Server could not display. To find this I used:

SELECT Code, UNICODE(LEFT(DimensionsTextImport, 1)) AS UnicodeTest FROM Product.<tablename>

And discovered text which would not convert had Unicode 65279

Solution

Create a new column and fill this with the data from the first column after a CASE statement.

UPDATE Product.Dimensions
SET DimensionsText = CASE
WHEN UNICODE(LEFT(DimensionsTextImport, 1)) = 65279 THEN SUBSTRING(DimensionsTextImport, 2, 50)
ELSE DimensionsTextImport END

With reference to:

http://stackoverflow.com/questions/6441208/unable-to-replace-char63-by-sql-query

http://www.fileformat.info/info/unicode/char/feff/index.htm

http://en.wikipedia.org/wiki/Byte_Order_Mark