Bond & Pollard | Oracle EBS Consultants

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 - Return the Nth field within a string, where the fields are separated by a specified delimiter 
  **
  ** Return the Nth field within a string, where the fields are separated by a specified delimiter 
  ** e.g. semicolon, comma or tab character.
  ** Ignore the delimiters within pairs of double quotes. 
  ** Strip double quotes from the start and end of the string.
  ** Example:
  **   select get_field('field1;"field;;;2";"field"""3";field4',3,';') from dual;
  ** Result:
  **   field3"""3
  **
  ** IN
  **   p_string        - String containing delimiter separated values 
  **   p_position      - Indicates which field to return, Nth in string
  **   p_delimiter     - Delimiter character used to separate fields
  ** RETURN
  **   VARCHAR2  Nth field of the string
  ** EXCEPTIONS
  **         - 
  */
  FUNCTION get_field(
    p_string    IN VARCHAR2, 
    p_position  IN NUMBER, 
    p_delimiter IN VARCHAR2 DEFAULT ','
  ) RETURN VARCHAR2;

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.

Delimited String Field Extract