Sign are treated as Numeric in SQL Server but conversion to Numeric fails

Yesterday, when I was involved in the data cleansing activity as a part of my Data Migration project, I found a strange result, when I was converting numeric number to decimal. Let me share a piece of code

select

case

when ISNUMERIC(@NumberString) =1

       then cast(@NumberString as decimal(28,8))

else 

       cast('0.00' as decimal(28,8))

end as MyNo

Msg 8115, Level 16, State 6, Line 8

Arithmetic overflow error converting varchar to data type numeric.

Now, if I go back and check my logic, it seems all fine, the first check is made for whether the number string passed is numeric, if it turns true then cast it to decimal else set it to zero, then where this problem arose. Let’s interrogate

I am taking each part separately to understand the problem

select ISNUMERIC('-')

ISNUMERIC

-----------

1

This is bit shocking to me, lets do the check with ‘+’ sign.

select ISNUMERIC('-')

ISNUMERIC

-----------

1

Great, this means SQL Server consider sign as numeric character. Now lets come to our logic

 case

when ISNUMERIC(@NumberString) =1

       then cast(@NumberString as decimal(28,8))

else 

       cast('0.00' as decimal(28,8))

end as MyNo

so here it says, if @NumberString is 1 then cast the number to decimal, so in our case our input parameter to this check is numeric i.e. ‘-‘, so now it should enter true part of the case i.e.

then cast(@NumberString as decimal(28,8))

i.e select cast('-' as decimal(28,10))

Msg 8115, Level 16, State 6, Line 17

Arithmetic overflow error converting varchar to data type numeric.

Conclusion: Here it throws the error, which means the ISNUMERIC and CAST function are working in Sync.

Get your tea cup ready, lot more code to do :)

Post Reference: Vikram Aristocratic Elfin Share