Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Separate account

450162Apr 20 2006 — edited Nov 2 2010
<DIV><FONT face=Tahoma size=2><SPAN
class=939363000-21042006>Hi...</SPAN></FONT></DIV>
<DIV><FONT face=Tahoma size=2><SPAN class=939363000-21042006>I want to separate
account like below</SPAN></FONT></DIV>
<DIV><FONT face=Tahoma size=2><SPAN
class=939363000-21042006></SPAN></FONT> </DIV>
<DIV><FONT face=Tahoma size=2><SPAN
class=939363000-21042006>ACCOUNT</SPAN></FONT></DIV>
<DIV><FONT face=Tahoma size=2><SPAN class=939363000-21042006><A
href="mailto:Frank.Bishop@trovv.com">Frank.Bishop@trovv.com</A></SPAN></FONT></DIV>
<DIV><FONT face=Tahoma size=2><SPAN class=939363000-21042006><A
href="mailto:George.Bisk@aus.com">George.Bisk@aus.com</A></SPAN></FONT></DIV>
<DIV><FONT face=Tahoma size=2><SPAN class=939363000-21042006><A
href="mailto:Andrew.Hide@trovv.com">Andrew.Hide@trovv.com</A></SPAN></FONT></DIV>
<DIV><FONT face=Tahoma size=2><SPAN class=939363000-21042006><A
href="mailto:Martin.Loe@trovv.com">Martin.Loe@trovv.com</A></SPAN></FONT></DIV>
<DIV><FONT face=Tahoma size=2><SPAN class=939363000-21042006><A
href="mailto:Gandi.Malik@aus.com">Gandi.Malik@aus.com</A></SPAN></FONT></DIV>
<DIV><FONT face=Tahoma size=2><SPAN
class=939363000-21042006></SPAN></FONT> </DIV>
<DIV><FONT face=Tahoma size=2><SPAN class=939363000-21042006>Report
need</SPAN></FONT></DIV>
<DIV><FONT face=Tahoma size=2><SPAN
class=939363000-21042006></SPAN></FONT> </DIV>
<DIV><FONT face=Tahoma color=#0000ff size=2><SPAN
class=939363000-21042006><STRONG>First_name       
Last_name</STRONG></SPAN></FONT></DIV>
<DIV><FONT face=Tahoma size=2><SPAN
class=939363000-21042006>Frank                Bishop</SPAN></FONT></DIV>
<DIV><FONT face=Tahoma size=2><SPAN
class=939363000-21042006>George              Bisk</SPAN></FONT></DIV>
<DIV><FONT face=Tahoma size=2><SPAN
class=939363000-21042006>Andrew            
Hide</SPAN></FONT></DIV>
<DIV><FONT face=Tahoma size=2><SPAN
class=939363000-21042006>Martin               Loe</SPAN></FONT></DIV>
<DIV><FONT face=Tahoma size=2><SPAN
class=939363000-21042006>Gandi               
Malik</SPAN></FONT></DIV>
<DIV><FONT face=Tahoma size=2><SPAN
class=939363000-21042006></SPAN></FONT> </DIV>
<DIV><FONT face=Tahoma size=2><SPAN
class=939363000-21042006></SPAN></FONT> </DIV>
<DIV><FONT face=Tahoma size=2><SPAN class=939363000-21042006>Thank in
advanced</SPAN></FONT></DIV>
<DIV><FONT face=Tahoma size=2><SPAN
class=939363000-21042006></SPAN></FONT> </DIV>
<DIV><FONT face=Tahoma size=2><SPAN
class=939363000-21042006>Jonel</SPAN></FONT></DIV>

Comments

Aketi Jyuuzou 
select RegExp_Replace(ACCOUNT,'^([^.]+)\.([^@]+).*$','\1') as First_name,
 RegExp_Replace(ACCOUNT,'^([^.]+)\.([^@]+).*$','\2') as Last_name
 from
or
select RegExp_Replace(ACCOUNT,'^([^.]+).*$','\1') as First_name,
 RegExp_Replace(ACCOUNT,'^[^.]+\.([^@]+).*$','\1') as Last_name
 from
OracleSQLPuzzle
http://www.geocities.jp/oraclesqlpuzzle
450162
Aketi,
Thank for your script, unfortunately it couldn't execute by error " ORA-00904 Invalid column name "
This is for Oracle 10i, any solution for 9i ....?


Jonel

Message was edited by:
Jonel
11408
Hi Jonel
Would you like to try this statement:

select substr(ACCOUNT, 1, instr(ACCOUNT, '.') -1) first_name,
substr(ACCOUNT, instr(ACCOUNT, '.') + 1,
(instr(ACCOUNT, '@') -1) - instr(ACCOUNT, '.'))
last_name,
substr(ACCOUNT, instr(ACCOUNT, '@') + 1) email
from ...
450162
Perfect....thanks.
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 18 2006
Added on Apr 20 2006
4 comments
2,104 views