String split in oracle

Just write this function in your db,

CREATE OR REPLACE function find_position(src varchar2,pos number,del varchar2) return varchar2 is
i number:=0;
ipos number:=0;
ipos1 number:=0;
begin
   while (i<pos) loop
      ipos1:=ipos;
        ipos:=instr(src,del,ipos+1);
        i:=i+1;
   end loop;
   IF (ipos=0) THEN
    return substr(src,ipos1+1);
   ELSE
    return substr(src,(ipos1+1),(ipos-ipos1));
   END IF;
end;
/

Now you can call thihs function like,
find_position(<src_string>,<which_position>,<delimeter_string>) will return you the actual characters., For example,

declare
i varchar2(2000):='first second third fourth';
begin
 dbms_output.put_line('first='||find_position(i,1,' '));
  dbms_output.put_line('first='||find_position(i,2,' '));
   dbms_output.put_line('first='||find_position(i,3,' '));
    dbms_output.put_line('first='||find_position(i,4,' '));
      end;
/
first=first
first=second
first=third
first=fourth

Hello  SaseeKrishnan

Try this

SELECT substr('Hello World', 1, instr('Hello World', ' ') )a,
       substr('Hello World', instr('Hello World', ' ')+1 ) b
FROM dual

If youwant to tockenize a string (separating it by a particular char) you'll need a function.

regards

I

SaseeKrishnan,
   Unfortunately, "in oracle" is extremely vague.  If you mean in a pl/sql procedure, then it's pretty simple:

   mystring := 'Hello World';
   var1 := substr(mystring, 1, instr(mystring,' ') - 1);
   var2 := substr(mystring, instr(mystring,' ') + 1);


   That would be variables.  If you REALLY meant columns in a select statement, then we need to look at a couple of places where you might need such a thing:

1) In a select list, from a table:

select mystring, substr(mystring, 1, instr(mystring,' ') - 1) FirstPart, substr(mystring, instr(mystring,' ') + 1) SecondPart
from mytable;

2) In a select with an inline select:

select mystring, (select substr(mystring, 1, instr(mystring,' ') - 1) from mytable where key=this.key) FirstPart,
         (select substr(mystring, instr(mystring,' ') + 1) from mytable where key=this.key) SecondPart
from mytable this;

3) In a Where clause:

select * from mytable
where substr(mystring, 1, instr(mystring,' ') - 1) = 'Hello' or substr(mystring, instr(mystring,' ') + 1)  = 'World';

Maybe an example or more detail would help us give you better advice.

Good luck!
DrSQL