Problem solve Get help with specific problems with your technologies, process and projects.

Parsing a delimited string

How can I parse a delimited string in Oracle?

To parse a delimited string, you will want to make use of the INSTR function to search for the components of your string. The following code example (in PL/SQL) assumes that the first character of the string is the delimiter. You can see how I search through the string and output the components of that string, minus the delimiter character.

 DECLARE myString VARCHAR2(255):='|this|is|a|string|'; strLength NUMBER; counter NUMBER; delimiter CHAR; spot NUMBER; BEGIN -- Get length of string strLength:=LENGTH(myString); -- Get delimiter character delimiter:=SUBSTR(myString,1,1); DBMS_OUTPUT.PUT_LINE('The delimiter is: '||delimiter); -- start with second character of the string counter:=2; LOOP -- Find the next delimiter location spot:=INSTR(myString,delimiter,counter); -- Print out the component of the string DBMS_OUTPUT.PUT_LINE(SUBSTR(myString,counter,spot-counter)); -- Move to next location after the delimiter counter:=spot+1; -- Continue until entire string has been processed EXIT WHEN counter >= strLength; END LOOP; END; /


For More Information

This was last published in September 2002

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.