Solved

oracle split/substr/instrg help!!!!

Posted on 2003-11-10
3
442 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 250 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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 shows how to recover a database from a user managed backup

688 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