OracleDB中如何用SELECT按需将逗号分隔值转为多列

固定列数场景 #

如果能提前确定需要拆分出的列数（比如示例中的6列），直接用REGEXP_SUBSTR函数即可，Oracle 11g及以上版本支持该函数，写法简洁高效：

SELECT
  REGEXP_SUBSTR(PARAM_VALUE, '[^,]+', 1, 1) AS PARAM_VALUE1,
  REGEXP_SUBSTR(PARAM_VALUE, '[^,]+', 1, 2) AS PARAM_VALUE2,
  REGEXP_SUBSTR(PARAM_VALUE, '[^,]+', 1, 3) AS PARAM_VALUE3,
  REGEXP_SUBSTR(PARAM_VALUE, '[^,]+', 1, 4) AS PARAM_VALUE4,
  REGEXP_SUBSTR(PARAM_VALUE, '[^,]+', 1, 5) AS PARAM_VALUE5,
  REGEXP_SUBSTR(PARAM_VALUE, '[^,]+', 1, 6) AS PARAM_VALUE6
FROM TEST_TABLE;

参数说明 #

REGEXP_SUBSTR(源字符串, 匹配规则, 起始位置, 第N个匹配项)

• [^,]+：匹配任意不含逗号的连续字符，精准拆分逗号分隔的每个值
• 第4个参数指定取第几个拆分值，对应生成的列

如果是Oracle 10g及以下版本（无正则函数支持），可以用SUBSTR+INSTR组合实现：

SELECT
  SUBSTR(PARAM_VALUE, 1, INSTR(PARAM_VALUE, ',') - 1) AS PARAM_VALUE1,
  SUBSTR(PARAM_VALUE, INSTR(PARAM_VALUE, ',') + 1, INSTR(PARAM_VALUE, ',', 1, 2) - INSTR(PARAM_VALUE, ',', 1, 1) - 1) AS PARAM_VALUE2,
  SUBSTR(PARAM_VALUE, INSTR(PARAM_VALUE, ',', 1, 2) + 1, INSTR(PARAM_VALUE, ',', 1, 3) - INSTR(PARAM_VALUE, ',', 1, 2) - 1) AS PARAM_VALUE3,
  SUBSTR(PARAM_VALUE, INSTR(PARAM_VALUE, ',', 1, 3) + 1, INSTR(PARAM_VALUE, ',', 1, 4) - INSTR(PARAM_VALUE, ',', 1, 3) - 1) AS PARAM_VALUE4,
  SUBSTR(PARAM_VALUE, INSTR(PARAM_VALUE, ',', 1, 4) + 1, INSTR(PARAM_VALUE, ',', 1, 5) - INSTR(PARAM_VALUE, ',', 1, 4) - 1) AS PARAM_VALUE5,
  SUBSTR(PARAM_VALUE, INSTR(PARAM_VALUE, ',', 1, 5) + 1) AS PARAM_VALUE6
FROM TEST_TABLE;

• 利用INSTR定位每个逗号的位置，再用SUBSTR截取对应区间的字符串，最后一列直接取最后一个逗号后的内容。

动态列数场景 #

如果无法提前确定列数（数据中分隔值数量不固定），单纯SELECT语句无法直接实现，需要结合PL/SQL生成动态SQL：

1. 先统计数据中最多的分隔值数量：

SELECT MAX(REGEXP_COUNT(PARAM_VALUE, ',') + 1) AS MAX_COLUMNS FROM TEST_TABLE;

2. 用PL/SQL块生成并执行动态查询：

DECLARE
  v_sql VARCHAR2(4000);
  v_max_cols NUMBER;
BEGIN
  SELECT MAX(REGEXP_COUNT(PARAM_VALUE, ',') + 1) INTO v_max_cols FROM TEST_TABLE;
  
  v_sql := 'SELECT ';
  FOR i IN 1..v_max_cols LOOP
    v_sql := v_sql || 'REGEXP_SUBSTR(PARAM_VALUE, ''[^,]+'', 1, ' || i || ') AS PARAM_VALUE' || i || ',';
  END LOOP;
  v_sql := RTRIM(v_sql, ',') || ' FROM TEST_TABLE';
  
  EXECUTE IMMEDIATE v_sql;
END;
/

该块会自动根据数据中的最大列数，生成对应数量的拆分列并执行查询。

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