Delimited String Field Extract

How to extract fields from a comma-separated values record, or string.

View PL/SQL Code in a new tab

Data is commonly exchanged using comma-separated values (CSV) files. Fields may be enclosed in quotation marks so that they can contain embedded delimiter characters. A problem can occur when a quoted field contains embedded quotes.

For example, consider the following CSV record which contains the fields: Order Number, Order Date, Product Description, Quantity, Price.
    9001089,21/08/2022,"Picture Frames, Gold, 12" x 16"",100,14.99

  • We need to extract the third field, containing the product description: Picture Frames, Gold, 12" x 16"
  • The quotation marks indicating the size of the frame in inches need to be included in the product description field value, and not treated as terminating the string.
  • The product description string contains commas, which are not to be treated as delimiters between fields.

In this post we look at a handy string function that solves this problem, as well as allowing you to extract fields from CSV records that contain various types of delimiter: comma, semi-colon, tab.

Here's the Package Specification for the util_string package function get_field

get_field function

You can take a look at the PL/SQL code here.

Let's try the function out with the above example CSV record.

XEPDB1>APPSDEMO>sqlplus demo_connect/[password]@//localhost/xepdb1

SQL> SELECT util_string.get_field('&str', &pos, ',') FROM dual;
Enter value for str: 9001089,21/08/2022,"Picture Frames, Gold, 12" x 16"",100,14.99
Enter value for pos: 3

UTIL_STRING.GET_FIELD ('9001089,21/08/2022,"Picture Frames, Gold, 12" x 16"",100,14.99',3,',')
Picture Frames, Gold, 12" x 16"

The result returned is the third field, the product description, containing the embedded commas and quotes.

    Picture Frames, Gold, 12" x 16"

The get_field function will be extremely useful for loading CSV file data into our database, which is something we will cover in a future blog post.

In the next post, we will take a look at a simple PL/SQL application that imports order data from CSV files into the Oracle database.

Previous Blog Next Blog