SQL拆分可变长度分段字符串报错求助：处理'-'分隔的11段数据

问题场景 #

我有一个仅含单列的表，数据示例如下：

ROW-1>> 7302-2210177000-XXXX-XXXXXX-XXX-XXXXXXXXXX-XXXXXX-XXXXXX-U-XXXXXXXXX-XXXXXX
ROW-2>> 0311-1130101-XXXX-000000-XXX-XXXXXXXXXX-XXXXXX-XXXXXX-X-XXXXXXXXX-WIPXXX

需求：将这些以'-'分隔的内容拆分后加载到新表中，字符串共包含11个分段，对应新表的11列，且各分段长度可变，需保留原有长度（例如标准格式为4位的分段，若实际为2位则保留2位）。

原错误SQL及问题 #

我编写的SQL代码如下：

select 
 SUBSTR(PROFILE_ID,1,(case when length(instr(PROFILE_ID,'-')<>4) THEN (instr(PROFILE_ID,'-') else SUBSTR(PROFILE_ID,1,4) end)
 )AS [RQUIRED_COLUMN_NAME]
 from [TABLE_NAME]; 

执行时出现右括号不匹配错误，且逻辑本身存在语法问题。

错误分析 #

1. 括号不匹配：CASE表达式缺少结束括号，SUBSTR的参数括号也未正确配对。
2. 逻辑语法错误：length(instr(PROFILE_ID,'-')<>4)完全不符合SQL语法——instr返回字符位置，与4比较得到布尔值，用length处理布尔值是无效操作，完全偏离了截取分段的需求。

解决方案 #

根据不同数据库，提供以下可靠的拆分实现：

1. SQL Server（2016+） #

方式一：STRING_SPLIT + PIVOT

WITH SplitData AS (
  SELECT 
    PROFILE_ID,
    value AS segment,
    ROW_NUMBER() OVER (PARTITION BY PROFILE_ID ORDER BY (SELECT NULL)) AS seg_num
  FROM [TABLE_NAME]
  CROSS APPLY STRING_SPLIT(PROFILE_ID, '-')
)
SELECT
  MAX(CASE WHEN seg_num = 1 THEN segment END) AS col1,
  MAX(CASE WHEN seg_num = 2 THEN segment END) AS col2,
  MAX(CASE WHEN seg_num = 3 THEN segment END) AS col3,
  MAX(CASE WHEN seg_num = 4 THEN segment END) AS col4,
  MAX(CASE WHEN seg_num = 5 THEN segment END) AS col5,
  MAX(CASE WHEN seg_num = 6 THEN segment END) AS col6,
  MAX(CASE WHEN seg_num = 7 THEN segment END) AS col7,
  MAX(CASE WHEN seg_num = 8 THEN segment END) AS col8,
  MAX(CASE WHEN seg_num = 9 THEN segment END) AS col9,
  MAX(CASE WHEN seg_num = 10 THEN segment END) AS col10,
  MAX(CASE WHEN seg_num = 11 THEN segment END) AS col11
FROM SplitData
GROUP BY PROFILE_ID;

方式二：CHARINDEX逐段截取

SELECT
  -- 第1段：开头到第一个'-'
  SUBSTRING(PROFILE_ID, 1, CHARINDEX('-', PROFILE_ID) - 1) AS col1,
  -- 第2段：第一个'-'后到第二个'-'
  SUBSTRING(PROFILE_ID, CHARINDEX('-', PROFILE_ID) + 1, 
            CHARINDEX('-', PROFILE_ID, CHARINDEX('-', PROFILE_ID) + 1) - CHARINDEX('-', PROFILE_ID) - 1) AS col2,
  -- 第3段：第二个'-'后到第三个'-'
  SUBSTRING(PROFILE_ID, CHARINDEX('-', PROFILE_ID, CHARINDEX('-', PROFILE_ID) + 1) + 1,
            CHARINDEX('-', PROFILE_ID, CHARINDEX('-', PROFILE_ID, CHARINDEX('-', PROFILE_ID) + 1) + 1) - CHARINDEX('-', PROFILE_ID, CHARINDEX('-', PROFILE_ID) + 1) - 1) AS col3,
  -- 依次类推编写col4到col10的截取逻辑
  -- 第11段：最后一个'-'之后的内容
  RIGHT(PROFILE_ID, LEN(PROFILE_ID) - CHARINDEX('-', REVERSE(PROFILE_ID)) + 1) AS col11
FROM [TABLE_NAME];

2. MySQL #

利用SUBSTRING_INDEX直接按分隔符取第N段：

SELECT
  SUBSTRING_INDEX(PROFILE_ID, '-', 1) AS col1,
  SUBSTRING_INDEX(SUBSTRING_INDEX(PROFILE_ID, '-', 2), '-', -1) AS col2,
  SUBSTRING_INDEX(SUBSTRING_INDEX(PROFILE_ID, '-', 3), '-', -1) AS col3,
  SUBSTRING_INDEX(SUBSTRING_INDEX(PROFILE_ID, '-', 4), '-', -1) AS col4,
  SUBSTRING_INDEX(SUBSTRING_INDEX(PROFILE_ID, '-', 5), '-', -1) AS col5,
  SUBSTRING_INDEX(SUBSTRING_INDEX(PROFILE_ID, '-', 6), '-', -1) AS col6,
  SUBSTRING_INDEX(SUBSTRING_INDEX(PROFILE_ID, '-', 7), '-', -1) AS col7,
  SUBSTRING_INDEX(SUBSTRING_INDEX(PROFILE_ID, '-', 8), '-', -1) AS col8,
  SUBSTRING_INDEX(SUBSTRING_INDEX(PROFILE_ID, '-', 9), '-', -1) AS col9,
  SUBSTRING_INDEX(SUBSTRING_INDEX(PROFILE_ID, '-', 10), '-', -1) AS col10,
  SUBSTRING_INDEX(PROFILE_ID, '-', -1) AS col11
FROM [TABLE_NAME];

3. Oracle #

使用REGEXP_SUBSTR按正则匹配分段：

SELECT
  REGEXP_SUBSTR(PROFILE_ID, '[^-]+', 1, 1) AS col1,
  REGEXP_SUBSTR(PROFILE_ID, '[^-]+', 1, 2) AS col2,
  REGEXP_SUBSTR(PROFILE_ID, '[^-]+', 1, 3) AS col3,
  REGEXP_SUBSTR(PROFILE_ID, '[^-]+', 1, 4) AS col4,
  REGEXP_SUBSTR(PROFILE_ID, '[^-]+', 1, 5) AS col5,
  REGEXP_SUBSTR(PROFILE_ID, '[^-]+', 1, 6) AS col6,
  REGEXP_SUBSTR(PROFILE_ID, '[^-]+', 1, 7) AS col7,
  REGEXP_SUBSTR(PROFILE_ID, '[^-]+', 1, 8) AS col8,
  REGEXP_SUBSTR(PROFILE_ID, '[^-]+', 1, 9) AS col9,
  REGEXP_SUBSTR(PROFILE_ID, '[^-]+', 1, 10) AS col10,
  REGEXP_SUBSTR(PROFILE_ID, '[^-]+', 1, 11) AS col11
FROM [TABLE_NAME];

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