时间戳

首页 > 解决方案 > 仅基于特定分隔符拆分字符串

mysql - 仅基于特定分隔符拆分字符串

问题描述

我正在尝试拆分一个字段（在某个分隔符';'处）并将结果插入表中。最多为 5 个由 ';' 分隔的子字符串 . 最多只有5个水果。仅给定水果列，如何拆分字符串以获取单独的水果。如果水果少于 5 个，剩余的列将返回 NA。

水果 水果1 水果2 水果3 水果4 水果5
苹果; 橙; 香蕉 苹果 橙 香蕉 -无效的- -无效的-
苹果; 橙; 菠萝; 火龙果; 香蕉 苹果 橙 菠萝 火龙果 香蕉
梨/葡萄；橙; 香蕉; 草莓 梨/葡萄 橙 香蕉 草莓 -无效的-
苹果; 蓝莓; 猕猴桃/柠檬 苹果 蓝莓 猕猴桃/柠檬 -无效的- -无效的-

我第一次创建了新列并将其全部设置为空。我已经尝试了以下代码，但它不起作用，如果水果比列少，剩余的列将只取最后一个水果的值而不是 null。

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

是否有更多信息可以拆分字符串？

标签： mysqlsqlstringsplitmysql-workbench

解决方案

在 MySQL 5.7 和 8.0 中，现在支持 JSON 函数。你可以做一些字符串操作来改变它：

apple; orange; banana

进入这个：

["apple", "orange", "banana"]

然后使用 JSON 函数按位置提取特定的数组元素。

mysql> set @s = 'apple; orange; banana';

mysql> select cast(concat('["', replace(@s, '; ', '","'), '"]') as json) as array;
+-------------------------------+
| array                         |
+-------------------------------+
| ["apple", "orange", "banana"] |
+-------------------------------+


mysql> select json_unquote(json_extract(
    cast(concat('["', replace(@s, '; ', '","'), '"]') as json),
    '$[1]')) as element;
+---------+
| element |
+---------+
| orange  |
+---------+

然后你可以提取'$[2]'或'$[3]'或任何其他元素。您可以使用->>提取和取消引用的快捷方式。

SELECT  
  fruits,
  fruits->>'$[0]' AS `fruit1`,
  fruits->>'$[1]' AS `fruit2`,
  fruits->>'$[2]' AS `fruit3`,
  fruits->>'$[3]' AS `fruit4`
FROM (
  SELECT CAST(CONCAT('["', REPLACE(fruits, '; ', '","'), '"]')) AS fruits
  FROM TABLENAME
) AS f;

您可能会考虑将列表存储为 JSON 列，而不是当前以分号分隔的字符串格式。

推荐阅读