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

x
?
Solved

oracle split/substr/instrg help!!!!

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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 …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

773 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