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
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.
