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

Finding lowest date among each category

Here we have data scattered in table like below

Subject                        Date

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

Data & File Structure          2014-11-18 10:12:57.767

System Software                2014-11-26 10:12:57.767

DBMS                           2014-11-18 10:12:57.767

Data & File Structure          2014-11-24 10:12:57.767

DBMS                           2014-11-16 10:12:57.767

Data & File Structure          2014-11-25 10:12:57.767

System Software                2014-11-23 10:12:57.767

Data & File Structure          2014-11-24 10:12:57.767

DBMS                           2014-11-29 10:12:57.767

Now we are interested in finding lowest date for each subject.

Let’s create table and try to find the solution using t-sql concepts.

create table Class

([Subject] varchar(30),

[Date] datetime)

Command(s) completed successfully.

So here our table is ready, lets insert data according to problem statement.

insert into Class

select 'Data & File Structure', getdate() - 12 union all

select 'System Software', getdate() - 4 union all

select 'DBMS', getdate() - 12 union all

select 'Data & File Structure', getdate() - 6 union all

select 'DBMS', getdate() - 14 union all

select 'Data & File Structure', getdate() - 5 union all

select 'System Software', getdate() - 7 union all

select 'Data & File Structure', getdate() - 6 union all

select 'DBMS', getdate() - 1

Now our table is ready with data. Ok, its time to fire a query taking both the column under group by

select Subject,Date from Class

group by Subject, Date

Subject                        Date

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

Data & File Structure          2014-11-18 12:33:53.440

Data & File Structure          2014-11-24 12:33:53.440

Data & File Structure          2014-11-25 12:33:53.440

DBMS                           2014-11-16 12:33:53.440

DBMS                           2014-11-18 12:33:53.440

DBMS                           2014-11-29 12:33:53.440

System Software                2014-11-23 12:33:53.440

System Software                2014-11-26 12:33:53.440

Now, we are interested in finding lowest date among each subject, so we can apply MIN aggregate function to date field taking subject as group by field.

select Subject,min(Date) from Class

group by Subject

Subject                       

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

Data & File Structure          2014-11-18 12:33:53.440

DBMS                           2014-11-16 12:33:53.440

System Software                2014-11-23 12:33:53.440

Yup we got our work done; this is what we were expecting.

Get your Sofa in washroom and do code J

Post Reference: Vikram Aristocratic Elfin Share

Calling a procedure from Trigger?

With a small demonstration we will see how we can call a stored procedure from a trigger.

Here we are creating a table Employee

create table tbl_Employee

(id int identity(1,1),

name varchar(20))

Command(s) completed successfully.

Now lets insert couple of records into newly created table.

insert into tbl_Employee

select 'Akansha'  union all

select 'Aurpita'

Since our table is ready, we can now create a procedure, which we will be calling inside a trigger.

create procedure sp_DisplayEmployee

as

       select * from tbl_Employee

go

Command(s) completed successfully.

At this point, we have everything ready, table, procedure, so now turn to create trigger on table Employee for Insert and Update operation and call sp_Display function in it.

We have very simple logic inside trigger i.e. whenever any insert or update commands fired on Employee table, this trigger will comes into picture and display the original data in the table.

create trigger trg_Employee

on tbl_Employee

for insert,update

as

       exec sp_DisplayEmployee

go

Command(s) completed successfully.

Our trigger is now ready, lets fire update statement to see whether it will fire associated trigger.

update tbl_Employee

set name = 'Lopamudra'

where id =4

id     name

1      Akansha

2      Aurpita

Great, so with update statement, it fires trigger and in return trigger call sp_DisplayEmployee stored procedure to display records.

Conclusion: You can call procedure inside trigger.

Get your washroom ready for your coding :)

Post Reference: Vikram Aristocratic Elfin Share

How to convert scientific number to decimal?

Suppose you have number in scientific form and you want to migrate these number to a decimal field. How you approach to do this since cast function to decimal fails with scientific numbers

Now if have a number like this

'0.23e10'

Lets try to cast it to decimal(10,5)

select cast('0.23e10' as decimal(10,5))

Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to numeric.

So its fail. Ok lets try to cast it to float type.

select cast('0.00023e4' as float)

FloatCasted

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

2.3

That’s great, now lets try to convert to decimal.

select cast(cast('0.00023e4' as float) as decimal(10,5)) decimalCasted

FloatCasted

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

2.30000

That’s workaround for scientific to decimal conversion.

Get your tea cup and start coding J

Post Reference: Vikram Aristocratic Elfin Share