基于SQL按分隔符拆分含内嵌逗号的CSV字符串
我有一个结构如下的平面文件（flat file）：
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" .....
首行是列标题，字段以逗号分隔，需求是将每行的单个字符串拆分为独立字段。原本可以用Excel的分列功能（以逗号为分隔符）处理后上传到数据库表，但
Account_Name
字段的值内部可能包含逗号，导致普通分列失效。我编写了如下SQL来处理，请问这段SQL是否正确？另外有没有更简便的实现方式？
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) <> '"' 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 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)
1. 现有SQL的问题
你的SQL存在几个明显的缺陷：
-
无效判断
：CASE语句的两个分支返回完全相同的表达式，等于没做任何判断，根本无法处理
Account_Name含内部逗号的场景。 -
硬编码依赖强
：所有字段的位置都靠嵌套
LOCATE_IN_STRING硬计算，一旦字段顺序变动、其他字段出现带引号的逗号，整个逻辑会直接失效。 -
边界处理缺失
：没有考虑行尾、空字段、引号配对错误等异常情况，比如最后一个字段为空时
SUBSTR会报错，引号不配对会导致定位完全错误。
2. 更简便的实现方式
这类带引号的CSV是标准格式，大部分数据库都有原生支持，无需手写复杂的字符串处理逻辑：
方法1：用数据库原生导入工具
从
RRN
函数判断你用的是IBM i上的DB2，直接用
CPYFRMIMPF
命令即可：
CPYFRMIMPF FROMFILE(你的源文件) TOFILE(目标表) DTAFMT(*CSV) STRDELIM('"')
这个命令会自动识别带引号的字段，跳过内部的逗号，直接完成导入，完全不需要写SQL。
方法2：转JSON后解析
DB2 for i 7.3及以上版本支持JSON函数，可以把CSV行转成JSON格式再提取字段：
SELECT JSON_VALUE(json_row, '$.machineCode') AS MACHINECODE, CAST(JSON_VALUE(json_row, '$.Key') AS DEC(12,0)) AS KEY, CAST(JSON_VALUE(json_row, '$.Ip_Name_No') AS DEC(12,0)) AS IP_NN, JSON_VALUE(json_row, '$.Share_Percent') AS PERCENTAGE, JSON_VALUE(json_row, '$.Account_Name') AS ACCOUNT_NAME, JSON_VALUE(json_row, '$.Account_No') AS ACCOUNT_NUMBER FROM ( SELECT '{"' || REPLACE(REPLACE(DATA, '"', '""'), ',', '","') || '"}' AS json_row FROM PLDWRK WHERE DATA NOT LIKE 'machineCode%' -- 排除表头行 ) AS t
通过替换字符把CSV转成合法JSON，再用
JSON_VALUE
提取字段，自动处理带内部逗号的引号字段。
方法3：正则表达式拆分
利用DB2的
REGEXP_SUBSTR
函数，用正则匹配带引号的字段：
SELECT
REGEXP_SUBSTR(DATA, '^"([^"]+)"|^([^,]+)', 1, 1, '', 1) AS MACHINECODE,
CAST(REGEXP_SUBSTR(DATA, ',([^,]+),', 1, 1, '', 1) AS DEC(12,0)) AS KEY,
CAST(REGEXP_SUBSTR(DATA, ',([^,]+),', 1, 2, '', 1) AS DEC(12,0)) AS IP_NN,
REGEXP_SUBSTR(DATA, ',"([^"]+)",', 1, 1, '', 1) AS PERCENTAGE,