Home » SQL & PL/SQL » SQL & PL/SQL » Substr and Instr (Oracle 10g,XP)
Substr and Instr [message #403524] Sat, 16 May 2009 06:14 Go to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

In my table details i have following data ,I want to split the data in to n parts which is equal to number of space between data (:ie '10 SPRING DRIVE WAY' I want to split this into four columns )


create table details
( val varchar2(4000));


Insert into DETAILS
   (VAL) Values
   ('5806 SW 103RD STREET RD');
Insert into DETAILS
   (VAL) Values
   ('10 SPRING DRIVE WAY');
Insert into DETAILS
   (VAL) Values
   ('?  SE 130 ST');
Insert into DETAILS
   (VAL) Values
   ('OLD SE 157TH PL JACKSONVILLE RD');
Insert into DETAILS
   (VAL) Values
   ('4152 SW 140TH STREET RD');
Insert into DETAILS
   (VAL) Values
   ('0000NO SITUS AVAILABLE');
Insert into DETAILS
   (VAL) Values
   ('1  HICKORY TRAK');
COMMIT;


please help me
Thanks in Advance
Re: Substr and Instr [message #403525 is a reply to message #403524] Sat, 16 May 2009 06:26 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
What output do you want?

regards,
Delna
Re: Substr and Instr [message #403527 is a reply to message #403525] Sat, 16 May 2009 06:31 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

If my data are '10 SPRING DRIVE WAY' ,'1 HICKORY TRAK' .I want output in this way

one  two       thr    four
---  ---      ----    ----
10   SPRING   DRIVE   WAY
1    HICKORY  TRAK  
Re: Substr and Instr [message #403528 is a reply to message #403525] Sat, 16 May 2009 06:32 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Search this forum for 'row to column' or 'pivot'.

regards,
Delna
Re: Substr and Instr [message #403538 is a reply to message #403527] Sat, 16 May 2009 12:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for "string list" or the like, this is a FAQ and your title seems to show that you know how to solve this.
You just need a "row generator" to break the string into values and "row to column" method.

Regards
Michel

Re: Substr and Instr [message #403757 is a reply to message #403538] Mon, 18 May 2009 09:05 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

Sir you please have a look at my post i don't need a row generator , i need a splitting technique ,to split particular words into the number of (n+1) number of spaces
Re: Substr and Instr [message #403758 is a reply to message #403757] Mon, 18 May 2009 09:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It seems you know how to do it. Why don't you show us?
What is your problem using instr and substr to take the first, second... parts?

Regards
Michel
Re: Substr and Instr [message #403885 is a reply to message #403758] Tue, 19 May 2009 06:14 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

select fir,sec,third,address from (
  select substr(ADDRESS,1,instr(ADDRESS,' ',1)-1) fir ,replace(substr(ADDRESS,1,instr(ADDRESS,' ',7)-1),
substr(ADDRESS,1,instr(ADDRESS,' ',1))) sec  , replace(substr(ADDRESS,1,instr(ADDRESS,' ',10)-1),
substr(ADDRESS,1,instr(ADDRESS,' ',7))) third  , ADDRESS
  from (select   val ADDRESS  from details t  ) )


This is how i did ,but output is not correctly getting ,using this scrap idea please have a try .

Thanks in Advance
Re: Substr and Instr [message #403892 is a reply to message #403885] Tue, 19 May 2009 06:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
INSTR as a parameter that allow you to choose the first, second, third... instance of the character you're searching.

Regards
Michel
Re: Substr and Instr [message #404020 is a reply to message #403892] Wed, 20 May 2009 02:08 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

Sir is there any other techniques apart from my way of approach ?
Re: Substr and Instr [message #404024 is a reply to message #404020] Wed, 20 May 2009 02:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
rakeshramm wrote on Wed, 20 May 2009 09:08
Sir is there any other techniques apart from my way of approach ?

It is the right way but use the full power of the functions.

Regards
Michel

Re: Substr and Instr [message #404045 is a reply to message #404024] Wed, 20 May 2009 03:33 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

Sir will you give me some example please .
Re: Substr and Instr [message #404061 is a reply to message #404045] Wed, 20 May 2009 04:28 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions068.htm#i77598

Regards
Michel
Previous Topic: Hierarchial queries with union.
Next Topic: SQL query
Goto Forum:
  


Current Time: Wed Dec 07 22:31:14 CST 2016

Total time taken to generate the page: 0.16932 seconds