T-SQL unable to CAST text to a number – Byte order mark – Dodgy character at start
14-Mar-1313 Leave a comment
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