| Author |
Message |
eberhard
Newbie
Joined: 21 Sep 2011
Online Status: Offline
Posts: 7
|
  
Topic: Convert Binary field
Posted: 14 Nov 2012 at 6:02am |
|
I have a Binary field that i need to convert to display on a report. I was trying to use the "CAST" command, however either it doesn't work or my syntax is wrong ... help
|
 IP Logged |
|
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 14 Nov 2012 at 7:14am |
what's it supposed to be...before and after is 10 supposed to = 2 or is something else?
|
| IP Logged |
|
eberhard
Newbie
Joined: 21 Sep 2011
Online Status: Offline
Posts: 7
|
Posted: 14 Nov 2012 at 8:52am |
it is a string of text up to 254 characters
the statement that i was trying is
cast(cast(fieldname as varbinary(254)) as varchar(254))
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 14 Nov 2012 at 9:26am |
do a quick google search, basically it's not looking good. basically the data is a bunch of hexadecimal values, which are probably meaningless to us. Even if convert to ASCII, they may still be meaningless. I searched for varbinary to varchar conversion
|
| IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 14 Nov 2012 at 11:13am |
|
If they are truly ascii characters stored as hex, then I did a conversion (some years ago). It would take a while to find the formula. But I will look for it if that is the case.
|
| IP Logged |
|
eberhard
Newbie
Joined: 21 Sep 2011
Online Status: Offline
Posts: 7
|
Posted: 15 Nov 2012 at 2:20am |
|
yes they are ASCII characters they just need to be brought back from Hex to display on a report.
|
| IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 15 Nov 2012 at 9:59am |
|
Here is a formula that should work. The stringvar text has the hex that needs to be converted. The stringvar newtext is the converted data. There may be a better way, but this was quick and easy.
stringvar text := "430D44";
numbervar converted;
stringvar temp1;
stringvar temp2;
numbervar i;
stringvar newtext;
for i := 1 to len(text) step 2
do
(
temp1 := mid(text,i,1);
temp2 := mid(text,i+1,1);
if isnumeric(temp1) then
converted := tonumber(temp1)*16
else
converted := switch(temp1 = "A",10,temp1 = "B",11,temp1 = "C",12,temp1 = "D",13,temp1 = "E",14, temp1 = "F",15)*16;
if isnumeric(temp2) then
converted := converted + tonumber(temp2)
else
converted := converted + switch(temp2 = "A",10,temp2 = "B",11,temp2 = "C",12,temp2 = "D",13,temp2 = "E",14, temp2 = "F",15);
newtext := newtext + chr(converted);
);
newtext
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 16 Nov 2012 at 8:08am |
one of the websites I looked at had SQL to convert the values (I thought)...might make life easier. just thought I would pass it along
|
| IP Logged |
|
|
|
