基于SQL按分隔符拆分含内嵌逗号的CSV字符串

云上老周

2026-8-1

问题描述

我有一个结构如下的平面文件（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) <> '"' 
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)

解答

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,
    REGEXP_SUBSTR(DATA, ',"([^"]+)",', 1, 2, '', 1) AS ACCOUNT_NAME,
    REGEXP_SUBSTR(DATA, ',"([^"]+)"$|,([^,]+)$', 1, 1, '', 1) AS ACCOUNT_NUMBER
FROM PLDWRK
WHERE DATA NOT LIKE 'machineCode%'

正则会优先匹配带引号的字段内容，自动跳过内部的逗号，拆分逻辑更简洁可靠。

内容的提问来源于stack exchange，提问作者Theju112

火山引擎最新活动