SQL Server 2012中用对应项目编号替换文件路径字符串部分内容

针对你需要将File Path列中不规范的项目编号替换为对应WBS1列规范值的需求，以下是基于SQL Server 2012的解决方案：

思路 #

核心逻辑是：先提取WBS1的核心字符（去除点号），再在文件名中定位与之匹配的不规范编号片段，最后将该片段替换为完整的WBS1值。由于文件名中的编号存在缺前导零、无点号、带后缀等多种格式，需通过自定义函数实现灵活匹配。

步骤1：创建匹配片段提取函数 #

该函数用于在文件名中定位需要替换的不规范编号部分：

CREATE FUNCTION dbo.FindMatchingSegment(@FileName VARCHAR(200), @WBS_Core VARCHAR(20))
RETURNS VARCHAR(200)
AS
BEGIN
    DECLARE @Match VARCHAR(200) = '';
    -- 优先匹配完整的WBS核心字符（无点号）
    SET @Match = SUBSTRING(@FileName, PATINDEX('%' + @WBS_Core + '%', @FileName), LEN(@WBS_Core));
    IF @Match <> '' RETURN @Match;

    -- 匹配去掉前导零的WBS核心字符（处理如099→99的情况）
    DECLARE @NoLeadingZeroCore VARCHAR(20) = LTRIM(REPLACE(@WBS_Core, '0', ' '));
    SET @NoLeadingZeroCore = REPLACE(@NoLeadingZeroCore, ' ', '0');
    DECLARE @StartPos INT = PATINDEX('%' + @NoLeadingZeroCore + '%', @FileName);
    IF @StartPos > 0
    BEGIN
        SET @Match = SUBSTRING(@FileName, @StartPos, PATINDEX('%[^0-9A-Za-z]%', SUBSTRING(@FileName, @StartPos, LEN(@FileName)) + ' ') - 1);
        RETURN @Match;
    END;

    -- 匹配带点号的部分片段（处理如108.0050的情况）
    DECLARE @Part1 VARCHAR(10) = LEFT(@WBS_Core, 3);
    DECLARE @Part2 VARCHAR(10) = SUBSTRING(@WBS_Core, 4, 4);
    SET @StartPos = PATINDEX('%' + @Part1 + '.' + @Part2 + '%', @FileName);
    IF @StartPos > 0
    BEGIN
        SET @Match = SUBSTRING(@FileName, @StartPos, 8);
        RETURN @Match;
    END;

    RETURN @Match;
END;

步骤2：执行更新语句 #

通过CTE拆分路径为目录和文件名，调用函数完成替换：

WITH FilePathSplit AS (
    SELECT
        WBS1,
        [File Path],
        -- 提取目录部分（最后一个反斜杠之前的内容）
        LEFT([File Path], LEN([File Path]) - CHARINDEX('\', REVERSE([File Path]))) AS Directory,
        -- 提取文件名部分（最后一个反斜杠之后的内容）
        RIGHT([File Path], CHARINDEX('\', REVERSE([File Path])) - 1) AS FileName,
        -- 生成WBS1的无点号核心字符
        REPLACE(WBS1, '.', '') AS WBS_Core
    FROM 你的表名 -- 替换为实际表名
)
UPDATE 你的表名
SET [File Path] = fps.Directory + '\' + REPLACE(fps.FileName, dbo.FindMatchingSegment(fps.FileName, fps.WBS_Core), fps.WBS1)
FROM 你的表名 t
JOIN FilePathSplit fps ON t.WBS1 = fps.WBS1
WHERE dbo.FindMatchingSegment(fps.FileName, fps.WBS_Core) <> '';

验证效果 #

执行上述语句后，数据将被修正为期望格式：

注：若表中存在WBS1重复的情况，需将JOIN条件调整为唯一标识列（如主键），避免更新错误。

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