asked on

Convert a BLOB Field in SQL Server table to Usable String

I have a BLOB field in SQL Server that I need to be converted to a usable format. I have so far gotten nothing but unreadable characters. It is supposed to be a series of geographic points.

Open in new window

Below is an example of one of the values in the BLOB field

"0xAC100000010000009795D5A7CE1296E3BCDFB919D2ADA805A5BB56EFA17AB0C40FFDD0AA01B5D615E889108E8502E48118978603D09B0D8ED701D087D307BBAB7CF8B75D95EE0BC6EFCF09BDAB9C01F8DED2029CFC2ADD8CDB0285832CE0E30C92CF01CC9E3B9AC107DAFACA0193DE19EFEF0D98E101F9CBDF01B0AC1CD3BC0C8BCA01CDCD72B9C30EC9CBFC02909F30FFE7B007B7DB77D4B7D40588E05BF981D9049A8B4CFCC0990A9698A501F7FE13A5C302EAF628AE9605ED9411B19502C2E29807A8B674DC80F601B3E0ED01CBF76BA9A768ECF06383C360D0EA0AB1BB0AC9D24CAB884ACFC308909E08FC98068CFE05D5F9E302A8FAD702EA859A05A2C28305D8EC3EAEDB3CF9CF088AAA08D5FD39BA8138D1842287F020DFF70F86B20FD3860EABC90DD6A4189BBA17CAF04E8E994CEB8443A4E040D9FF1ABC891AD7BD9302AB8C8A02F6BC8703A48DFA02CAE19202B6AC8902E5FD9601BEE69101D3EA85038FB7F802DAA1E80187C177FEA4D1059F8AF302D9AEE803949BFB01E2CE0ABCBA05E8FB0C98D506E5EEDA04AE88B602F5B1E00182B373D393960387EDD001EF9FA507ADE9DF03ED871AB2B20DFAA19202ADFE8C01D9B1D70794B9F903DD9E37BBE702F687AC0A9DAD43C4B2D904A7C61EF6F6D9019B880BEDE0F8039ECE19C498A8018CC408ECF8A603B3B815D4D21BA6B301DAA9C105B2DD23D58FAC0AB4FE42C791AC0A90EF42D0DBB20499B01CEBB8F905B9AE26F196AC0A8AD042DF98AC0AA6C042DE9EC6088FEE36F5FCE50198C20BF686B202ADA90FEF96FA07B2F732D7A0AC0ABB9142E7A2AC0ABC8142F6A4AC0A98F241D3AC3189BA02D4FBFA09ABA83FCCB9DB06A0CF2AE29FF6029AF68201E0BE5EAD8621D3B4B70A9187D303CFE0DE02BFD27AF78AD7049EB9D101C9F99E019AC837CCE1E20181A24FFE8D9C06A7A09602F2B5900388F78B01FAFD8901A9C206C2C2AC0AB6C03EC7C4AC0AACB13EF5BBC206BD9E27FA8AEA03AF8217F3C8AC0AA2923ED9CBAC0ABE823EDB899605B9F91EFACA579F8D17EF8CC30482E09801C78AC0049FF69701E5F4F40586C9C701DEDF9603AC9C6BC3AC9E07BF92F401F598B50AB19FDF02F2CC3B9AD90FF7D8F9099EB6CF02DFEEA702A2F24DD2B4479DE612E2ADC3018AC912F9FDFD05A2E548F4B7D0059DB644EBB2DE049DC839FBEEAE0AAAEF7DF18CBF05BAD242C8C6F4048385DA01F7821DB5880AD49EB602A3CD6BCFEBE507B184DA02E4D795069EE49102E7A9EF019CFB52D79FB102B4F607ECAEAC0A8DA822C5D3F0068FE016EFFFC901CFE449CAC1EC01CAB256D89E8E01C8FB33E3B5A108E9AC8203E4B073889CA701C8D88501ADDEC101D4A7BD0382A48505F5AEBD03A2A28505C49326A8A137E5AFC202BCFED203F5F454A6FF7ACC858C0383CFBD049C9949B5824AAEC2F402ACD8F80288FE9E02BE95A20285CD93059BEA9A05A69973A8B974AAA1DE01B5D4E001819EC2028BD8C502859EB604989DBC04"

Open in new window

I am unsure of how to write the query to return something in a readable format.
Thanks!

EDIT, I added an export of the table to possibly help with a response.
TableF281Points.xlsx

Jonathan Mori

ASKER

I have similar BLOB fields in the same Database. When I did the query pictured above, it returned XML, but with this field, it is only returning the unreadable symbols pictured in the jpg. I am not sure what the final output is supposed to look like, in theory, it is a series of points that forms a line, but the query in the jpg is returning those symbols.

Michael Hurley

Is it Possible that you may have a stored image in Base64-encoded text format? From what I have read, It looks like SQL Server CAST() and CONVERT() can NOT convert Base64-encoded text into images. Can you check the tables in your database to confirm? If so, one of our top Experts onE-E, @ScottFell has forwarded me this info on how to decode and convert those back into your system. Here is that article--Thank you @ScottFell! https://www.mssqltips.com/sqlservertip/5653/convert-sql-server-base64-encoded-text-to-images/

Let us know if an image is the culprit!
Thanks,
Michael

Jonathan Mori

ASKER

Thank you for the response, I am working on step one,

WITH ConvertBase64 AS (
SELECT 
   points
   , 'C:\Users\morij\Desktop\Layers\CLShapefiles' + points as [PathAndFileName]
FROM dbo.f281
)
SELECT 
   [PathAndFileName]
   , CAST('' AS XML).value('xs:base64Binary(sql:column("points"))'
     , 'varbinary(MAX)') AS img
FROM ConvertBase64

Open in new window

Receiving the error,
msg 402, Level 16, State 1, Line 4
The data types varchar and varbinary(max) are incompatible in the add operator.
I will keep trying.

ASKER CERTIFIED SOLUTION

Jonathan Mori

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial