DB2 SQL statement to split a string based on delimiter

Question

I have a "flat file" with structure as below:

machineCode,Key,Ip_Name_No,Share_Percent,Account_Name,Account_No 
"ygh048GT",4767,534293748,"100.00","cderfgdsc Publishing International Ltd","160102040" 
"xcd064HW",6380,65424090,"100.00","dascdfrgh snm skion","00090382478" 
"000065AN",6402,65424090,"100.00","xcdertn,john sean","00090382478" 
.....

The first row are the column headings. As can be seen, the fields are separated by a comma. The requirement is to split the single string into separate fields.

This could be done by excel and then uploaded to a DB table using the data to columns option with comma as delimiter but the Account_Name field can contain commas within the values itself.

So, I came up with the below SQL. Question is, does this look correct ? Also, there must be some easier way to do this, any suggestions ?

Please note, for values with quotes, I am trying to extract the value with the quote. For example, "ygh048GT" and not ygh048GT.

Also, for the ACCOUNT_NAME field, I have tried to determine that the comma is not a part of the value, but an actual delimiter by checking if the next character is a quote(").

WITH POS AS (
select 
LOCATE_IN_STRING ( DATA , ',' , 2 ) - 1 AS TUNECODE_END ,
LOCATE_IN_STRING (DATA, ',' , LOCATE_IN_STRING ( DATA , ',' , 2 ) + 1) - 1 AS WORKKEY_END,
LOCATE_IN_STRING( DATA , ',' , (LOCATE_IN_STRING (DATA, ',' , LOCATE_IN_STRING ( DATA , ',' , 2 ) + 1) + 1) ) - 1 AS IPNN_END,
LOCATE_IN_STRING( DATA , ',' , (LOCATE_IN_STRING( DATA , ',' , (LOCATE_IN_STRING (DATA, ',' , LOCATE_IN_STRING ( DATA , ',' , 2 ) + 1) + 1) ) + 1) ) - 1 AS PERC_END,
CASE WHEN
 SUBSTR ( DATA , 
(
LOCATE_IN_STRING ( DATA , ',' , 
(LOCATE_IN_STRING( DATA , ',' , (LOCATE_IN_STRING( DATA , ',' , (LOCATE_IN_STRING (DATA, ',' , LOCATE_IN_STRING ( DATA , ',' , 2 ) + 1) + 1) ) + 1) ) + 1 ) ) + 1),
1) <> '"' 
THEN
LOCATE_IN_STRING ( DATA , ',' , 
(LOCATE_IN_STRING ( DATA , ',' , 
(LOCATE_IN_STRING( DATA , ',' , (LOCATE_IN_STRING( DATA , ',' , (LOCATE_IN_STRING (DATA, ',' , LOCATE_IN_STRING ( DATA , ',' , 2 ) + 1) + 1) ) + 1) ) + 1 ) ) + 1)) - 1 
ELSE
LOCATE_IN_STRING ( DATA , ',' , 
(LOCATE_IN_STRING( DATA , ',' , (LOCATE_IN_STRING( DATA , ',' , (LOCATE_IN_STRING (DATA, ',' , LOCATE_IN_STRING ( DATA , ',' , 2 ) + 1) + 1) ) + 1) ) + 1 ) ) - 1
END AS ACNAME_END,
RRN(P) ROWN
FROM PLDWRK P 
) SELECT 
CAST ( SUBSTR( DATA , 1, TUNECODE_END ) AS CHAR(25))AS MACHINECODE ,
CAST ( SUBSTR( DATA , TUNECODE_END + 2 , WORKKEY_END - (TUNECODE_END + 1) ) AS DEC(12,0)) AS KEY,
CAST(SUBSTR( DATA , WORKKEY_END + 2, IPNN_END - (WORKKEY_END + 1) ) AS DEC(12, 0 )) AS IP_NN,
CAST (SUBSTR( DATA, IPNN_END + 2, PERC_END - (IPNN_END + 1)) AS CHAR(8))AS PERCENTAGE,
CAST (SUBSTR( DATA, PERC_END + 2, ACNAME_END - (PERC_END + 1)) AS CHAR(100)) AS ACCOUNT_NAME,
CAST (SUBSTR( DATA, ACNAME_END + 2 ) AS CHAR(30)) as ACCOUNT_NUMBER
 FROM PLDWRK P JOIN POS ON ROWN = RRN(P) 

• Kenny Ostrom: Am I to understand that you're creating a database table with one field, which is just the full line of text? And then using SQL to break it down? That seems like a really complicated way to do what almost every database can already do -- import a CSV file. If I'm understanding the question correctly ...
• Kenny Ostrom: If it's already in the db as a text/blob entity then my apologies. In that case, I'd probably recommend an external script in any language that already has a csv library. Why reinvent the wheel?

Answer 1

Depending on which version of DB2 are you using there is the SPLIT table function:

Example:

 SELECT ordinal_position, CAST(ELEMENT AS varchar(100)) Element 
FROM TABLE(SYSTOOLS.SPLIT('"ygh048GT",4767,534293748,"100.00","cderfgdsc Publishing International Ltd","160102040" ', ','))

You can Join your table with this and replace fixed test with column name.

More info here: https://www.ibm.com/docs/en/i/7.4?topic=services-split-table-function