如何使用SQL拆分食品订单字符串中的商品编码与数量

没问题！我帮你整理几种主流数据库下的SQL拆分方案，完美适配你这种"102*4;109*3;101*2"格式的订单字符串，不用再依赖前端提前处理啦：

先按分号;把每个独立的商品条目拆分成单独行，再对每个条目按星号*拆分商品编码和数量，最后提取出对应的字段。

1. MySQL 8.0+ 方案 #

利用递归CTE（公共表表达式）来循环拆分每个商品条目，再用SUBSTRING_INDEX提取编码和数量：

WITH RECURSIVE order_items AS (
    SELECT 
        SUBSTRING_INDEX(order_str, ';', 1) AS item,
        SUBSTRING(order_str, LENGTH(SUBSTRING_INDEX(order_str, ';', 1)) + 2) AS remaining_str
    FROM your_table
    WHERE order_str != ''
    UNION ALL
    SELECT 
        SUBSTRING_INDEX(remaining_str, ';', 1) AS item,
        SUBSTRING(remaining_str, LENGTH(SUBSTRING_INDEX(remaining_str, ';', 1)) + 2) AS remaining_str
    FROM order_items
    WHERE remaining_str != ''
)
SELECT
    SUBSTRING_INDEX(item, '*', 1) AS product_code,
    CAST(SUBSTRING_INDEX(item, '*', -1) AS UNSIGNED) AS quantity
FROM order_items;

注：如果是MySQL 5.x（无递归CTE），可以创建一个数字辅助表配合SUBSTRING_INDEX来实现拆分，不过更推荐升级到8.0版本用上面的简洁方案。

2. SQL Server 2016+ 方案 #

用STRING_SPLIT快速拆分商品条目，再通过CHARINDEX和SUBSTRING拆分编码和数量：

SELECT
    SUBSTRING(item.value, 1, CHARINDEX('*', item.value) - 1) AS product_code,
    CAST(SUBSTRING(item.value, CHARINDEX('*', item.value) + 1, LEN(item.value)) AS INT) AS quantity
FROM your_table
CROSS APPLY STRING_SPLIT(order_str, ';') AS item
WHERE item.value != '';

3. PostgreSQL 方案 #

用STRING_TO_ARRAY+UNNEST拆分商品条目，再用SPLIT_PART直接提取编码和数量，语法非常简洁：

SELECT
    SPLIT_PART(item, '*', 1) AS product_code,
    CAST(SPLIT_PART(item, '*', 2) AS INT) AS quantity
FROM your_table,
     UNNEST(STRING_TO_ARRAY(order_str, ';')) AS item
WHERE item != '';

• 记得过滤空条目：如果订单字符串末尾有多余的;，拆分后会产生空值，用WHERE item != ''可以避免。
• 数量转类型：上面的示例都把数量转成了整数类型，方便后续的统计、关联等操作。
• 低版本兼容：如果你的数据库版本太老（比如MySQL 5.x、SQL Server 2014及以下），可以考虑自定义字符串拆分函数来实现类似效果。

内容的提问来源于stack exchange，提问作者Equan Ur Rehman