Converting arithmetic formula in a string into a v

问题:

I have a table in which all entries are in form of arithmetic formulas (i.e. 1+2+3 etc).

In this table all columns are of type varchar. The table has many columns like this.

I want to calculate formula and insert the values into another. Any suggestions on how to achieve this?

回答1:

You can try this. I hope this can assist you. It takes expression from t1 and put the result in t2. You won't want to make t1 the actual table as it deletes from the table, so you can copy the actual table value to a temp table

declare @t as table(val varchar(20))
declare @t2 as table(val varchar(20))
insert into @t values
('1+3'),
('2*3'),
('9+3*2')


declare @exp varchar(20)
while(exists(select 1 from @t))
begin
    select top(1) @exp = val from @t

    insert into @t2
    exec ('select '+@exp)  

    delete top (1) from @t
end

select * from @t2

Result

val
------
4
6
15

You can check these links Similar question here and Convert string with expression to decimal

回答2:

Follow the link - Convert string with expression to decimal

After you create function DBO.EVAL , just Run :-

select dbo.eval(column_name) from table_name;

回答3:

You can declare two table variables:

• @tabformula in which will be stored the formulas
• @tabresult in which will stored the results

There is an ID column that will be used for joining the two tables.

After filling the first table with the required formulas, you declare a cursor, and then you execute the formula and store the result into the @tabresult table.

declare @tabformula as table (id int identity(1,1), formula
varchar(200)) declare @tabresult as table (id int, result int)

insert into @tabformula(formula) values('1+4+6+7') ;
insert into @tabformula(formula) values('10+4+60+7'); 
insert into @tabformula(formula) values('1+4+6+70') ;
insert into @tabformula(formula) values('1+44+65+7');


declare c cursor for select ID,formula from @tabformula declare @id as
int declare @formula as varchar(200)

open c fetch c into @id,@formula while @@fetch_status=0 begin print
@formula insert into @tabresult (id,result) exec( 'select '+ @id +
','+@formula ) fetch c into @id,@formula end close c deallocate c
select T1.id,t1.formula,T2.result from @tabformula t1 inner join
@tabresult t2 on t1.id=t2.id