OraFAQ Forum: SQL & PL/SQL » problem in procedure

Home » SQL & PL/SQL » SQL & PL/SQL » problem in procedure

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

problem in procedure [message #217126]

Wed, 31 January 2007 14:58

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.

Report message to a moderator

Re: problem in procedure [message #217132 is a reply to message #217126]

Wed, 31 January 2007 15:44

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: problem in procedure [message #217148 is a reply to message #217126]

Wed, 31 January 2007 18:24

ebrian
Messages: 2794
Registered: April 2006

Senior Member

What have you tried so far? You could probably utilize just SQL for this problem.

Report message to a moderator

Re: problem in procedure [message #217182 is a reply to message #217126]

Thu, 01 February 2007 00:49

victoryhendry
Messages: 96
Registered: January 2007
Location: Bangalore

Member

So u have to use INITCAP Function

Report message to a moderator

Re: problem in procedure [message #217190 is a reply to message #217182]

Thu, 01 February 2007 01:47

Frank
Messages: 7901
Registered: March 2000

Senior Member

initcap will return Michael Jr instead of JR

Report message to a moderator

Re: problem in procedure [message #217342 is a reply to message #217190]

Thu, 01 February 2007 14:54

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

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

Report message to a moderator

Re: problem in procedure [message #217475 is a reply to message #217438]

Fri, 02 February 2007 07:01

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

Report message to a moderator

Previous Topic:	Resolved
Next Topic:	Help with Query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 04 18:18:12 CST 2024