-3

Please any one can give some idea , how to replace set of String with value in Oracle .eg :-

String Formula: (20+OFR)/(10-OGR) need to Replace OFR=100 and OGR=50 .

String Formula is dynamically changing.

Expected result (20+OFR)/(10-OGR)= (20+100)/(10-50)

michaelok
  • 1,124
  • 1
  • 13
  • 20
Rohit Kumar
  • 5
  • 2
  • Shouldn't an iterated application of `replace` suffice for this kind of job ? In the vein of `replace ( , 'OFR', ), 'OGR', )` ? Do I read you wrong? – collapsar Feb 26 '16 at 10:01
  • Thanks Musakkhir , may you give some idea to iterate this String . – Rohit Kumar Feb 26 '16 at 11:37
  • Is this formula coming from a column? Will the codes to substitute always be right before the closing paren? Will you then be selecting it in PL/SQL code, doing substitutions and using the formula? Please provide more information on the environment and intended usage by editing your post using the edit link and updating the tags. – Gary_W Feb 26 '16 at 15:28

2 Answers2

0

I don't know what you are trying to achieve. But if you want to create a dynamic formula, you have to use a select statement as: select 'Your formula' from dual in order to get the result.

Here is a sample code:

declare
v_ofr NUMBER :=100 ;
v_ogr NUMBER := 50;
v_sql   VARCHAR2(500);
BEGIN

v_sql := 'select (20+ ' || v_ofr || ')/(10-' || v_ogr ||') from dual';

execute immediate v_sql;

DBMS_OUTPUT.PUT_LINE ( v_sql);

end;
Burhan Khalid Butt
  • 275
  • 1
  • 7
  • 20
  • Thanks @Burhan , I am not expecting like this , i have to create a SP in which Formula column have (20+OFR)/(10-OGR) which can change dynamic as user choice , now where ever they put String like OFR , OGR will replaced with corresponding value . – Rohit Kumar Feb 26 '16 at 10:11
0

To answer your basic question ' how to replace set of String with value in oracle', take a look at the support for regex in Oracle. For example, you could achieve your example with the following:

select 
      REGEXP_REPLACE( REGEXP_REPLACE('(20+OFR)/(10-OGR)',
                     'OFR',
                     '100'), 'OGR',50)
      as "names after regexp"
      from DUAL;

Beyond that, you'd probably have more coding, something similar to what Burhan showed above. Hope that helps.

michaelok
  • 1,124
  • 1
  • 13
  • 20