# Cast imported Excel Column with length trouble

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 Martin

please show us the 2% that are not working since that is what you want help solving right?

Maybe I wasn’t too clear in my post, but the example I added is one of the 2% cases. Try it and you’ll see: if you shorten @price by one decimal it get’s perfectly rounded. For some reason very few Excel records are stored as a 13 and maybe more decimal string. No clue why! But sql reacts differently when casting this string to real once it has more then 12 decimals.

The cause of this problem is the standard behavior of the function isnumeric

``````select isnumeric('256,296296296296') = > 1
select isnumeric('256,2962962962964') => 0
``````

how to convert the second string to real, taking into account that it might be a textstring too

Assuming you're on SQL2012 or above, use Try_Cast rather than suffering with IsNumeric:

``IsNull(CAST(Round(TRY_CAST(REPLACE(@price,',','.') as real),2) as nvarchar(30)) , @price)``
1 Like

This works well if the string "is numeric" but throws the error when the string is not numeric!

Somehow I need to check first if the string is numeric or not. But how???? I could split it into 2 and check each part...

``````case when isnumeric(left(@Price, len(@Price)/2)) = 1 and isnumeric(Right(@Price, len(@Price) - len(@Price)/2)) = 1 Then IsNull(CAST(Round(TRY_CAST(REPLACE(@price,',','.') as real),2) as nvarchar(30)) , @price) ELSE @Price END
``````

ok. This works, but this is really painful

What error is it throwing? Try_Cast will return NULL if you can't cast the value so it shouldn't be possible for that line to throw an error.

No, you are right! I don't understand why I came to a different conclusion first. Nice one! BUT: Finally I had to move that table to a different server (v.10.50) where TRY_CAST doesn't work... too bad!

Ah, that's unfortunate. The next best strategy might be to just check for characters you aren't expecting with LIKE, i.e.

``select case when @price like '[^0-9,]' then @price else CAST(Round(CAST(REPLACE(@price,',','.') as real),2) as nvarchar(30)) end``
1 Like

That wouldn't stop a textstring with one number to be processed as real.

I stick to this one, spltting the string into 2 for the numeric check and then cutting 2 behind the point.

``````case when isnumeric(left(@Price, len(@Price)/2)) = 1 and isnumeric(Right(@Price, len(@Price) - len(@Price)/2)) = 1
THEN CAST(ROUND(CAST(left(REPLACE(@price,',','.'), CHARINDEX('.',REPLACE(@price,',','.')) +4) as real),2) as nvarchar)
ELSE @price END``````