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

How to convert 0 to 1 and 1 to 0 in table object

Say I am having a table ZeroOneZero with following data

select * from ZeroOneZero

name       Active

---------- ------

Prakriti   0

Aaliya     1

Falak      0

Snehal     1

(4 row(s) affected)

Now what I want is to convert 0 values in Active column to 1 and vice versa. It seems very easy to do, yes absolutely it is, but you need make sure you don’t have to use where clause in Update statement else you will end up getting something unexpected….

Let’s check out how to get done with this problem, the Case clause is the solution:

update ZeroOneZero

set Active =

      (case when Active = 0 then 1

              when Active = 1 then 0

      end)

(4 row(s) affected)

/*Now lets fire select query to see the result*/

select * from ZeroOneZero

name       Active

---------- ------

Prakriti   1

Aaliya     0

Falak      1

Snehal     0

(4 row(s) affected)

Yes we are through.

Coding a Psyche Game

Post Reference: Vikram Aristocratic Elfin Share

Case with Update statement Or Conditional Update

There are certain situation where we need some updation  on the basis of condition. For example  if name contain ‘Kruthika’ update it to ‘Kruthik’.

Our table is containing the following records

select * from user_tab

user_id     user_name                                          active

----------- -------------------------------------------------- ------

1           Kruthika                                           1

2           Orpita                                             1

3           Aalap                                              1

4           Trishona                                           1

5           Shobna                                             1

6           Eekalabya                                          1

7           Phaneesh                                           1

8           Kishalaya                                          1

9           Mrinmoy                                            1

10          Satyajit                                           1

16          suranjan                                           1

(11 row(s) affected)

You might be thinking this update can be accomplished with the help of were condition in update statement. Correct it goes like this

update user_tab

set user_name = 'Kruthik'

where user_name = 'Kruthika'

(1 row(s) affected)

select * from user_tab

user_id     user_name                                          active

----------- -------------------------------------------------- ------

1           Kruthik                                            1

2           Orpita                                             1

3           Aalap                                              1

4           Trishona                                           1

5           Shobna                                             1

6           Eekalabya                                          1

7           Phaneesh                                           1

8           Kishalaya                                          1

9           Mrinmoy                                            1

10          Satyajit                                           1

16          suranjan                                           1

(11 row(s) affected)

But consider a situation where you need to update ‘Trishona’ with ‘Trishon’ and ‘suranjan’ with ‘suranjana’.

Is it possible with WHERE clause in Update statement. ANSWER IS NO.

Then what is option left for such update, Here comes CASE, let see how this update can be done with CASE

update user_tab

set user_name = (case user_name

                              when 'Trishona' then 'Trishon'

                              when 'suranjan' then 'suranjana'

                              else user_name end)

user_id     user_name                                          active

----------- -------------------------------------------------- ------

1           Kruthika                                           1

2           Orpita                                             1

3           Aalap                                              1

4           Trishon                                            1

5           Shobna                                             1

6           Eekalabya                                          1

7           Phaneesh                                           1

8           Kishalaya                                          1

9           Mrinmoy                                            1

10          Satyajit                                           1

16          suranjana                                          1

(11 row(s) affected)

I really like this keyword a lot of thing can be accomplished with CASE.

Post Reference: Vikram Aristocratic Elfin Share