Cast imported Excel Column with length trouble

Transact-SQL
1

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 :slight_smile:
Martin

2

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

3

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.

4

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

5

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
6

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

7

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.

8

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!

9

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
10

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