Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lenovo27
Starting Member
15 Posts |
 Posted - 2009-10-27 : 16:48:19
|
| Hello,I am trying to convert binary data in a table column into strings. I have tried numerous methods, none of which have been successful, hence the reason I am here.I have tried SELECT sys.fn_sqlvarbasetostr(column_name) From table_name...but it just displays the same binary data. I even tried changing the data type in the table for that column, no luck. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-10-27 : 17:17:21
|
That sys function does in fact convert the binary value to a string representation of the binary value. See, this shows that you can concatenate a string to its result:SELECT 'this is a string: ' + sys.fn_sqlvarbasetostr(0x1230)Give an example of what output you expect given a specific binary value.EDIT:perhaps you looking for a direct conversion between varchar and binary:select convert(varbinary, 'This is an encoded string') ,convert(varchar, 0x5468697320697320616E20656E636F64656420737472696E67)output:-------------------------------------------------------------- ------------------------------0x5468697320697320616E20656E636F64656420737472696E67 This is an encoded string Be One with the OptimizerTG |
 |
|
|
lenovo27
Starting Member
15 Posts |
Posted - 2009-10-27 : 17:50:05
|
quote:
Originally posted by TG
That sys function does in fact convert the binary value to a string representation of the binary value. See, this shows that you can concatenate a string to its result:SELECT 'this is a string: ' + sys.fn_sqlvarbasetostr(0x1230)Give an example of what output you expect given a specific binary value.EDIT:perhaps you looking for a direct conversion between varchar and binary:select convert(varbinary, 'This is an encoded string') ,convert(varchar, 0x5468697320697320616E20656E636F64656420737472696E67)output:-------------------------------------------------------------- ------------------------------0x5468697320697320616E20656E636F64656420737472696E67 This is an encoded string Be One with the OptimizerTG
Yes, I get the output, you are correct, I am no longer looking at a column full of <binary data>, HOWEVER; the data does me little good in that format. I want to be able to see what the data means, so yes, a direct conversion would be what I am looking for. |
|
|
|
lenovo27
Starting Member
15 Posts |
Posted - 2009-11-09 : 19:19:38
|
| Here is an example:Convert this to something that makes sense:0x002d46d84344d9d14f7d87cd75322af19e69 |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-11-10 : 10:40:59
|
| "Makes sense" in what context? Do you have any idea what the value is used for? Are the values stored in a column - what's the column name? Be One with the OptimizerTG |
|
|
|
learntsql
524 Posts |
Posted - 2009-11-10 : 23:42:41
|
| How Can i Decrypt the Password column of binary datatype into plain text,any methods. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2009-11-11 : 04:07:13
|
quote:
Originally posted by TG
"Makes sense" in what context? Do you have any idea what the value is used for? Are the values stored in a column - what's the column name?
I'd add to that: What language and what script is it likely to be in? Do you have any clues as to how it's encoded -- e.g. Unicode as UTF-16, as UTF-8, BIG5, EBCDIC? Might there be endian issues? And what makes you think it's a string rather than, say, a floating point number, the start of an image header, or some sort of generated unique ID?I know it was someone else piggy-backing onto this topic, but as Tara says, if it's encrypted rather than just in some obscure character encoding it's unlikely you'll get any help. |
|
|
|
|
|
|
|
|
Convering binary values to strings