Database   Sql   Functions   Packages   Performance   Books   Oracle   Other   About   XML   ORA-messages
  Parsing a delimited characterstring in Oracle pl/sql


Pl/sql tools
create table
Database scheduler
create tablespace
dba metrics
Table with sequenced numbers
Autonomous transaction
ORA-12537 error
Parse a String in pl/sql
Export with oracle datapump


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.
  string_to_parse varchar2(2000) := 'abc,def,ghi,klmno,pqrst';
  l_count number;
  l_value   varchar2(2000);
  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;
  end loop;
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);