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)
