SQL: Perform arithmetic operations on values in a column

Question

I have a varchar that contains a formula:

declare @formula varchar(50) = 'X + Y + Z'

and I also have a table:

+---+---+
| A | B |
+---+---+
| X | 1 |
+---+---+
| Y | 2 |
+---+---+
| Z | 3 |
+---+---+

Column A values are unique and the formula may change. For example, if the formula is set to 'X + Y + Z' then the result would be 6. And if the formula is set to 'Z - X + Y' then the result is 4. Operations include only addition and subtraction. How can I achieve this? Having a hard time looking for where to start.

I don't see any formulas in your data. Just the names of parameters/variables. — Gordon Linoff, Dec 23 '19 at 19:10
I think he's trying to write a formula into a separate field and have that formula be dynamically calculated, sort of like Excel would do. But I can't quite wrap my head around how it would behave or be executed, etc. Question doesn't make a lot of sense as written. — Marc, Dec 23 '19 at 19:14
@Marc There would be a varchar variable that contains the formula. And it would do the calculations for the values based on that formula. You're right, it would be like Excel. — Wabbage, Dec 23 '19 at 19:19
One way would be to dynamically pivot the data so that X, Y, and Z are columns and then apply the formula. There's not a way to apply formulas to row-based data in standard SQL. — D Stanley, Dec 23 '19 at 19:27
@DStanley How about doing a case statement inside a sum function? For example, sum(case when substring(@formula, charindex(A, @formula) - 2, 1) = '-' ) then -B ...) result — Wabbage, Dec 23 '19 at 19:52
Sure you could parse the formula then apply it to rows instead of columns, but it would be incredibly complex to do it generically. — D Stanley, Dec 23 '19 at 19:55

John Cappelletti · Accepted Answer · 2019-12-23T20:30:16.763

4

SQL Server does NOT support macro substitution, nor does it have an Eval()... this leaves Dynamic SQL

Example

Declare @YourTable Table ([A] varchar(50),[B] varchar(50))
Insert Into @YourTable Values 
 ('X',1)
,('Y',2)
,('Z',3)

Declare @formula varchar(50) = 'X + Y + Z'
Select @formula=replace(@formula,[A],[B])
 From  @YourTable

Exec('Select NewValue='+@formula)

Returns

NewValue
6

edited Dec 23 '19 at 20:30

answered Dec 23 '19 at 20:23

John Cappelletti

79,615
7
44
66

1

+1, elegant solution. Note, that the precedence order of _- (negative)_ is different compared to most programming languages and `-1.0/-2.0*3.0` is evaluated as `0.166666`, while most of us think the result should be `1.5` (as is commented [here](https://stackoverflow.com/questions/54513450/a-strange-operation-problem-in-sql-server-100-10010-0)). – Zhorov Dec 23 '19 at 21:51
1

@Zhorov Well, according to "My Dear Aunt Sally", that would be 0.166 Just in case I'm dating myself. My Dear Aunt Sally is a mnemonic for Multiplication, Division, Addition, Subtraction :) The current is PEMDAS – John Cappelletti Dec 23 '19 at 22:00
This helped me a lot. Thanks so much! – Wabbage Dec 24 '19 at 15:08
@Wabbage Always happy to help – John Cappelletti Dec 24 '19 at 15:11
@Wabbage Just a comment. When I do such things, I tend to "tokenize" with brackets, for example ( [CurrentAssets] - [Inventory] )/( [CurrentLiability] ) – John Cappelletti Dec 24 '19 at 15:38

score 2 · Answer 2 · answered Dec 23 '19 at 21:03

Just for fun, here is a modified option which will support a TABLE

Example

Declare @YourValues Table ([A] varchar(50),[B] varchar(50))
Insert Into @YourValues Values 
 ('X',1)
,('Y',2)
,('Z',3)

Declare @YourFormula Table (ID int,Formula varchar(50))
Insert Into @YourFormula Values
(1,'X + Y + Z'),
(2,'X - Y + Z')

Declare @SQL varchar(max) = stuff((Select concat(',(',ID,',',Formula,')') From @YourFormula For XML Path ('')),1,1,'') 
Select  @SQL=replace(@SQL,[A],[B])
  From  @YourValues

Create Table #TempResults (ID int,Calc money)
Exec('Insert Into #TempResults Select * from (values '+@SQL+')A(ID,Calc)')

Select * from #TempResults

Returns

ID  Calc
1   6.00
2   2.00

score 2 · Answer 3 · answered Dec 23 '19 at 21:04

This is rather crude and only works for +/- operands, however, I believe it satisfies the question.

DECLARE  @Formulas TABLE (Formula NVARCHAR(MAX))
INSERT INTO @Formulas SELECT 'Z-X+Y'

DECLARE @Values TABLE(Name NVARCHAR(50), Value DECIMAL(18,2))
INSERT @Values VALUES ('X',1),('Y',2),('Z',3)

;WITH MySplitFormula AS
(
    SELECT Value = SUBSTRING(Formula,Number,1) FROM @Formulas
    CROSS APPLY (SELECT DISTINCT number FROM master..spt_values WHERE number > 0 AND number <= LEN(Formula))V
)

,NormalizedFormula AS
(
    SELECT 
        DerivedOperations   = CASE WHEN F.Value IN('+','-') THEN F.Value ELSE NULL END,
        IsOperator          = CASE WHEN F.Value IN('+','-') THEN 1 ELSE 0 END,
        DerivedValues       = CASE WHEN F.Value IN('+','-') THEN NULL ELSE V.Value END
    FROM 
        MySplitFormula F
        LEFT OUTER JOIN @Values V ON V.Name = F.Value
    WHERE
        NOT F.Value IS NULL
),
ValidatedFormula AS
(
    SELECT DerivedOperations,DerivedValues FROM NormalizedFormula WHERE NOT((DerivedOperations IS NULL) AND (DerivedValues IS NULL))
),
Operators AS
(
    SELECT 
        OrderIndex=ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
        Operator=DerivedOperations FROM ValidatedFormula WHERE NOT DerivedOperations IS NULL
),
Operands AS
(
    SELECT 
        OrderIndex=ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
        Operand=DerivedValues FROM ValidatedFormula WHERE NOT DerivedValues IS NULL
)
,Marked AS
(
    SELECT 
        OP.OrderIndex,
        DoOperation = CASE WHEN OP.OrderIndex % 2 = 1 THEN  1 ELSE  0 END,
        Operand1 = Operand,
        Operator,
        Operand2 = LEAD(Operand) OVER(ORDER BY OP.OrderIndex) 
    FROM
        Operands OP
        LEFT OUTER JOIN Operators OPR ON OPR.OrderIndex = OP.OrderIndex
)
,MarkedAgain AS
(
    SELECT 
        *,
        CalculatedValue = CASE WHEN DoOperation = 1 THEN  
            CASE
                WHEN Operator = '+' THEN Operand1 + Operand2 
                WHEN Operator = '-' THEN Operand1 - Operand2 
                WHEN Operator IS NULL THEN 
                    CASE WHEN LAG(Operator) OVER(ORDER BY OrderIndex) ='+' THEN Operand1 ELSE -Operand1  END
            ELSE NULL
            END
        END
    FROM 
        Marked  
)
SELECT SUM(CalculatedValue) FROM MarkedAgain

SQL: Perform arithmetic operations on values in a column

3 Answers3