Q

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 first published in September 2002
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close