SQL Server：按固定长度为列值左侧补0的实现方法

针对你提到的需求——把包含MRN 1946 456 27 557的列，将其中每个数字段补0至6位（左侧补0），最终输出MRN 001946 000456 000027 000557，可以用SQL Server的字符串拆分和聚合函数来实现，以下是具体方法：

适用SQL Server 2016及以上版本（支持STRING_SPLIT和STRING_AGG） #

这个版本的内置函数能大大简化处理流程，直接拆分数字段、补0后再拼接回去：

SELECT 
    'MRN ' + STRING_AGG(RIGHT('000000' + TRIM(split_value.value), 6), ' ') AS formatted_result
FROM 
    your_table
CROSS APPLY 
    STRING_SPLIT(SUBSTRING(your_column, 5, LEN(your_column)), ' ') AS split_value
WHERE 
    TRIM(split_value.value) <> '' -- 过滤可能的空字符串
GROUP BY 
    your_column;

代码解释： #

• SUBSTRING(your_column, 5, LEN(your_column))：截取原字符串中MRN 之后的所有内容（因为MRN 占前4个字符，从第5位开始是数字部分）
• STRING_SPLIT(..., ' ')：把截取后的数字部分按空格拆分成单独的行，每个行对应一个数字段
• RIGHT('000000' + TRIM(value), 6)：核心补0逻辑——给每个数字段前面拼接6个0，然后取右边6位。这样不管原数字是1-6位中的任意长度，最终都会变成6位，不足的左侧补0；如果刚好6位，就保留原数字本身
• STRING_AGG(..., ' ')：把处理后的6位数字段用空格重新拼接成字符串，再加上前缀MRN 得到最终结果

兼容旧版本SQL Server（无STRING_SPLIT/STRING_AGG） #

如果你的SQL Server版本低于2016，可以用XML来模拟拆分和聚合的逻辑，示例代码如下：

WITH split_numbers AS (
    SELECT 
        your_column,
        CAST('<val>' + REPLACE(SUBSTRING(your_column, 5, LEN(your_column)), ' ', '</val><val>') + '</val>' AS XML) AS xml_values
    FROM your_table
),
formatted_numbers AS (
    SELECT 
        your_column,
        RIGHT('000000' + TRIM(x.val.value('.', 'VARCHAR(10)')), 6) AS formatted_val
    FROM split_numbers
    CROSS APPLY xml_values.nodes('/val') x(val)
    WHERE TRIM(x.val.value('.', 'VARCHAR(10)')) <> ''
)
SELECT 
    'MRN ' + STUFF((
        SELECT ' ' + formatted_val
        FROM formatted_numbers fn
        WHERE fn.your_column = main.your_column
        FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS formatted_result
FROM formatted_numbers main
GROUP BY your_column;

逻辑说明： #

• 先把数字部分转换成XML格式，用<val>标签包裹每个数字段
• 通过nodes()方法拆分XML得到单个数字段，同样用RIGHT('000000' + ...)完成补0操作
• 最后用FOR XML PATH('')把补0后的数字段拼接起来，再用STUFF去掉开头多余的空格，加上MRN 前缀得到最终结果

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