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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in September 2002