?
Solved

oracle split/substr/instrg help!!!!

Posted on 2003-11-10
3
Medium Priority
?
445 Views
Last Modified: 2013-12-12
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
0
Comment
Question by:dcmumbai
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 22

Accepted Solution

by:
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

by:Helena Marková
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

by:dcmumbai
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question