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
