SQL Query - BLOB Conversion to String

Recently all the data migrations that I have been doing I have had to try to learn some SQL on the fly. It has been a bit of a struggle, but with all the information out there on it Google has become my best friend.

A recent issue I ran into was the fact that some information is store in a SQL database in a Binary Large OBject for or BLOB. It will usually look something like this

UEHFNSDK56N36KI3N6KI3N3M46AFD8XCV78912HJKASUIY45LHSDA

Just a bunch of non-sense. I usually need it to be in a an excel spreadsheet in a reasonable format that can be read, the text above mean nothing. So I did a bit of research and putting a few thing that I learned together I a an SQL Query that looks like this to convert it to text.

select AccountNo, CONVERT(varchar(max),convert(varbinary(max),NOTES)) from CONTHIST

This gives me 2 columns in the query results, the first being the AccountNo that I would use to match the notes up to a given account and the second being a column  of converted BLOB text.

This is all well and good, but for the life of me I can not figure out how to get the query results out into a spreadsheet or even a manageable format that I could put into a spreadsheet. I can get standard query results into excel, but as I have been told, because the notes contain return chars that breaks up the copy and paste.

As soon as I figure this out I will be sure to post it so I don't forget it!





Here is a nice little replace function that removes carriage returns that can cause headaches going from SQL results to Excel. TaskItem would be the column name.

REPLACE(taskitem, Char(13) + Char(10), ' ') as TaskItem

Replace and convert at the same time:

REPLACE(convert(varchar(max),convert(ntext,TaskToDo)), Char(13) + Char(10), ' ')