如何在Exasol中将字符串拆分为单列多行？

问题描述 #

现有analytics.fruits表，其中fruits列存在一行数据为apple, banana, lemon, kiwi，需要将该列的逗号分隔值拆分为每行一个水果名称的形式。

解决方案 #

Exasol提供了多种方式实现这个需求，以下是两种常用方法：

方法1：使用STR_SPLIT_TO_TABLE内置函数

这是最直接的方法，Exasol的STR_SPLIT_TO_TABLE函数专门用于将字符串按指定分隔符拆分成多行结果：

SELECT trim(split_val) AS fruits
FROM analytics.fruits,
     STR_SPLIT_TO_TABLE(fruits, ',')

• STR_SPLIT_TO_TABLE(fruits, ',')会将fruits列的字符串按逗号拆分成多行的split_val字段
• trim()用于去除拆分后每个值前后的空格（因为原字符串是, 分隔，拆分后的值会带有前导空格）

方法2：正则表达式+数字序列表

如果需要更灵活的拆分规则，可以使用正则表达式结合生成的数字序列来提取每个元素：

WITH nums AS (
    SELECT LEVEL AS n
    FROM dual
    CONNECT BY LEVEL <= 10 -- 此处数字只需大于等于拆分后的最大元素数量即可
)
SELECT trim(REGEXP_SUBSTR(f.fruits, '[^,]+', 1, n.n)) AS fruits
FROM analytics.fruits f
JOIN nums n ON REGEXP_SUBSTR(f.fruits, '[^,]+', 1, n.n) IS NOT NULL

• nums CTE生成连续的数字序列，用于定位要提取的第n个元素
• REGEXP_SUBSTR(f.fruits, '[^,]+', 1, n.n)提取第n个以逗号分隔的子字符串
• JOIN条件过滤掉超出元素数量的NULL结果，确保只返回有效拆分值

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