Convert an arithmetic formula in a string to values

Question

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

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

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

Answer 1

You can try this. Hope this helps you. It takes an expression from t1 and puts the result in t2. You will not want to make t1 the actual table, because it is deleted from the table, so you can copy the actual value of the table to a temporary 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

Answer 2

-

DBO.EVAL : -

select dbo.eval(column_name) from table_name;

Answer 3

:

• @tabformula,
• @tabresult,

, .

, formula @tabresult.

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