About Me

My photo
Mumbai, Maharastra, India
He has more than 7.6 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in various database concepts. You can reach him at viki.keshari@gmail.com https://www.linkedin.com/in/vikrammahapatra/ https://twitter.com/VikramMahapatra http://www.facebook.com/viki.keshari

Search This Blog

Monday, December 1, 2014

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

No comments:

Post a Comment