Iteratively Calculate output from String '6*5 2/8' using SQL-CodePudding

I know if we use directly use in select statement it will work

select (6*5 2/4) from dual;

This will produce output 30.5 but my expectation is
6*5 = 30 2 = 32 / 4 = 8 it should return 8

is there a way to do calculation like this?

CodePudding user response：

The solution below assumes all inputs are non-negative integers, there are no parentheses and no spaces in the input expression, and there is no division by zero.

If the input has parentheses, those can be removed first. If there are spaces, they can be removed first too. If the inputs may include fractional numbers and/or negative numbers, that can be accommodated too, with a bit more work.

The strategy: first insert parentheses to force the order of evaluation; this is shown in the new_str intermediate result (in the prep subquery). We also need to change '/' to ' div ' for use in XQuery.

Then just use XQuery to evaluate the resulting arithmetic expression string.

with
  test_data (str) as (
    select '6*5 2/4'    from dual union all
    select '332'        from dual union all
    select '12 3*5/75'  from dual
  )
, prep (str, new_str) as (
    select str,
           replace(
             rpad('(', length(regexp_replace(str, '\d')), '(') ||
             regexp_replace(str, '([- */])', ')\1')
             , '/', ' div ')
    from   test_data
  )
select str, new_str,
       xmlcast(xmlquery(new_str returning content) as number) as result
from   prep;

STR        NEW_STR               RESULT
---------  -------------------  -------
6*5 2/4    (((6)*5) 2) div 4          8
332        332                      332
12 3*5/75  (((12) 3)*5) div 75        1

CodePudding user response：

You can use a recursive query:

WITH perform_calculation (value, calculation) AS (
  SELECT value,
         value
  FROM   table_name
UNION ALL
  SELECT value,
         CASE REGEXP_SUBSTR(calculation, '^(-?\d \.?\d*)([ */-])(-?\d \.?\d*)(.*)$', 1, 1, NULL, 2)
         WHEN ' ' THEN REGEXP_SUBSTR(calculation, '^(-?\d \.?\d*)([ */-])(-?\d \.?\d*)(.*)$', 1, 1, NULL, 1)
                        
                       REGEXP_SUBSTR(calculation, '^(-?\d \.?\d*)([ */-])(-?\d \.?\d*)(.*)$', 1, 1, NULL, 3)
         WHEN '-' THEN REGEXP_SUBSTR(calculation, '^(-?\d \.?\d*)([ */-])(-?\d \.?\d*)(.*)$', 1, 1, NULL, 1)
                       -
                       REGEXP_SUBSTR(calculation, '^(-?\d \.?\d*)([ */-])(-?\d \.?\d*)(.*)$', 1, 1, NULL, 3)
         WHEN '*' THEN REGEXP_SUBSTR(calculation, '^(-?\d \.?\d*)([ */-])(-?\d \.?\d*)(.*)$', 1, 1, NULL, 1)
                       *
                       REGEXP_SUBSTR(calculation, '^(-?\d \.?\d*)([ */-])(-?\d \.?\d*)(.*)$', 1, 1, NULL, 3)
         WHEN '/' THEN REGEXP_SUBSTR(calculation, '^(-?\d \.?\d*)([ */-])(-?\d \.?\d*)(.*)$', 1, 1, NULL, 1)
                       /
                       REGEXP_SUBSTR(calculation, '^(-?\d \.?\d*)([ */-])(-?\d \.?\d*)(.*)$', 1, 1, NULL, 3)
         END
         || REGEXP_SUBSTR(calculation, '^(-?\d \.?\d*)([ */-])(-?\d \.?\d*)(.*)$', 1, 1, NULL, 4)
  FROM   perform_calculation
  WHERE  REGEXP_SUBSTR(calculation, '^(-?\d \.?\d*)([ */-])(-?\d \.?\d*)(.*)$', 1, 1, NULL, 1) IS NOT NULL
)
SEARCH DEPTH FIRST BY value SET value_order
SELECT value,
       calculation
FROM   perform_calculation
WHERE  REGEXP_SUBSTR(calculation, '^(-?\d \.?\d*)([ */-])(-?\d \.?\d*)(.*)$', 1, 1, NULL, 1) IS NULL;

Which, for the sample data:

CREATE TABLE table_name (value) AS
SELECT '6*5 2/4' FROM DUAL UNION ALL
SELECT '6*5 2/4' FROM DUAL UNION ALL
SELECT '32/4*3/2 24/4 3/6' FROM DUAL UNION ALL
SELECT '3/2-3*5' FROM DUAL;

Outputs:

VALUE CALCULATION

3/2-3*5 -7.5

32/4*3/2 24/4 3/6 2

6*5 2/4 8

6*5 2/4 8

VALUE	CALCULATION
3/2-3*5	-7.5
32/4*3/2 24/4 3/6	2
6*5 2/4	8
6*5 2/4	8

db<>fiddle here

CodePudding user response：

Division takes precedent over addition so your calculation, by default, will be:

(6*5) (2/4)

If you want to change the default precedence then you need to use brackets:

(6*5 2)/4