Oracle SQL：拆分含IC前缀文本为行并去重，保留前缀

针对你遇到的字符串清理需求，这里提供不同数据库下的实现方案，核心逻辑是识别双IC前缀的拼接串并拆分，同时保留格式、完成去重：

Oracle 实现 #

假设你的表名为your_table，目标列是account_number，可以用递归查询结合正则表达式拆分：

WITH split_data AS (
    SELECT 
        CASE 
            -- 匹配"IC+数字+IC+数字"的拼接格式
            WHEN REGEXP_LIKE(account_number, '^IC\d+IC\d+$') THEN
                REGEXP_SUBSTR(account_number, 'IC\d+', 1, LEVEL)
            ELSE
                account_number
        END AS cleaned_value
    FROM your_table
    -- 仅对双IC拼接串拆分为2行，其余保持1行
    CONNECT BY LEVEL <= CASE WHEN REGEXP_LIKE(account_number, '^IC\d+IC\d+$') THEN 2 ELSE 1 END
    AND PRIOR account_number = account_number
    AND PRIOR SYS_GUID() IS NOT NULL -- 防止递归循环
)
SELECT DISTINCT cleaned_value
FROM split_data;

逻辑说明 #

1. 用REGEXP_LIKE判断是否为双IC前缀的拼接串；
2. 符合条件的串用REGEXP_SUBSTR结合LEVEL拆分为两行，每行保留完整的IC+数字格式；
3. 非双IC格式的数值直接返回原内容；
4. 最后通过DISTINCT完成去重，包括拆分后重复的内容（比如IC123456IC123456拆分后仅保留一行）。

PostgreSQL 实现 #

如果用PostgreSQL，可以借助正则全局匹配和数组展开：

SELECT DISTINCT 
    UNNEST(
        CASE 
            WHEN account_number ~ '^IC\d+IC\d+$' THEN REGEXP_MATCHES(account_number, 'IC\d+', 'g')
            ELSE ARRAY[account_number]
        END
    ) AS cleaned_value
FROM your_table;

逻辑说明 #

1. 用~匹配双IC拼接格式；
2. 符合条件的串用REGEXP_MATCHES加g参数全局提取所有IC+数字片段，得到数组；
3. 非双IC格式的数值包装为单元素数组；
4. 用UNNEST将数组展开为行，最后DISTINCT去重。

对你之前尝试的问题说明 #

你之前用的REGEXP_SUBSTR('account','number','[^IC]',1,2)逻辑有误：[^IC]是匹配非I、非C的字符，会直接跳过IC前缀，导致丢失格式；而且该写法没有针对双IC拼接的场景做拆分，无法满足需求。

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