如何批量替换Varchar字段中的URL：提取6位数字生成指定新URL

核心逻辑 #

针对长度为42位、第32-37位是6位数字的错误URL，通过正则匹配定位目标链接，提取中间6位数字后，替换为固定前缀https://www.this.org/number/加该数字的新URL。以下是主流数据库的落地实现：

SQL Server 实现 #

SQL Server可通过PATINDEX匹配URL模式，结合循环处理单条记录中的多个匹配项：

-- 先备份数据到临时表（操作原表前务必备份）
SELECT * INTO #TempCharacteristics FROM Characteristics;

DECLARE @OldUrlPattern VARCHAR(100) = 'http://replace.this.org/number/[0-9][0-9][0-9][0-9][0-9][0-9].html';
DECLARE @NewPrefix VARCHAR(50) = 'https://www.this.org/number/';

-- 循环替换每条记录里的所有目标URL
WHILE EXISTS (SELECT 1 FROM #TempCharacteristics WHERE PATINDEX(@OldUrlPattern, FullDescription) > 0)
BEGIN
    UPDATE #TempCharacteristics
    SET FullDescription = STUFF(
        FullDescription,
        PATINDEX(@OldUrlPattern, FullDescription),
        42,
        @NewPrefix + SUBSTRING(FullDescription, PATINDEX(@OldUrlPattern, FullDescription) + 31, 6)
    )
    WHERE PATINDEX(@OldUrlPattern, FullDescription) > 0;
END

-- 验证无误后同步回原表
UPDATE c
SET c.FullDescription = tc.FullDescription
FROM Characteristics c
JOIN #TempCharacteristics tc ON c.主键列 = tc.主键列;

DROP TABLE #TempCharacteristics;

MySQL 8.0+ 实现 #

MySQL 8.0及以上版本支持REGEXP_REPLACE，可直接用正则捕获组提取6位数字并替换：

UPDATE Characteristics
SET FullDescription = REGEXP_REPLACE(
    FullDescription,
    'http://replace.this.org/number/([0-9]{6})\\.html',
    'https://www.this.org/number/$1'
);

说明：正则中的([0-9]{6})是捕获组，对应原URL的6位数字，替换时用$1引用该组内容，默认会替换所有匹配项。

PostgreSQL 实现 #

PostgreSQL支持正则捕获组替换，需指定g参数实现全局替换：

UPDATE Characteristics
SET FullDescription = REGEXP_REPLACE(
    FullDescription,
    'http://replace.this.org/number/([0-9]{6})\\.html',
    'https://www.this.org/number/\1',
    'g'
);

说明：'g'参数确保替换单条记录中的所有目标URL，不加则仅替换第一个匹配项。

注意事项 #

1. 操作前必须备份原数据表，避免数据不可逆损失。
2. 若原URL前缀与示例有差异，需对应调整正则表达式的前缀部分，保证匹配准确性。
3. SQL Server无原生全局正则替换能力，循环处理是可靠的替代方案，需循环到记录中无匹配URL为止。

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