Ask the Expert

Parsing a delimited string

How can I parse a delimited string in Oracle?

    Requires Free Membership to View

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 first published in September 2002

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: