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

Question

我正在尝试拆分字段（在某个分隔符“；”）并将结果插入表中。最大值为5个子字符串，由“；”分隔。最多只有5个水果。如果只有水果列，如何拆分字符串以获得单独的水果。如果果数少于5，其余列将返回NA。

我首先创建了新列并将其全部设置为null。我尝试了以下代码，但它不起作用，如果水果比列少，其余列将只取最后一个水果的值而不是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;

是否还有其他信息可用于拆分字符串？

Answer 1

在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列，而不是当前分号分隔的字符串格式。