按分号分隔符拆分字符串为最多5列并补全空值的SQL实现问题

原写法问题说明 #

原有SQL的判断逻辑依赖多个字段长度累加计算分隔符位置，容易受字段内空格、特殊字符干扰导致判断失效，最终出现剩余列重复取最后一个值的问题。

实现思路 #

1. 先计算fruits字段内;的总个数：通过LENGTH(fruits) - LENGTH(REPLACE(fruits, ';', ''))即可得到，拆分后的总元素数为「分号个数 + 1」
2. 取第k个元素时，先判断k是否大于总元素数，是则直接返回NULL，否则用两层SUBSTRING_INDEX取出对应位置的子串，再用TRIM()去除子串前后多余的空格，匹配示例效果。

完整查询SQL #

SELECT  
  fruits,
  TRIM(SUBSTRING_INDEX(fruits, ';', 1)) AS fruit1,
  CASE 
    WHEN 2 <= (LENGTH(fruits) - LENGTH(REPLACE(fruits, ';', '')) + 1) 
    THEN TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(fruits, ';', 2), ';', -1))
    ELSE NULL 
  END AS fruit2,
  CASE 
    WHEN 3 <= (LENGTH(fruits) - LENGTH(REPLACE(fruits, ';', '')) + 1) 
    THEN TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(fruits, ';', 3), ';', -1))
    ELSE NULL 
  END AS fruit3,
  CASE 
    WHEN 4 <= (LENGTH(fruits) - LENGTH(REPLACE(fruits, ';', '')) + 1) 
    THEN TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(fruits, ';', 4), ';', -1))
    ELSE NULL 
  END AS fruit4,
  CASE 
    WHEN 5 <= (LENGTH(fruits) - LENGTH(REPLACE(fruits, ';', '')) + 1) 
    THEN TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(fruits, ';', 5), ';', -1))
    ELSE NULL 
  END AS fruit5
FROM TABLENAME;

写入已有列的更新SQL #

你已经提前新建了fruit1到fruit5字段，可直接用以下语句更新写入：

UPDATE TABLENAME
SET 
  fruit1 = TRIM(SUBSTRING_INDEX(fruits, ';', 1)),
  fruit2 = CASE 
    WHEN 2 <= (LENGTH(fruits) - LENGTH(REPLACE(fruits, ';', '')) + 1) 
    THEN TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(fruits, ';', 2), ';', -1))
    ELSE NULL 
  END,
  fruit3 = CASE 
    WHEN 3 <= (LENGTH(fruits) - LENGTH(REPLACE(fruits, ';', '')) + 1) 
    THEN TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(fruits, ';', 3), ';', -1))
    ELSE NULL 
  END,
  fruit4 = CASE 
    WHEN 4 <= (LENGTH(fruits) - LENGTH(REPLACE(fruits, ';', '')) + 1) 
    THEN TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(fruits, ';', 4), ';', -1))
    ELSE NULL 
  END,
  fruit5 = CASE 
    WHEN 5 <= (LENGTH(fruits) - LENGTH(REPLACE(fruits, ';', '')) + 1) 
    THEN TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(fruits, ';', 5), ';', -1))
    ELSE NULL 
  END;

该方案每个列的计算逻辑完全独立，不会依赖前面字段的计算结果，从根源避免了长度计算错误导致的取值重复问题，同时自动处理分号前后的空格，和你给出的示例效果完全匹配。

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