Home » SQL & PL/SQL » SQL & PL/SQL » function help
function help [message #244207] Tue, 12 June 2007 00:05 Go to next message
yog_23
Messages: 79
Registered: March 2007
Member
Any better way of writing this ?

Data looks lke
"sjhsjd","dddsfds",2,"3-10-2007","123232121" 


Function should remove the quotes, split the string and return the o/p based on position being passed in input.

    Function 
        SPLIT_STRING (the_list  varchar2, the_index number, delim varchar2 := ',')
    Return Varchar2    
    Is
        start_pos number;
	end_pos   number;
    
        Begin
            if the_index = 1 then
		start_pos := 1;
	    else
		start_pos := instr(the_list,delim,1,the_index - 1);
		if start_pos = 0 then
		    return null;
		else
		    start_pos := start_pos + length(delim);
		end if;
	    end if;
	    
	    end_pos := instr(the_list,delim,start_pos,1);
	    
	    if end_pos = 0 then
		return replace(substr(the_list,start_pos),'"');
	    else
		return replace(substr(the_list,start_pos,end_pos - start_pos),'"');
	    end if;
    End SPLIT_STRING;


This is called as :
SPLIT_STRING(SAMPLE_DATA1, 11)
SPLIT_STRING(SAMPLE_DATA1, 1)

Thank you
Yog
Re: function help [message #244211 is a reply to message #244207] Tue, 12 June 2007 00:37 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Try:

CREATE OR REPLACE FUNCTION split_string ( pString VARCHAR2, pIndex NUMBER, pSeparator VARCHAR2 := ',') RETURN VARCHAR2 IS
BEGIN
  RETURN REPLACE(SUBSTR(pSeparator || pString || pSeparator, INSTR(pSeparator || pString || pSeparator,pSeparator,1,pIndex) + 1,
             (INSTR(pSeparator || pString || pSeparator,pSeparator,1,pIndex + 1) - 1 - INSTR(pSeparator || pString || pSeparator,pSeparator,1,pIndex))),'"');
END;


HTH.
Michael
Previous Topic: Condition ending with (+) ? What is mean?
Next Topic: Reading XML-files
Goto Forum:
  


Current Time: Wed Dec 07 02:39:57 CST 2016

Total time taken to generate the page: 0.14614 seconds