OraFAQ Forum: SQL & PL/SQL » Seperate the values in a Column

Home » SQL & PL/SQL » SQL & PL/SQL » Seperate the values in a Column

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Seperate the values in a Column [message #20410]

Tue, 21 May 2002 13:55

V Guru
Messages: 3
Registered: May 2002

Junior Member

Hello People,
I have a column containing the following values
Last_name
---------
John Jr
Peter, Jr
Smith,Jr

I want to seperate the values in this column meaning
I want John seperately and Jr seperately. How would I do that? I could use instrb() but then some of the values have a space between name and Jr.

Any suggestions??

Thanks,
V

Report message to a moderator

Re: Seperate the values in a Column [message #20412 is a reply to message #20410]

Tue, 21 May 2002 16:24

siva
Messages: 58
Registered: August 1999

Member

Hello,

I created a table called test with column x and
i inserted the following rows.

X
--------------------
xxx Jr
yyyy,Jr
zzzz ,Jr
aaaa, Jr

using this query we can get the results in the way you
mentioned.Hope this helps you.

select
substr(x,1,instr(replace(x,'Jr','*'),'*')-1) col1,
substr(x,instr(replace(x,'Jr','*'),'*')) col2
from test;

COL1 COL2
-------------------- ----
xxx Jr
yyyy, Jr
zzzz , Jr
aaaa, Jr

Thanks,
Siva.

Report message to a moderator

Previous Topic:	How to enable "ORA-00439: feature not enabled: OLAP Window Functions" .
Next Topic:	Select in Set statement in update

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 09 23:08:54 CDT 2024