如何在SQL结果集中单列替换多类字符并生成固定长度扁平文件

核心处理逻辑 #

要一次性清除多种目标字符，需要嵌套REPLACE函数依次处理每个需删除的符号；针对前缀Dr和后缀Jr，需先移除这些前后缀（兼顾大小写变体），再处理符号，最后截取固定长度并补空格保证列宽一致。

推荐方案：封装自定义清洗函数 #

为避免重复编写冗余代码，先创建通用姓名清洗函数：

CREATE FUNCTION dbo.CleanName(@inputName VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
    DECLARE @cleanedName VARCHAR(100) = @inputName
    
    -- 移除前缀Dr（含大小写）
    SET @cleanedName = REPLACE(REPLACE(@cleanedName, 'Dr ', ''), 'dr ', '')
    -- 移除后缀Jr（含大小写）
    SET @cleanedName = REPLACE(REPLACE(@cleanedName, ' Jr', ''), ' jr', '')
    
    -- 清除连字符、撇号、空格、句点
    SET @cleanedName = REPLACE(REPLACE(REPLACE(REPLACE(@cleanedName, '-', ''), '''', ''), ' ', ''), '.', '')
    
    RETURN @cleanedName
END
GO

修改后的查询语句 #

调用函数处理三个姓名字段，确保固定列宽：

SELECT
    REPLACE(idNum, '-', '') +
    'ABC' +
    '123' +
    -- lastName：清洗后取前13位，不足补空格
    LEFT(dbo.CleanName(lastname) + REPLICATE(' ', 13), 13) +
    -- firstName：清洗后取前10位，不足补空格
    LEFT(dbo.CleanName(firstName) + REPLICATE(' ', 10), 10) +
    -- middleName：清洗后取前7位，不足补空格
    LEFT(dbo.CleanName(middleName) + REPLICATE(' ', 7), 7) +
    REPLACE(CONVERT(CHAR(10), dateField, 101),'/','') +
    '                                    ' +
    REPLACE(LEFT(CLNum + REPLICATE(' ', 6), 6), '''', '') +
    REPLACE(LEFT(UID + REPLICATE(' ', 8), 8), '''', '') +
    '                    ' +
    'DEFG' +
    '000'
FROM TableName
WHERE CLNum = '1234'
AND ISNULL(dateField, '') <> ''

无函数替代方案（直接嵌套REPLACE） #

若无法创建自定义函数，可直接在查询中嵌套所有替换逻辑：

SELECT
    REPLACE(idNum, '-', '') +
    'ABC' +
    '123' +
    LEFT(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(lastname, 'Dr ', ''), 'dr ', ''), ' Jr', ''), ' jr', ''), '-', ''), '''', ''), ' ', ''), '.', '')
        + REPLICATE(' ', 13), 13
    ) +
    LEFT(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(firstName, 'Dr ', ''), 'dr ', ''), ' Jr', ''), ' jr', ''), '-', ''), '''', ''), ' ', ''), '.', '')
        + REPLICATE(' ', 10), 10
    ) +
    LEFT(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(middleName, 'Dr ', ''), 'dr ', ''), ' Jr', ''), ' jr', ''), '-', ''), '''', ''), ' ', ''), '.', '')
        + REPLICATE(' ', 7), 7
    ) +
    REPLACE(CONVERT(CHAR(10), dateField, 101),'/','') +
    '                                    ' +
    REPLACE(LEFT(CLNum + REPLICATE(' ', 6), 6), '''', '') +
    REPLACE(LEFT(UID + REPLICATE(' ', 8), 8), '''', '') +
    '                    ' +
    'DEFG' +
    '000'
FROM TableName
WHERE CLNum = '1234'
AND ISNULL(dateField, '') <> ''

效果验证 #

输入McDonald-O'Leary经清洗后得到McDonaldOLeary，截取前13位即为McDonaldOLear，完全符合需求。

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