How to parse a string in Oracle pl/sql
When you want to parse a String in Oracle based on delimiters,
you can use the Instr function to
determine where the delimiters are. Then you can use the substr
function to get the data between the delimiters
You can combine these easily using the REGEXP_SUBSTR command.
The REGEXP_SUBSTR extends the functionality of the SUBSTR function by letting you search a
string for a regular expression pattern. It is also similar to REGEXP_INSTR, but instead of
returning the position of the substring, it returns the substring itself. This function is useful
if you need the contents of a match string but not its position in the source string.
The function returns the string as VARCHAR2 or CLOB data in the same character set as source_string.
Example:
declare
string_to_parse varchar2(2000) := 'abc,def,ghi,klmno,pqrst';
l_count number;
l_value varchar2(2000);
begin
string_to_parse := string_to_parse||',';
l_count := length(string_to_parse) - length(replace(string_to_parse,',',''));
-- In oracle 11g use regexp_count to determine l_count
for i in 1 .. l_count loop
select regexp_substr(string_to_parse,'[^,]+',1,i)
into l_value
from dual;
dbms_output.put_line(l_value);
end loop;
end;
When the delimiter is a comma and the names between the comma are valid Oracle table names, you can use dbms_utility.comma_to_table(stringToParse,numberOfTablesn,resultListOfTables);
|