Home » SQL & PL/SQL » SQL & PL/SQL » problem in procedure
problem in procedure [message #217126] Wed, 31 January 2007 14:58 Go to next message
kavi123
Messages: 13
Registered: January 2007
Junior Member
Hi,
Iam working as an Oracle developer in Connecticut.I had a problem while writing a procedure in PL/SQL.I had a column father last name which in few records it was entered as MICHEAL JR,
JOHN,SR.
So now Iam trying to write a procedure through which my output can be in two seperate columns father last name and father generation id.

output:
Micheal JR
John SR

I have 657000 records in the table.

Please anyone kindly help me in doing this.

Thanku,
Have a great day,
Kavitha.
Re: problem in procedure [message #217132 is a reply to message #217126] Wed, 31 January 2007 15:44 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
> I am working as an Oracle developer in Connecticut.
Are you the brother-in-law of the boss?
What are/were your qualification for getting hired as an Oracle developer?
The PL/SQL Reference manual can be found at http://tahiti.oracle.com
Many fine coding example can be found at http://asktom.oracle.com
Re: problem in procedure [message #217148 is a reply to message #217126] Wed, 31 January 2007 18:24 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
What have you tried so far? You could probably utilize just SQL for this problem.
Re: problem in procedure [message #217182 is a reply to message #217126] Thu, 01 February 2007 00:49 Go to previous messageGo to next message
victoryhendry
Messages: 95
Registered: January 2007
Location: Bangalore
Member

So u have to use INITCAP Function
Re: problem in procedure [message #217190 is a reply to message #217182] Thu, 01 February 2007 01:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
initcap will return Michael Jr instead of JR
Re: problem in procedure [message #217342 is a reply to message #217190] Thu, 01 February 2007 14:54 Go to previous messageGo to next message
Ericle
Messages: 44
Registered: April 2006
Location: United States of America ...
Member

For the first column he could do
select initcap(substr(father_lastname,1,instr(father_lastname,' ')))||
       substr(father_lastname,instr(father_lastname,' ')+1,
       instr(father_lastname,',') - instr(father_lastname,' ')-1)
  from big_table


That would give Micheal JR

He could use similar code for the other column to get the John SR output, but I have a headache and can't figure that out right now. Razz

[Updated on: Thu, 01 February 2007 15:03]

Report message to a moderator

Re: problem in procedure [message #217438 is a reply to message #217342] Fri, 02 February 2007 03:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
  1  SELECT replace(replace(initcap(father_lastname),'Jr','JR'),'Sr','SR')
  2  From (select 'JOHN JR' father_lastname from dual union all
  3*       select 'JOHN SR' father_lastname from dual)
SQL> /

REPLACE(REPLACE(INITCAP(FATH
----------------------------
John JR
John SR
Re: problem in procedure [message #217475 is a reply to message #217438] Fri, 02 February 2007 07:01 Go to previous message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
did u attemted if yes then send the pl/sql what have you done?

Ashu
Previous Topic: Resolved
Next Topic: Help with Query
Goto Forum:
  


Current Time: Sat Dec 03 12:21:09 CST 2016

Total time taken to generate the page: 0.11408 seconds