A function for handling character string in Oracle/PLSQL

　　Original works, reproduced and please indicate the source

　　When we get a string with character ':', and we wish to split it without ':' and get each one into variable array type. The following way is one of solutions, maybe it is you want. Take it !

Alogrithm thought: the author has forgotten that, cause this code was written for a long time before. the detail can be read according to the comments.

The below function has been verified in real project environment, so you can use it directly.

CREATE OR REPLACE FUNCTION to_sp_list
    (SELF IN TypeName, p_input IN VARCHAR2) RETURN Varchar2Array  IS
    l_size INTEGER ;           --the number of substring without ':'
    l_sp_array Varchar2Array ; --l_sp_array, a array storages Varchar2 a datatype, the type is Varchar2Array
    l_i INTEGER:= 1;           --the index helps to get the next string
    i INTEGER;                 --use it to judge the condition of exitting loop
    BEGIN
        l_size  := 0;
        l_sp_array  := NULL;

        BEGIN
            l_sp_array  := Varchar2Array ();  --instance Varchar2Array object

　　　       --according to the first occurrence of ':', if the position > 0, continue runnning
            if instr(p_input, ':', 1, 1) > 0 THEN
                l_size := length(p_input) - length(replace(p_input, ':', '')) + 1;  --get the number of substring without ':'
                l_sp_array.EXTEND(l_size); --there are how many substring, the capacity of l_sp_array should be increased by how many units
                l_sp_array(l_i):=substr(p_input, 1, instr(p_input, ':', 1, l_i)-1); --get the first string into the array l_sp_array
                l_i  := l_i  + 1; --index plus 1
                i := 1;

　　　           --if l_size > 2, it means the original string has more than two ':', so the next, we continue to get next string into the array by the loop
                if l_size > 2 THEN
                    WHILE i < l_size - 1   
                    LOOP
                        l_sp_array(l_i):=substr(p_input, instr(p_input, ':', 1, l_i-1)+1, instr(p_input, ':', 1, l_i)-1-instr(p_input, ':', 1, l_i-1));
                        l_i := l_i + 1;
                        i := i + 1;
                    END LOOP;

　　　               --while exitting loop, there will be the last string at the end of the original string, the below sentence will put the last string into the array
                    l_sp_array(l_i):=substr(p_input, instr(p_input, ':', 1, l_i-1)+1);

　　　           --otherwise, there is only one character ':' in the original string. The next, we get the second string and put it into the array directly
                else
                    l_sp_array(l_i):=substr(p_input, instr(p_input, ':', 1, 1)+1);
                end if;

　　         --otherwise, there's no ':' in the original string. the capactiy of array should be expanded with one unit, then, put the original string into the array
            else
                l_sp_array.EXTEND;
                l_sp_array(1) := p_input;
            end if;
        EXCEPTION
            WHEN others THEN
            BEGIN
                dbms_output.put_line('======Exception Message======'||sqlerrm);--print exception message
　　　　　　　　　 dbms_output.put_line('======Exception Position======'||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);--print the exception position
            END;
        END;
        RETURN l_sp_array ;
    END;

　　Actually, the function is not perfect enough. It would be better, if the func can split string without different characters like '@', '#' and '*', etc. As we all know, a programmer always wants to make code written by himself better. So, the next step is we should modify the above func and make it better.

　　We can regard the object character as a parameter and transfer it to this function, then, the func will be normal in more cases. Certainly, the modified func don't think about blank character ' '. That means this function can be more perfect.

CREATE OR REPLACE FUNCTION to_sp_list_modified
    (SELF IN TypeName, p_input IN VARCHAR2, p_str VarChar2) RETURN Varchar2Array  IS
    l_size INTEGER ;           --the number of substring without p_str
    l_sp_array Varchar2Array ; --l_sp_array, a array storages Varchar2 a datatype, the type is Varchar2Array
    l_i INTEGER:= 1;           --the index helps to get the next string
    i INTEGER;                 --use it to judge the condition of exitting loop
    BEGIN
        l_size  := 0;
        l_sp_array  := NULL;

        BEGIN
            l_sp_array  := Varchar2Array ();  --instance Varchar2Array object

　　　       --according to the first occurrence of p_str, if the position > 0, continue runnning
            if instr(p_input, p_str, 1, 1) > 0 THEN
                l_size := length(p_input) - length(replace(p_input, p_str, '')) + 1;  --get the number of substring without p_str
                l_sp_array.EXTEND(l_size); --there are how many substring, the capacity of l_sp_array should be increased by how many units
                l_sp_array(l_i):=substr(p_input, 1, instr(p_input, p_str, 1, l_i)-1); --get the first string into the array l_sp_array
                l_i  := l_i  + 1; --index plus 1
                i := 1;

　　　           --if l_size > 2, it means the original string has more than two p_str, so the next, we continue to get next string into the array by the loop
                if l_size > 2 THEN
                    WHILE i < l_size - 1   
                    LOOP
                        l_sp_array(l_i):=substr(p_input, instr(p_input, p_str, 1, l_i-1)+1, instr(p_input, p_str, 1, l_i)-1-instr(p_input, p_str, 1, l_i-1));
                        l_i := l_i + 1;
                        i := i + 1;
                    END LOOP;

　　　               --when exitting loop, there will be the last string at the end of the original string, the below sentence will put the last string into the array
                    l_sp_array(l_i):=substr(p_input, instr(p_input, p_str, 1, l_i-1)+1);

　　　           --otherwise, there is only one character p_str in the original string. The next, we get the second string and put it into the array directly
                else
                    l_sp_array(l_i):=substr(p_input, instr(p_input, p_str, 1, 1)+1);
                end if;

　　         --otherwise, there's no p_str in the original string. the capactiy of array should be expanded with one unit, then, put the original string into the array
            else
                l_sp_array.EXTEND;
                l_sp_array(1) := p_input;
            end if;
        EXCEPTION
            WHEN others THEN
            BEGIN
                dbms_output.put_line('======Exception Message======'||sqlerrm);--print exception message
　　　　　　　　　 dbms_output.put_line('======Excp Pos======'||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);--print the exception position
            END;
        END;
        RETURN l_sp_array ;
    END;

　　to be continued......