Redshift中如何将字符串化数组拆分为多行以用于WHERE子句

以下针对主流数据库提供对应解决方案，你可以根据自己使用的数据库选择合适的写法：

MySQL 实现 #

通过递归CTE（公共表达式）逐步拆分字符串：

SELECT ...
FROM religions
WHERE name IN (
    WITH RECURSIVE split_religions AS (
        -- 第一步：去除字符串首尾的[]，得到待拆分的内容
        SELECT 
            TRIM(BOTH '[]' FROM t.religions) AS remaining_str
        FROM your_table t
        -- 这里可添加WHERE条件指定要拆分的目标行，比如 WHERE t.id = 1
        UNION ALL
        -- 递归截取剩余字符串
        SELECT
            SUBSTRING(remaining_str, LOCATE('", "', remaining_str) + 4)
        FROM split_religions
        WHERE LOCATE('", "', remaining_str) > 0
    )
    -- 提取单个宗教名称并去除前后引号
    SELECT TRIM(BOTH '"' FROM 
        CASE WHEN LOCATE('", "', remaining_str) > 0 
             THEN SUBSTRING(remaining_str, 1, LOCATE('", "', remaining_str) - 1)
             ELSE remaining_str
        END) AS religion_name
    FROM split_religions
);

PostgreSQL 实现 #

利用PostgreSQL原生的数组转换函数，写法更简洁：

SELECT ...
FROM religions
WHERE name IN (
    -- 先去除首尾[]，再按'", "'拆分字符串并展开为多行
    SELECT unnest(string_to_array(TRIM(BOTH '[]' FROM t.religions), '", "'))
    FROM your_table t
    -- 可选：添加WHERE条件指定目标行
);

也可以用正则拆分函数替代：

SELECT ...
FROM religions
WHERE name IN (
    SELECT regexp_split_to_table(TRIM(BOTH '[]' FROM t.religions), '", "')
    FROM your_table t
);

SQL Server 实现 #

2016及以上版本（支持STRING_SPLIT） #

SELECT ...
FROM religions
WHERE name IN (
    -- 去除首尾[]后拆分，再去掉每个元素的引号
    SELECT TRIM('"' FROM s.value)
    FROM your_table t
    CROSS APPLY STRING_SPLIT(TRIM(BOTH '[]' FROM t.religions), '", "') s
    -- 可选：添加WHERE条件指定目标行
);

2016以前版本（使用递归CTE） #

SELECT ...
FROM religions
WHERE name IN (
    WITH split_religions AS (
        SELECT 
            TRIM(BOTH '[]' FROM t.religions) AS remaining_str
        FROM your_table t
        -- 指定目标行的WHERE条件
        UNION ALL
        SELECT
            SUBSTRING(remaining_str, CHARINDEX('", "', remaining_str) + 4, LEN(remaining_str))
        FROM split_religions
        WHERE CHARINDEX('", "', remaining_str) > 0
    )
    SELECT TRIM('"' FROM 
        CASE WHEN CHARINDEX('", "', remaining_str) > 0
             THEN SUBSTRING(remaining_str, 1, CHARINDEX('", "', remaining_str) - 1)
             ELSE remaining_str
        END) AS religion_name
    FROM split_religions
);

注意：如果你的表中存在多行数据，务必在子查询中添加WHERE条件定位到需要拆分的目标行，避免将所有行的宗教字符串都拆分后混入结果。

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