如何编写SQL查询将竖线分隔数据转为逐行单列格式？

你当前用replace的方式会把所有分隔符替换后合并成一串，这不是拆分的正确思路，需要用字符串拆分+行转列的操作，以下是不同主流数据库的实现方案：

MySQL 8.0+ / MariaDB 10.3+ #

利用递归CTE和字符串定位函数拆分：

WITH RECURSIVE split_data AS (
    SELECT
        TRIM(BOTH '|' FROM a_list) AS remaining_str,
        1 AS pos
    FROM your_table
    WHERE a_list IS NOT NULL AND a_list != ''
    UNION ALL
    SELECT
        SUBSTRING(remaining_str, LOCATE('|', remaining_str) + 1),
        pos + 1
    FROM split_data
    WHERE LOCATE('|', remaining_str) > 0
)
SELECT TRIM(remaining_str) AS value
FROM split_data
WHERE TRIM(remaining_str) != '';

先去除字符串首尾的竖线，再递归拆分每个竖线分隔的片段，最后过滤空值。

SQL Server 2016+ #

使用内置的STRING_SPLIT函数，操作更简洁：

SELECT TRIM(value) AS value
FROM your_table
CROSS APPLY STRING_SPLIT(TRIM(BOTH '|' FROM a_list), '|')
WHERE TRIM(value) != '';

先清理首尾竖线，再按竖线拆分字符串，通过CROSS APPLY把拆分结果转为行，最后过滤空值。

PostgreSQL #

结合STRING_TO_ARRAY和UNNEST函数实现拆分：

SELECT elem AS value
FROM your_table,
     UNNEST(STRING_TO_ARRAY(TRIM(BOTH '|' FROM a_list), '|')) AS elem
WHERE elem != '';

将字符串转为数组后，用UNNEST把数组元素转为单行记录，同时过滤空元素。

旧版MySQL（低于8.0） #

如果不支持递归CTE，可借助数字表实现拆分：

-- 先创建临时数字表，数字数量需大于最大拆分片段数
CREATE TEMPORARY TABLE nums (n INT);
INSERT INTO nums VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

SELECT
    TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(BOTH '|' FROM a_list), '|', n), '|', -1)) AS value
FROM your_table
JOIN nums ON n <= LENGTH(TRIM(BOTH '|' FROM a_list)) - LENGTH(REPLACE(TRIM(BOTH '|' FROM a_list), '|', '')) + 1
WHERE TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(BOTH '|' FROM a_list), '|', n), '|', -1)) != '';

通过计算字符串中竖线的数量确定拆分次数，关联数字表逐个提取每个分隔片段。

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