I have a pricelist that I imported from Excel that needs special SQL-treatment in order to be cast into the format I want. The columns in Excel looks like:
Price ------- 108,15 Not available 29,72
These numbers are actually formated; their original size became clear when I imported them to SQL. I finally came up with this expression: (given @price = nvarchar(40) = '92,5925925925926')
CASE WHEN isnumeric(@price) = 1 THEN CAST(Round(CAST(REPLACE(@price,',','.') as real),2) as nvarchar(30)) ELSE @price END as Price
that does the job for 98% of the records, but fails in those few cases, where the imported number has more than 12 decimals (as in this case of @price). The result than becames unrounded as the whole number gets processed badly.
How can I deal with these cases? ... and why is the preview not working on this post