[SOLVED] Extract the numbers interspersed with ‘+’

Extract the numbers interspersed with ‘+’

I have a column in my table in string format that contains different types of discounts:

integers
decimal numbers
compound discounts, i.e. whole numbers interspersed with the + symbol (e.g. 10+3, 5+3+2 etc.). In this case the discount should be interpreted as, in the example 10+3, discount of 10% and then further discount of 3%, which equals a discount of 12.7% (1- 0.9*0.97)

In the third case, how can I extract, one at a time, the numbers interspersed with the + so that I get the total discount? The problem is that the number of + is variable, there could be one, or two, or none. Thanks for your help!

Here's the table: https://www.dropbox.com/scl/fi/ifu871wh75913qiotpgn7/DiscountFormula.xlsx?rlkey=h3p0o12bvn58wawguxotwu7s1&st=r0il3ew1&dl=0

Solution

You are much better off storing the final result rather than (or as well as) the steps to calculate it but if you only have to worry about + and all the values are >= 0 and < 100 then you can use string_split and the trick to simulate a PRODUCT aggregate function.

DECLARE @T table(YourDiscountColumn VARCHAR(100));

INSERT @T VALUES  ('10+3'), ('5+3+2')

SELECT *
FROM @T
CROSS APPLY
(SELECT 1 - EXP(SUM(log(1 - value / 100.0))) AS v
        FROM    string_split(CASE 
                                WHEN YourDiscountColumn NOT LIKE  '%[^0-9+]%' THEN YourDiscountColumn 
                              END, '+')) X(compound_discount)

Or if you don't want to use the logarithm approach and can assume a max number of elements you could also do something like

SELECT compound_discount = 1 -
       ISNULL(1 - JSON_VALUE(array, '$[0]')/100.0,1) *
       ISNULL(1 - JSON_VALUE(array, '$[1]')/100.0,1) *
       ISNULL(1 - JSON_VALUE(array, '$[2]')/100.0,1) *
       ISNULL(1 - JSON_VALUE(array, '$[3]')/100.0,1) *
       ISNULL(1 - JSON_VALUE(array, '$[4]')/100.0,1) *
       ISNULL(1 - JSON_VALUE(array, '$[5]')/100.0,1)
FROM @T
CROSS APPLY
(VALUES(CONCAT('[', REPLACE(CASE 
                                WHEN YourDiscountColumn NOT LIKE  '%[^0-9+]%' THEN YourDiscountColumn 
                              END, '+', ','),']')))V(array);