Home » SQL & PL/SQL » SQL & PL/SQL » Splitting string into records (Oracle 10g, Windows XP)
Splitting string into records [message #364805] Wed, 10 December 2008 02:23 Go to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hi friends,

I am having a string of charactors which contains colon(:) within it.
Now I am facing problem in splitting this string by colon.

if string is like ab:cd:ef
output should be

ab
cd
ef

that means three records of a table.

is it possible?
please help.



[mod-edit: disabled smiles so : shows]

[Updated on: Wed, 10 December 2008 02:27] by Moderator

Report message to a moderator

Re: Splitting string into records [message #364807 is a reply to message #364805] Wed, 10 December 2008 02:31 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
yes it is possible.. i strongly feel similar quetions were answered in this forum itself. try search. when you lift your hands, i will do the search for you.
yours
dr.s.raghunathan
Re: Splitting string into records [message #364808 is a reply to message #364805] Wed, 10 December 2008 02:34 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
There are various methods. One method is to use Tom Kyte's str2tbl function, as demonstrated below.

SCOTT@orcl_11g> create or replace type myTableType as table
  2  of varchar2 (255);
  3  /

Type created.

SCOTT@orcl_11g> create or replace
  2  function str2tbl( p_string in varchar2 , p_delim in varchar2) return myTableType
  3  as
  4  	 l_string	 long default p_string || p_delim;
  5  	 l_data 	 myTableType := myTableType();
  6  	 n		 number;
  7  begin
  8    loop
  9  	   exit when l_string is null;
 10  	   n := instr( l_string, p_delim );
 11  	   l_data.extend;
 12  	   l_data(l_data.count) :=
 13  		 ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
 14  	   l_string := substr( l_string, n+length (p_delim) );
 15    end loop;
 16    return l_data;
 17  end;
 18  /

Function created.

SCOTT@orcl_11g> select * from table (cast (str2tbl ('ab:cd:ef', ':') as myTableType))
  2  /

COLUMN_VALUE
--------------------------------------------------------------------------------
ab
cd
ef

SCOTT@orcl_11g>

Re: Splitting string into records [message #364810 is a reply to message #364808] Wed, 10 December 2008 02:44 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Yet another less generic method:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2     s VARCHAR2(80) := 'ab:cd:ef';
  3  BEGIN
  4     WHILE instr(s, ':') > 0 LOOP
  5       dbms_output.put_line( substr(s, 1, instr(s, ':')-1));
  6       s := substr(s, instr(s, ':')+1);
  7     END LOOP;
  8     dbms_output.put_line( s );
  9  END;
 10  /
ab
cd
ef
Re: Splitting string into records [message #364816 is a reply to message #364805] Wed, 10 December 2008 02:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with data as (select ':'||'ab:cd:ef'||':' s from dual)
  2  select substr(s,instr(s,':',1,level)+1,instr(s,':',1,level+1)-instr(s,':',1,level)-1) w
  3  from data
  4  connect by level < length(s)-length(replace(s,':',''))
  5  /
W
----------
ab
cd
ef

3 rows selected.

Regards
Michel
Re: Splitting string into records [message #364818 is a reply to message #364805] Wed, 10 December 2008 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with data as (select ':'||'ab:cd:ef'||':' s from dual)
  2  select regexp_substr(s,'[^:]+',1,level) w
  3  from data
  4  connect by level < length(s)-length(replace(s,':',''))
  5  /
W
----------
ab
cd
ef

3 rows selected.

Regards
Michel
Re: Splitting string into records [message #364843 is a reply to message #364818] Wed, 10 December 2008 04:11 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Thanks to all intelligent members

Regards
Delna
Previous Topic: Query to copy columns from one table to another table
Next Topic: SQL Max(cnt)
Goto Forum:
  


Current Time: Fri Dec 09 05:36:07 CST 2016

Total time taken to generate the page: 0.08216 seconds