如何转换SQL中Image类型列存储的坐标数组二进制数据

问题背景 #

• 待解析数据存储在SQL Server的IMAGE类型列中，为图形类应用导出的X、Y整数坐标集合
• 二进制数据示例如下：

0x0060C74500E8CF450080D0450058D1450090D24500F8D3450058D5450098D6450000D84500F0D8450000DA4500F8D94500E0D9450020DB450050DB450070DA450050DB450050DC4500A0DC450098DB450080DC450000DE4500C0DD450080DE4500A0DF450060E0450028E1450070E1450038E4450040E5450078E4450070E34500A0E34500C0E24500D0E0450088E0450078E34500E8E4450008E64500F8E7450060EB450018EC450050EB4500A8EA450060EA4500B8E84500E0E7450050E8450078EA450098EE4500A0F24500F0F5450060F74500E8F7450030F6450050F24500F0EE4500A0EC450058EB450028EB450090ED4500A0F04500D8F2450000F5450018F74500E8F5450000F3450038F04500F8EC450008EA4500C8E94500A8EC4500E8F04500E8F44500F8F84500D8FB4500D8FB450020F94500E8F44500E0F04500F8ED450078ED450060EF450058F4450048FA4500500046007C0246001803460008024600D0FE4500C8F84500B8F2450000EF4500D0EF450030F6450010FF4500A8034600F806460090084600D80546001C01460010F84500A0EE450008E54500B0DA4500E0CF450068C44500D0BB4500A8B54500B8AF450080AA450078A14500A097450058954500909345003092450020904500E08E450030904500A89045001891450040914500B88F4500C08C4500408A4500B08845007886450050854500608645003088450040894500A8894500688A4500208945009085450038824500907E450050784500B0744500C0754500E0794500307F450000834500688545009084450080824500707D450090724500F0674500F0604500305F4500F0604500B069450040784500C88245008085450028854500D8824500007A4500706B450050604500A05C4500805C4500E0634500B0704500907E4500E083450038864500D8854500507D4500B06A4500805B450080524500804D450050594500406C4500507E450090874500B08E450018914500888545003077450070634500D0504500303F450040454500505E4500F07545006886450008924500189D4500D0A64500A8AC4500D0B34500A0B84500B8BB450028BF450098C2450068C6450060C745295C8F3D295C0F3E9A99993E6666E63E295C0F3FAE47813FF628BC3F3D0AF73FC3F51840B81E4540666676401F859340F628AC405C8FCA40F628EC4048E106410AD7174152B82A411F853F41EC515441B81E6941295C7F411F858B41A4709741295CA34114AEAF415C8FBC411F85C941E17AD641A470E34148E1F041EC51FE410AD705425C8F0C42AE471342C3F51942D7A32042295C274285EB2D42A470344200003B421F8541428FC2474200004E42713D54421F855A42295C6042333366427B146C4285EB7142E17A774248E17C42D723814229DC8342A4708642A4F08842856B8B4285EB8D42EC519042B89E924248E19442F628974248619942C3759B421F859D429A999F42D7A3A1421F85A3424861A5428F42A742B81EA942CDCCAA42E17AAC42F628AE42ECD1AF420A57B142ECD1B242CD4CB442AEC7B542D723B742856BB84252B8B9420000BB423333BC42CD4CBD424861BE42E17ABF420080C0424861C1428F42C242D723C342E1FAC342F6A8C442EC51C542E1FAC542B89EC6423D0AC742A470C74229DCC7428F42C842856BC8425C8FC84214AEC84266E6C84233B3C8420080C842EC51C842B81EC84233B3C742D723C7427B94C6421F05C6428F42C542CD4CC442295CC3426666C242AE47C14266E6BF423D8ABE42F628BD4233B3BB4266E6B942B81EB8420A57B6421F85B442295CB242142EB0421F05AE4229DCAB424861A94248E1A6424861A44248E1A142F6289F4248619C42D7A3994248E19642C3F59342000091421F058E427B148B421F058842A4F084420AD78142A4707D42F6287742CDCC7042A4706A427B14644252B85D42EC51574285EB50421F854A42B81E44428FC23D42295C3742000031429A992A42295C2442B81E1E4248E11742D7A31142D7A30B42D7A30542AE47FF41AE47F3411F85E741F628DC4152B8D041AE47C541F628BA419A99AF413D0AA5415C8F9A417B149041E17A864185EB794152B8664133335341666642419A993141C3F52041F6281041A4700141295CE7400AD7CB40EC51B040295C974048E182401F855B4085EB3140F6280C400AD7E33F7B14AE3F3333733F85EB113FF628DC3E295C8F3E295C0F3E295C8F3D

踩坑记录 #

前期尝试多种转换方式均触发错误，失败方案如下：

• 尝试解压后转NVARCHAR，触发类型转换错误：

SELECT CAST(DECOMPRESS(SurfaceCardB) AS NVARCHAR(max)) AS DataAsText FROM [tblCardData];

• 尝试转VARBINARY后再转XML，触发XML解析错误：

SELECT CONVERT(xml, (CONVERT(varbinary(max), SurfaceCardB))) AS DataAsText FROM [tblCardData];

报错信息：Msg 9403, Level 16, State 1, Line 1 XML parsing: line 0, character 0, unrecognized input signature

• 直接使用开发人员提供的单转语句，触发显式转换错误：

select CONVERT(VARCHAR(MAX), "myimagecolumn", 2) from "yourtable"

正确转换方案 #

根据列的实际数据类型选择对应语句：

列类型为IMAGE #

IMAGE类型无法直接带样式转换为VARCHAR，必须先转为VARBINARY(MAX)做中间层，再用样式码2输出十六进制文本格式，语句如下：

SELECT 
    CONVERT(VARCHAR(MAX), CONVERT(varbinary(MAX), [yourimagecolumn]) ,2) AS CoordinateData
FROM [yourtable] 
WHERE [atablecolumn] = 'target filter value'

重要提示：IMAGE是SQL Server已淘汰的遗留数据类型，后续数据库设计请使用VARBINARY(MAX)替代，避免不必要的类型转换问题。

列类型为VARBINARY(MAX) #

无需嵌套转换，直接使用带样式码2的转换语句即可：

SELECT CONVERT(VARCHAR(MAX), [yourvarbinarycolumn], 2) AS CoordinateData FROM [yourtable]

转换得到十六进制文本后，即可按4字节（小端序）为单位拆分解析为对应的X、Y坐标值。

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