How to get a string from hexadecimal value in SQL

Question

question

dduser asked Feb 2, '16

How to get a string from hexadecimal value in SQL

I need to read a hexadecimal value field ( it starts from 0x504B030414...) in normal text

convert

6

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush commented · Feb 02, 2016 at 09:01 AM

I'm not clear what the problem is. Can you explain a bit more please?

0 ·

dduser commented · Feb 02, 2016 at 09:05 AM

There is a image type field in a sql table , value is 0x504B030414... Need to get the text and what it contains using SQL script.

0 ·

David Wimbush commented · Feb 02, 2016 at 09:46 AM

So presumably there is some code/process that is turning this text into the binary version that gets stored in the column. I think you need to know what that code/process is so you can reverse it.

0 ·

dduser commented · Feb 02, 2016 at 09:55 AM

I am doing a data conversion, so I dont have any coding

0 ·

David Wimbush commented · Feb 02, 2016 at 10:00 AM

Then all I can suggest is to try the CAST and CONVERT functions. It might be as simple as CAST(columnname AS NVARCHAR(MAX)).

0 ·

Show more comments

Answer 1 · 2016-02-02T18:02:20Z

perrywhittle answered Feb 2, '16

there is a built in function for doing this, use the following as an example SELECT SID , [sys].[fn_varbintohexstr](sid) AS SID_String FROM sys.database_principals

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dduser commented · Feb 05, 2016 at 06:03 AM

How to use this to my table ? There is a field in the table with image data type and it incldeues 0x504B030414.. value HOW TO GET THIS DATA INTO NORMAL TEXT??? I am doing a data conversion so I have only the database and no any coding

0 ·

dduser commented · Feb 05, 2016 at 07:14 AM

actually it s a binary zip file and contans a rtf file, I need to get that rtf directly

0 ·

Answer 2 · 2016-02-05T09:53:50Z

perrywhittle answered Feb 5, '16

you cant get the converted file through TSQL, you need to use either VB.net or C# to create a stream and read the object

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush commented · Feb 05, 2016 at 02:30 PM

Agreed. I think you need te re-think what you are trying to do here. I just typed "Hello world" and saved it as an RTF file. When I open the file in Notepad the text is readable characters, not binary. But there are 203 lines of what looks like meaningless junk with my text buried in the middle. So, even if you can convert this binary into a readable string, you will still have to find where the content starts. And then you will have to separate the markup from the text. I made the word "world" bold and underlined and now it looks like this in the file: \insrsid205060 Hello }{\rtlch\fcs1 \af0 \ltrch\fcs0 \b\ul\insrsid205060\charrsid8089627 world}{\rtlch\.

0 ·

question