Evaluate Formula from string Return the value of a string formula

Question

Hi All,

I have a table that looks like this

• MYTable

Field 1 as varchar(30) Named F_1 = abs(quantity_dispensed)

Field 2 as Float Named quantity_dispensed = 15 (15 is the quantity dispensed stored in the quntity_dispensed field)

HENCE:

Select F_1, quantity_dispensed From MYTable; <------ Returns

           **abs(quantity_dispensed)                          15**

How can I get the Value (15) from the formula displayed (F_1) from a simple query like this?

My Attempt:

Select Cast(F_1 as float) as Value_Calculated_From_F1_Formula. From MYTable;

To Return:

                **Value_Calculated_From_F1_Formula**

                                          **15**

Unfortunately, Cast() doesn't work.

Thank you!

Answer 1

Hi @Ross Crill

You can try using a cursor.

create table MYTable(F_1 varchar(30),quantity_dispensed Float)
insert into MYTable values
('abs(quantity_dispensed)',15),
('abs(quantity_dispensed)',-15),
('abs(quantity_dispensed)',-7.3),
('abs(quantity_dispensed)',-0.22);

Declare @sql nvarchar(max) = '' 
Declare CUR_B cursor forward_only for 
select 'update MYTable set quantity_dispensed =' + F_1 + 'from B where current of CUR_B' from MYTable; 
open CUR_B;
while 1 = 1 
begin   
  fetch CUR_B into @sql;   
  if not @@FETCH_STATUS = 0 break;
  exec(@Sql); 
end; 
close CUR_B; 
Deallocate CUR_B;

select F_1,quantity_dispensed as Value_Calculated_From_F1_Formula from MYTable;

Output:

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.