Home » SQL & PL/SQL » SQL & PL/SQL » Spilit Word in more than one field (Display data in select statement in following format)
Spilit Word in more than one field [message #308830] Tue, 25 March 2008 10:58 Go to next message
hasnainlakhani
Messages: 24
Registered: January 2007
Junior Member
Hello Guys

I want to display data in following format. can anybody inform me How to perform this

   Name                 firstname    secondname     thirdname
Hasnain Raza             Hasnain                      Raza
Mohd Hassan Ali           Mohd         Hassan          Ali
Syed Ali Raza Mohsin      Syed         Ali           Raza Mohsin


In short If the name contains 2 words then 1st word in name field will be firstname, and 2nd word in name field will be thirdname

If the name contain 3,4 or 5 words words then 1st word in name field will be firstname, 2nd word in name field will be secondname and remaining words in name fields will be thirdname

Waiting for reply




[moderator edit: code tags added; next time please add them yourself]

[Updated on: Tue, 25 March 2008 14:22] by Moderator

Report message to a moderator

Re: Spilit Word in more than one field [message #308833 is a reply to message #308830] Tue, 25 March 2008 11:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case: create table and insert statements.
Also post the result you want with these data.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: Spilit Word in more than one field [message #308869 is a reply to message #308830] Tue, 25 March 2008 14:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
You can use the SUBSTR and INSTR functions to select the individual names.
Re: Spilit Word in more than one field [message #309294 is a reply to message #308869] Thu, 27 March 2008 01:25 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Quote:
Try This Out.But Condition Is that In name column each Word must be seperated by one Space Character.


SELECT 
               SUBSTR(name,1,DECODE(INSTR(name,' ',1,1),0,LENGTH(name),INSTR(name,' ',1,1))) AS FirstName  ,
			   CASE   
			               WHEN INSTR(name,' ',1,1)>0 AND INSTR(name,' ',1,2)=0 THEN SUBSTR(name,INSTR(name,' ',1,1)+1,LENGTH(name)) 
   			               WHEN INSTR(name,' ',1,1)>0 AND INSTR(name,' ',1,2)>0 THEN SUBSTR(name,INSTR(name,' ',1,1)+1,INSTR(name,' ',1,2)-INSTR(name,' ',1,1)-1)
			   END AS MiddleName,
			   CASE 
			             WHEN INSTR(name,' ',1,2)>0 THEN SUBSTR(name,INSTR(name,' ',1,2)+1,LENGTH(name)) END AS LastName 
FROM 
              <table_name>
Re: Spilit Word in more than one field [message #309301 is a reply to message #309294] Thu, 27 March 2008 01:41 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please, keep your lines in 80 characters width.

Regards
Michel
Previous Topic: mutiple cursor
Next Topic: problem in converting Seconds to minute
Goto Forum:
  


Current Time: Sat Dec 03 08:15:04 CST 2016

Total time taken to generate the page: 0.09211 seconds