Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# oracle split/substr/instrg help!!!!

Posted on 2003-11-10
Medium Priority
447 Views
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

dcmumbai
0
Question by:dcmumbai
• 2

LVL 22

Accepted Solution

Helena Marková earned 1000 total points
ID: 9720490
For Firstcol:
select substr(t.formula,1,instr(t.formula,'/',1,1)-1) from demo t;

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

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

LVL 22

Expert Comment

ID: 9720584
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);
0

Author Comment

ID: 9724492
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
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …