T-SQL拆分文件大小字段后按单位统一转换为Mb的实现方法

可以基于拆分得到的单位列做条件判断，匹配对应换算系数完成单位转换，输出和目标格式完全一致的Mb单位结果。

换算规则匹配（对齐目标输出值） #

• bytes 转 Mb：除以1,000,000，对应68 bytes输出0.000068的要求
• Kb 转 Mb：除以1,000，对应122.80 Kb输出0.1228的要求
• Mb 单位数值直接保留，对应23.5 Mb输出23.5的要求
• Gb 转 Mb：乘以1,000，对应1.2 Gb输出1200的要求

完整SQL实现 #

完全兼容你已经验证过的拆分逻辑，直接在查询层增加单位判断即可。如果不需要保留原始拆分的数值和单位列，可直接删除查询中对应的Size和Metric字段，只输出换算后的结果。

WITH Splitted AS
(
    SELECT CAST(N'<x>' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([File Size],N' ',N' '),N'&',N'&'),N'<',N'<'),N'>',N'>'),N' ',N'</x><x>') + N'</x>' AS XML) testXML
    FROM Attachment
)
SELECT 
    testXML.value('/x[1]','float') AS Size,
    testXML.value('/x[2]','nvarchar(max)') AS Metric,
    CASE testXML.value('/x[2]','nvarchar(max)')
        WHEN N'bytes' THEN testXML.value('/x[1]','float') / 1000000.0
        WHEN N'Kb' THEN testXML.value('/x[1]','float') / 1000.0
        WHEN N'Mb' THEN testXML.value('/x[1]','float')
        WHEN N'Gb' THEN testXML.value('/x[1]','float') * 1000.0
    END AS [Converted FileSize]
FROM Splitted

可选优化 #

如果实际存储的单位存在大小写不一致的情况（如出现kb、GB这类写法），可以对匹配的单位值统一转大小写后再判断，例如：

CASE 
    WHEN LOWER(testXML.value('/x[2]','nvarchar(max)')) = N'bytes' THEN testXML.value('/x[1]','float') / 1000000.0
    WHEN LOWER(testXML.value('/x[2]','nvarchar(max)')) = N'kb' THEN testXML.value('/x[1]','float') / 1000.0
    WHEN LOWER(testXML.value('/x[2]','nvarchar(max)')) = N'mb' THEN testXML.value('/x[1]','float')
    WHEN LOWER(testXML.value('/x[2]','nvarchar(max)')) = N'gb' THEN testXML.value('/x[1]','float') * 1000.0
END AS [Converted FileSize]

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