如何用正则表达式（regex）查找相似字符串？数据库链接转HTTPS去重SQL求助

我来帮你搞定这个问题！针对你数据库里同时存在HTTP和HTTPS格式链接、转换时会触发重复错误的情况，结合你定义的.com/[a-zA-Z]*特征规则，我整理了不同数据库环境下的SQL查询方案：

核心思路 #

我们先从每个链接中提取你指定的特征（.com/后跟连续字母的部分），再识别链接的协议类型，最后筛选出同一特征下同时存在HTTP和HTTPS版本的记录——这些就是转换时会导致重复的风险项。

MySQL 版本 #

-- 方案1：按特征分组，查看存在双协议的链接集合
SELECT 
    link_feature,
    GROUP_CONCAT(DISTINCT url SEPARATOR '\n') AS conflicting_urls
FROM (
    SELECT 
        url,
        -- 提取.com/后的连续字母特征
        REGEXP_SUBSTR(url, '\\.com\\/[a-zA-Z]+') AS link_feature,
        -- 识别协议类型
        CASE 
            WHEN url LIKE 'https://%' THEN 'https'
            ELSE 'http'
        END AS protocol
    FROM your_table_name
    -- 只筛选符合特征规则的链接
    WHERE url REGEXP '\\.com\\/[a-zA-Z]+'
) AS url_data
GROUP BY link_feature
-- 只保留同时有http和https的特征组
HAVING COUNT(DISTINCT protocol) = 2;

-- 方案2：直接成对展示http和https重复链接
SELECT 
    a.url AS http_version,
    b.url AS https_version
FROM your_table_name a
JOIN your_table_name b 
    ON REGEXP_SUBSTR(a.url, '\\.com\\/[a-zA-Z]+') = REGEXP_SUBSTR(b.url, '\\.com\\/[a-zA-Z]+')
    AND a.url LIKE 'http://%'
    AND b.url LIKE 'https://%';

PostgreSQL 版本 #

-- 按特征分组查看冲突链接
SELECT 
    link_feature,
    STRING_AGG(DISTINCT url, ', ') AS conflicting_urls
FROM (
    SELECT 
        url,
        -- 提取特征
        SUBSTRING(url FROM '\.com\/[a-zA-Z]+') AS link_feature,
        -- 识别协议
        CASE 
            WHEN url LIKE 'https://%' THEN 'https'
            ELSE 'http'
        END AS protocol
    FROM your_table_name
    WHERE url ~ '\.com\/[a-zA-Z]+'
) AS url_data
GROUP BY link_feature
HAVING COUNT(DISTINCT protocol) = 2;

SQL Server 版本 #

-- 按特征分组查看冲突链接
SELECT 
    link_feature,
    STRING_AGG(DISTINCT url, ', ') AS conflicting_urls
FROM (
    SELECT 
        url,
        -- 提取.com/后的连续字母特征
        SUBSTRING(
            url,
            PATINDEX('%.com/[a-zA-Z]%', url),
            PATINDEX('%[^a-zA-Z]%', SUBSTRING(url, PATINDEX('%.com/[a-zA-Z]%', url) + 5, LEN(url))) + 5
        ) AS link_feature,
        -- 识别协议
        CASE 
            WHEN url LIKE 'https://%' THEN 'https'
            ELSE 'http'
        END AS protocol
    FROM your_table_name
    WHERE PATINDEX('%.com/[a-zA-Z]%', url) > 0
) AS url_data
GROUP BY link_feature
HAVING COUNT(DISTINCT protocol) = 2;

注意事项 #

• 记得把代码中的your_table_name替换成你实际的表名，url替换为存储链接的字段名。
• 如果你的链接中.com/后面包含数字、下划线等其他字符，可以把正则里的[a-zA-Z]+修改为[a-zA-Z0-9_]+来适配。
• 方案2的自连接查询可以直接看到具体的重复链接对，方便你针对性处理（比如删除其中一个版本，或者调整转换逻辑）。

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