question

dduser avatar image
dduser asked

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 comments
10 |1200 characters needed characters left characters exceeded

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

I'm not clear what the problem is. Can you explain a bit more please?
0 Likes 0 ·
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 Likes 0 ·
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 Likes 0 ·
I am doing a data conversion, so I dont have any coding
0 Likes 0 ·
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 Likes 0 ·
Show more comments
perrywhittle avatar image
perrywhittle answered
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 comments
10 |1200 characters needed characters left characters exceeded

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

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 Likes 0 ·
actually it s a binary zip file and contans a rtf file, I need to get that rtf directly
0 Likes 0 ·
perrywhittle avatar image
perrywhittle answered
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 comment
10 |1200 characters needed characters left characters exceeded

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

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 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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