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......
