LennyGray

asked on

# SQL using a string table field as a formula to calculate results in decimal form

I have a table which contains a data field which is in VARCHAR format that contains a formula. I would like to convert that formula into a decimal value in a new data field.

This is what one of the fields looks like:

CommonPrograms.dbo.cp_GreaterUDF(.05,(CommonPrograms.dbo.cp_GreaterUDF(.0225,225*.01) + 125*.01)) + 4.5

"CommonPrograms.dbo.cp_GreaterUDF" is a user-defined function that calculates the greater of two numbers. But there could be anything in that field. For example: the data field could contain: 2+3+6*100

Could you please send me code that will convert the VARCHAR field into a decimal data field which will now reflect the results of the formula?

Thanks!

This is what one of the fields looks like:

CommonPrograms.dbo.cp_Grea

"CommonPrograms.dbo.cp_Gre

Could you please send me code that will convert the VARCHAR field into a decimal data field which will now reflect the results of the formula?

Thanks!

ASKER CERTIFIED SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

something like this may work

```
declare @CustId int32
declare @Formula nvarchar(1000)
declare @sql nvarchar(4000)
declare CustList cursor for
select custid, formula from myTable where ...
OPEN CustList
FETCH NEXT FROM CustList INTO @CustId, @Formula
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql='update myTable set colX=' + @formula +' where CustID=' + @CustID;
exec @sql;
FETCH NEXT FROM CustList INTO @CustId, @Formula
END
CLOSE CustList
DEALLOCATE CustList
```

CAST (DataField AS DECIMAL (3, 2))