String Splitting In Pl/sql

I have a table like this in oracle: servername description ObjectState ----------------------------------------------------

Solution 1:

Try this Procedure!!

This will work if the description pattern will be the same with the spaces specified and the flow like description,phonenum,Qty

createor replace procedure sp_split_str
as
     end_pos number:=0;
     strt_pos number :=1;
     CNT NUMBER:=0;
     v_desc varchar(200);
beginfor i in (select*fromtable) loop
            if(instr(i.description,':',1,1)=0) then
                   dbms_output.put_line('Server Name : '||i.servername);
                   dbms_output.put_line('Description : '||substr(i.description,1,instr(i.description,'#',1,1)-2));
                   dbms_output.put_line('Phone Number : '||substr(i.description,instr(i.description,'#',1,1)+1,instr(i.description,'Q',-1,1)-2-instr(i.description,'#',1,1)));
                   dbms_output.put_line('Qty : '||substr(i.description,instr(i.description,'Q',-1,1)+4));           
                   dbms_output.put_line('Object State : '||i.objectstate);
            elsefor J in1..(length(i.description)-length(replace(i.description,':','')))+1 loop
                          IF(J=1) THEN
                                 end_pos := instr(i.description,':',1,1);
                                 v_desc := substr(i.description,strt_pos,end_pos-2);
                          ELSE
                                 end_pos := instr(i.description,':',1,j);
                                 strt_pos := instr(i.description,':',1,j-1);
                          END IF;
                          dbms_output.put_line('Server Name : '||i.servername);
                          dbms_output.put_line('Description : '||substr(v_desc,1,instr(v_desc,'#',1,1)-2));
                          dbms_output.put_line('Phone Number : '||substr(v_desc,instr(v_desc,'#',1,1)+1,instr(v_desc,'Q',-1,1)-2-instr(v_desc,'#',1,1)));
                          dbms_output.put_line('Qty : '||substr(v_desc,instr(v_desc,'Q',-1,1)+4));          
                          dbms_output.put_line('Object State : '||i.objectstate);

                          CNT := CNT+1;
                          IF(CNT=J) THEN
                                  v_desc := substr(i.description,strt_pos+2);
                          ELSE
                                  v_desc := substr(i.description,strt_pos+2,end_pos-2);
                          END IF;
                   END loop;
            end if;
      END LOOP;

END;