asked on

oracle split/substr/instrg help!!!!

Hi,

I want to spilt the following formula from oracle table column into different column.

table demo

srno act_code grp_code       formula
1      10      100      =(2000/30)*10

I want result as follows

srno act_code       grp_code formula        Firstcol Secondcol
1      10      100       =(2000/30)*10        2000        30

Thanks in advance.
dcmumbai

ASKER CERTIFIED SOLUTION

Helena Marková

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Helena Marková

I am sorry for my first answer, I have missed that formula is
=(2000/30)*10

For Firstcol:
select substr(t.formula,2,instr(t.formula,'/',1,1)-3) from demo t;

For Secondcol:
select substr(t.formula,instr(t.formula,'/',1,1)+1,instr(t.formula,')',1,1)-instr(t.formula,'/',1,1)-1) from demo t;

update demo set Firstcol=substr(formula,2,instr(formula,'/',1,1)-3),
Secondcol=substr(formula,instr(formula,'/',1,1)+1,instr(formula,')',1,1)-instr(formula,'/',1,1)+1);

dcmumbai

ASKER

Hi Henka,

You are the Oracle Guru!!!!

Excellent !!!. Thank you very much. I tried different way's but didn't realised for - instr and -1. I appreciate your prompt help. I may need one more help in generating dynamic sql. I have created procedure it works fine for one month . I need it dynamic instead of static month value.

I will post it once I have done first part.

Once again thank you very much.
dcmumbai