How to get the value for column reading as '=4102' as 80 in sql - implement the same using select query without creating the function

Question

I have read only access to this particular database, hence I am not allowed to create any functions. I am trying to achieve the below one using select statement.

How to get the value for column reading as '=4*10*2' as 80 in sql - implement the same using select query without creating the function. I used the below query:

qty
----
10*4*2
4*3*1
5*1*1



select case when length=1 then substr(qty,1,1) 
            when length=2 then substr(qty,1,1)*substr(qty,2,1)
            when length=3 then substr(qty,1,1)*substr(qty,2,1)*substr(qty,3,1)
       else qty
       end 
       from (select replace(qty,'*','') as qty from table_quants);

The above query works fine until and unless the value does not contain 10s or zeroes. i.e,

qty
10*4*2   0   ------> which is not correct, I should get 80 instead of zero
4*3*1    12
5*1*1    5

Can someone pls help me out.

Which database do you use? You tagged both MS SQL Server and Oracle. — Littlefoot, Apr 05 '21 at 18:45
See also: [How to do an EVAL expression in SQL](https://stackoverflow.com/questions/51588489/how-to-do-an-eval-expression-in-sql). — Olivier Jacot-Descombes, Apr 05 '21 at 18:54
Are you just using `*` or are you using other arithmetic functions? If you are, do you expect to use BODMAS operator priority or are you expecting to evaluate expressions from left-to-right? — MT0, Apr 05 '21 at 21:08

score 2 · Answer 1 · answered Apr 05 '21 at 18:52

If it were Oracle, then

SQL> with table_quants (id, qty) as
  2    -- sample data
  3    (select 1, '10*4*2' from dual union all
  4     select 2, '4*3*1'  from dual union all
  5     select 3, '5*1*1'  from dual
  6    ),
  7  split_qty as
  8    -- split QTY column to rows
  9    (select id,
 10       qty,
 11       regexp_substr(qty, '[^*]+', 1, column_value) val
 12     from table_quants cross join
 13          table(cast(multiset(select level from dual
 14                              connect by level <= regexp_count(qty, '\*') + 1
 15                             ) as sys.odcinumberlist))
 16    )
 17  -- compute the result
 18  select id,
 19    qty,
 20    round(exp(sum(ln(val)))) result
 21  from split_qty
 22  group by id, qty
 23  order by id;

        ID QTY        RESULT
---------- ------ ----------
         1 10*4*2         80
         2 4*3*1          12
         3 5*1*1           5

SQL>

score 1 · Answer 2 · answered Apr 06 '21 at 03:25

XMLTABLE is often a shortcut for simple expressions, eg

SQL> create table t ( expr varchar2(20));

Table created.

SQL> insert into t values ('1+2');

1 row created.

SQL> insert into t values ('1+2*7-3+11');

1 row created.

SQL> select * from t, xmltable(t.expr);

EXPR                 COLUMN_VALUE
-------------------- ------------------------------
1+2                  3
1+2*7-3+11           23

score 0 · Answer 3 · answered Apr 05 '21 at 19:23

Same idea with SQL Server, just a bit shorter:

with table_quants (id, qty) as
  -- sample data
  (select 1, '10*4*2' union all
   select 2, '4*3*1'  union all
   select 3, '5*1*1' 
  )
  select id, exp( (select sum(log(value)) from string_split(qty,'*')) ) result
  from table_quants

outputs

id          result
----------- ----------------------
1           80
2           12
3           5

(3 rows affected)

MT0 · Answer 4 · 2021-04-05T22:06:49.557

In Oracle, you can use a recursive sub-query factoring clause and simple string functions (which, in this testing, was faster than CROSS JOINing with a correlated TABLE collection expression generated by CAST and MULTISET):

WITH multiplied_values ( qty, value, start_pos, end_pos ) AS (
  SELECT qty,
         1,
         1,
         INSTR( qty, '*', 1 )
  FROM   table_name
UNION ALL
  SELECT qty,
         value * SUBSTR( qty, start_pos, end_pos - start_pos ),
         end_pos + 1,
         INSTR( qty, '*', end_pos + 1 )
  FROM   multiplied_values
  WHERE  end_pos > 0
)
SELECT qty,
       value * SUBSTR( qty, start_pos ) AS value
FROM   multiplied_values
WHERE  end_pos = 0;

Which, for your sample data:

CREATE TABLE table_name ( qty ) AS
SELECT '10*4*2' FROM DUAL UNION ALL
SELECT '4*3*1' FROM DUAL UNION ALL
SELECT '5*1*1' FROM DUAL;

Outputs:

QTY    | VALUE
:----- | ----:
10*4*2 |    80
4*3*1  |    12
5*1*1  |     5

db<>fiddle here

The equivalent in SQL Server is:

WITH multiplied_values ( qty, value, start_pos, end_pos ) AS (
  SELECT qty,
         1,
         1,
         CHARINDEX( '*', qty, 1 )
  FROM   table_name
UNION ALL
  SELECT qty,
         value * CAST( SUBSTRING( qty, start_pos, end_pos - start_pos ) AS INT ),
         end_pos + 1,
         CHARINDEX( '*', qty, end_pos + 1 )
  FROM   multiplied_values
  WHERE  end_pos > 0
)
SELECT qty,
       value * CAST( SUBSTRING( qty, start_pos, LEN( qty ) - start_pos + 1 ) AS INT )
         AS value
FROM   multiplied_values
WHERE  end_pos = 0;

db<>fiddle here

score 0 · Answer 5 · answered Apr 06 '21 at 17:12

In Oracle you can do this:

with function evaluate_expression(p_expression in varchar2)
   return number
is
   l_cursor integer default dbms_sql.open_cursor;
   l_feedback integer default 0;
   l_retval number; /* with divisions we might get a NUMBER */
begin
   dbms_sql.parse(l_cursor,'begin :ret_val := ' || p_expression ||'; end;', dbms_sql.native );
   dbms_sql.bind_variable(l_cursor,':ret_val',l_retval);
   l_feedback := dbms_sql.execute(l_cursor);
   dbms_sql.variable_value(l_cursor, ':ret_val', l_retval);
   dbms_sql.close_cursor(l_cursor);
   return l_retval;
exception
   when others then
     dbms_sql.close_cursor(l_cursor);
     if (sqlcode=-1476) then 
         return 0;
     else
         raise;
     end if;
end;
select evaluate_expression('(3*(2+3)+10-1)/2')  from dual;

EVALUATE_EXPRESSION('(3*(2+3)+10-1)/2')
---------------------------------------
                                     12

Or if you have many expressions to evaluate you can create a view:

create view exprs as 
   select '(3*(2+3)+10-1)/2' expr from dual union all
   select '1+2+3+4+5+6' from dual union all
   select '1*2*3*4*5*6' from dual
;

and use the above to resolve the expressions:

with function evaluate_expression(p_expression in varchar2)
   return number
is
   l_cursor integer default dbms_sql.open_cursor;
   l_feedback integer default 0;
   l_retval number; /* with divisions we might get a NUMBER */
begin
   dbms_sql.parse(l_cursor,'begin :ret_val := ' || p_expression ||'; end;', dbms_sql.native );
   dbms_sql.bind_variable(l_cursor,':ret_val',l_retval);
   l_feedback := dbms_sql.execute(l_cursor);
   dbms_sql.variable_value(l_cursor, ':ret_val', l_retval);
   dbms_sql.close_cursor(l_cursor);
   return l_retval;
exception
   when others then
     dbms_sql.close_cursor(l_cursor);
     if (sqlcode=-1476) then 
         return 0;
     else
         raise;
     end if;
end;
select expr||'='||evaluate_expression(expr) expr 
  from (select expr from exprs)
;

EXPR                                                     
---------------------------------------------------------
(3*(2+3)+10-1)/2=12                                      
1+2+3+4+5+6=21                                           
1*2*3*4*5*6=720

How to get the value for column reading as '=4*10*2' as 80 in sql - implement the same using select query without creating the function

5 Answers5

How to get the value for column reading as '=4102' as 80 in sql - implement the same using select query without creating the function