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) 

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