如何在PostgreSQL中提取方括号[]之间的字符并写入表中

核心思路 #

使用正则表达式匹配\[([^\]]+)\]，其中：

• \[ 匹配左方括号（转义是因为[在正则中是特殊字符）
• ([^\]]+) 捕获所有非右方括号的字符（这就是我们需要提取的内容）
• \] 匹配右方括号

根据不同数据库的正则函数特性，以下是具体实现方案：

PostgreSQL 实现 #

利用regexp_matches全局匹配所有符合项，再用unnest将结果拆分为多行，方便写入表：

-- 从单个字符串提取
SELECT unnest(regexp_matches('gegerferf[hello] frfer [world] frfre rfrf', '\[([^\]]+)\]', 'g')) AS extracted_text;

-- 从表中提取并写入目标表
INSERT INTO target_table (extracted_column)
SELECT unnest(regexp_matches(string_column, '\[([^\]]+)\]', 'g'))
FROM mytable;

• 'g' 参数表示全局匹配所有符合的片段
• unnest 将正则返回的数组转为单独的行记录

MySQL 8.0+ 实现 #

MySQL原生正则函数仅返回第一个匹配项，需结合递归CTE遍历所有匹配：

-- 从单个字符串提取
WITH RECURSIVE cte AS (
    SELECT 
        'gegerferf[hello] frfer [world] frfre rfrf' AS str,
        REGEXP_SUBSTR('gegerferf[hello] frfer [world] frfre rfrf', '\\[([^\\]]+)\\]', 1, 1) AS match_str,
        1 AS idx
    UNION ALL
    SELECT 
        str,
        REGEXP_SUBSTR(str, '\\[([^\\]]+)\\]', 1, idx + 1),
        idx + 1
    FROM cte
    WHERE match_str IS NOT NULL
)
SELECT SUBSTRING(match_str, 2, LENGTH(match_str)-2) AS extracted_text
FROM cte
WHERE match_str IS NOT NULL;

-- 从表中提取并写入目标表
WITH RECURSIVE cte AS (
    SELECT 
        string_column AS str,
        REGEXP_SUBSTR(string_column, '\\[([^\\]]+)\\]', 1, 1) AS match_str,
        1 AS idx,
        id -- 保留原表主键，避免数据混乱
    FROM mytable
    UNION ALL
    SELECT 
        str,
        REGEXP_SUBSTR(str, '\\[([^\\]]+)\\]', 1, idx + 1),
        idx + 1,
        id
    FROM cte
    WHERE match_str IS NOT NULL
)
INSERT INTO target_table (extracted_column, source_id)
SELECT SUBSTRING(match_str, 2, LENGTH(match_str)-2), id
FROM cte
WHERE match_str IS NOT NULL;

• 递归CTE逐次提取下一个匹配项
• SUBSTRING 用于去除匹配结果前后的[]

Oracle 实现 #

通过CONNECT BY生成层级，遍历所有匹配项：

-- 从单个字符串提取
SELECT REGEXP_REPLACE(REGEXP_SUBSTR('gegerferf[hello] frfer [world] frfre rfrf', '\[([^\]]+)\]', 1, LEVEL, 'i'), '^\[|\]$', '') AS extracted_text
FROM dual
CONNECT BY REGEXP_SUBSTR('gegerferf[hello] frfer [world] frfre rfrf', '\[([^\]]+)\]', 1, LEVEL, 'i') IS NOT NULL;

-- 从表中提取并写入目标表
INSERT INTO target_table (extracted_column)
SELECT REGEXP_REPLACE(REGEXP_SUBSTR(string_column, '\[([^\]]+)\]', 1, LEVEL, 'i'), '^\[|\]$', '')
FROM mytable
CONNECT BY REGEXP_SUBSTR(string_column, '\[([^\]]+)\]', 1, LEVEL, 'i') IS NOT NULL
AND PRIOR id = id -- 关联原表主键，防止笛卡尔积
AND PRIOR SYS_GUID() IS NOT NULL; -- 避免递归循环

• LEVEL 表示当前匹配的层级序号
• REGEXP_REPLACE 用于去除结果中的[]

关于你原语句的问题 #

你使用的regexp_replace如果仅用占位符'...'无法正确匹配，即便写对正则，也只能得到拼接后的字符串（如helloworld），无法生成单独的行记录，因此直接提取匹配项的方案更贴合需求。

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