如何在Azure SQL中识别Varbinary(max)字段中的文件类型？

可以通过SQL实现，核心思路是检查文件的魔术数字（文件头字节序列）——绝大多数文件类型都有固定的开头字节，我们只需提取Varbinary字段的前若干字节，与已知的魔术数字对比即可判断文件类型。

实现示例 #

以下是针对常见文件类型的SQL查询，你可以根据需求扩展更多类型：

SELECT 
    ID, -- 替换为你的表主键字段
    CASE
        -- PDF：开头为 %PDF-
        WHEN SUBSTRING(YourBinaryColumn, 1, 4) = 0x25504446 THEN 'PDF'
        -- Office Open XML格式（DOCX/PPTX/XLSX等）：本质是ZIP压缩包，前2字节为 PK
        WHEN SUBSTRING(YourBinaryColumn, 1, 2) = 0x504B THEN 'Office Open XML (DOCX/PPTX/XLSX)'
        -- 旧版Office文件（DOC/XLS/PPT）：前8字节固定为 0xD0CF11E0A1B11AE1
        WHEN SUBSTRING(YourBinaryColumn, 1, 8) = 0xD0CF11E0A1B11AE1 THEN 'Legacy Office (DOC/XLS/PPT)'
        -- JPG图片：前2字节为 FF D8
        WHEN SUBSTRING(YourBinaryColumn, 1, 2) = 0xFFD8 THEN 'JPG'
        -- PNG图片：前8字节固定为 0x89504E470D0A1A0A
        WHEN SUBSTRING(YourBinaryColumn, 1, 8) = 0x89504E470D0A1A0A THEN 'PNG'
        -- GIF图片：开头为 GIF89a 或 GIF87a
        WHEN SUBSTRING(YourBinaryColumn, 1, 6) IN (0x474946383961, 0x474946383761) THEN 'GIF'
        -- 空字段处理
        WHEN YourBinaryColumn IS NULL THEN 'Empty'
        ELSE 'Unknown'
    END AS FileType
FROM YourTableName; -- 替换为你的表名

注意事项 #

• 替换查询中的YourBinaryColumn和YourTableName为实际的字段名与表名
• 对于Office Open XML格式，SQL无法直接区分DOCX/PPTX/XLSX（因为都是ZIP包），如果需要细分，需结合外部工具解压读取内部文件结构
• 可根据需求扩展更多文件类型的魔术数字，比如ZIP（同Office Open XML，前2字节0x504B）、TXT（无固定魔术数字，可尝试判断是否为可打印ASCII字符）等

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