Parsing BINARY Data to VCHAR

[Recht]

I need to parse the following data which is stored in a BINARY format as VARCHAR.

0xFFFFFFFFFF30303030303030303031202020202020202020202020202020200A004E6F7469667920706179726F6C6C206F66206E657720656D706C6F7965652020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020

Normally I would use the CAST to display the data as VARCHAR. For example:

select CAST (FixedColumnData as varchar)
from orphan_0x9386909

The problem I am running into is when the CAST hits a 00 in the binary data it stop (treats it as a terminator). So a CAST of the above returns:

ÿÿÿÿÿ0000000001

Does anyone know of a way to skip certain data using CAST (i.e. filter out the 00 data) or a way to start a CAST at a certain offset within the row data (I.E. after the 00 in the binary string)?

I tried using CONVERT but it seems to suffer the same limitation.

I also tried using SUBSTRING:

select CAST ((SELECT SUBSTRING ( fixedcolumndata , 1 , 32 ) + SUBSTRING ( fixedcolumndata , 34 , 141 ))as varchar)
from orphan_0x9386909

with no luck

Thanks in advance.

 

[NoCoolHandle]

Interseting...

try

declare @b varbinary(8000)
set @b = 0xFFFFFFFFFF30303030303030303031202020202020202020202020202020200A004E6F7469667920706179726F6C6C206F66206E657720656D706C6F7965652020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020
select cast(@b as nVarchar(4000))

 

[NoCoolHandle]

Even More Curious...

declare @b varbinary(8000)
set @b = 0xFFFFFFFFFF30303030303030303031202020202020202020202020202020200A004E6F7469667920706179726F6C6C206F66206E657720656D706C6F7965652020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020
Declare @v varchar(4000)
set @v = cast(@b as Varchar(4000))
select @v
Select cast(@v as varbinary(4000))

So is QA just truncating something?

What happens if you do the same thing with a client like vb or dotnot

 

[Recht]

QA truncates the data at the 00 for me, it appears to be a terminator internally.

Catadmin suggested:

Try using PATIndex or CharIndex to locate your 00 value, then use Substring() to pull the first half of the string into one variable and the second half of the string into a second variable. Then set a third variable as the concatenation of String1 + String2 and Convert it to your varchar.

Make sense?


Catadmin - MCDBA, MCSA

I haven't had a chance to try it yet...

Thank you for the suggestions, I will try them all shortly, firefighting right now!

 

[NoCoolHandle]

Did you try the last script?

You might get a big surprise!

 

[NoCoolHandle]

For that matter look at.

declare @b varbinary(8000)
set @b = 0xFFFFFFFFFF30303030303030303031202020202020202020202020202020200A004E6F7469667920706179726F6C6C206F66206E657720656D706C6F7965652020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020
Declare @v varchar(4000)
set @v = cast(@b as Varchar(4000))
select len(@v),@v
Select cast(@v as varbinary(4000))

Returns
63
ÿÿÿÿÿ0000000001

Surprisingly a block of characters that only counts up to 15 if you look at it is 63 long??

Again Have you tried this with some other app than QA?

 

[Recht]

I don't get a good result with this, I must be missing something:

declare @b varbinary(8000)
set @b = 0xFFFFFFFFFF30303030303030303031...
select cast(@b as nVarchar(4000))

The data as it sits in the table is already set as a 175 width binary column. (Column Name=FixedColumnData, DataType=Binary, Size=175) It seems like when I try this, it is trying to convert the binary data to binary again, then convert to varchar?


With the second example I get the following:

ÿÿÿÿÿ0000000001

Again, it appears when it hits the 00 in the data stream, it terminates.

 

[Recht]

Please correct me if this is wrong:

Because the data is stored in binary, it takes 2 characters for each ASCII letter.

In 0xFFFFFFFFFF30303030303030303031202020202020202020202020202020200A00

The 0x is ignored as it just indicates the data is in Hex.

The FF is translated as ÿ
the 30 is translated as 0
the 31 is translated as 1
and the 20 is translated as a space
then there should be a funny character for the 0A

Then it hits the 00 and stops!

 

[Recht]

Good reply from Catadmin

I've used CAST and CONVERT to set a variable, not on the variable itself.

Set @Var = Cast(MyValue as DataType)
and
Set @Var = Convert(Datatype, MyValue)

But, until you mentioned it, I had never tried it directly. I just tested in in QA and didn't seem to have a problem.

Here's what I tested on


CODE

Declare @Var float
Set @Var = 123.5322
Select @Var
Select Cast(@Var as int)


I also reversed it, declare @Var as Int and Casting as Float. QA seemed fine with it.

I don't know what is wrong with my browser that I can't see the reply window on your original post. It's driving me nuts.


Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???"

 

[NoCoolHandle]

I think the key is to use nvarchar.

I also think all your data has been converted to char, but becuase of the character set, you just can't see the values.

The conversion of your binary value to varchar produces the following results (see below). What I find intertesting is that the NULL (marked with red) character does'nt prevent you from converting it back, but it does seem to limit amost every client I have played with's abiltity to show the characters after it.

Following string of ascii characters
255
255
255
255
255
48
48
48
48
48
48
48
48
48
49
32
32
32
32
32
32
32
32
32
32
32
32
32
32
32
32
10
[red]0[/red]
78
111
116
105
102
121
32
112
97
121
114
111
108
108
32
111
102
32
110
101
119
32
101
109
112
108
111
121
101


Rob

 

[Recht]

What I find intertesting is that the NULL (marked with red) character does'nt prevent you from converting it back, but it does seem to limit amost every client I have played with's abiltity to show the characters after it."

You hit the nail on the head. Thats what I was trying to find a way to skip in my original post.

 

[NoCoolHandle]

PLEASE RUN MY EXAMPLE IN QA AND LOOK AT THE RESULTS


And Sorry the previous posts results were produced with the following script

set nocount on
declare @b varbinary(8000)
set @b = 0xFFFFFFFFFF30303030303030303031202020202020202020202020202020200A004E6F7469667920706179726F6C6C206F66206E657720656D706C6F7965652020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020
Declare @v varchar(4000)
set @v = cast(@b as Varchar(4000))
select len(@v),@v
Select cast(@v as varbinary(4000))
Declare @i int, @curi int
Declare @asciiString varchar(8000)
set @i = 1
set @asciiString = ''
while @i < len(@v)
	begin
select 	ascii(substring(@v,@i,1))
--select @asciiString = @asciiString + cast(ascii(substring(@v,@i,1)) as char(3)) + ';'
		set @i = @i+1
	end
select @asciiString

And I think you are missing the important example from earlier..

When your value was converted from binary to char and back again. It converted it back correctly. All the missing values where in the string.

It makes no sence to break up your line on a null character.

All your data is in the varchar field! you just arn't seeing it because of the null character.

set nocount on
declare @b varbinary(8000)
set @b = 0xFFFFFFFFFF30303030303030303031202020202020202020202020202020200A004E6F7469667920706179726F6C6C206F66206E657720656D706C6F7965652020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020
Declare @v varchar(4000)
set @v = cast(@b as Varchar(4000))
select len(@v),@v
Select cast(@v as varbinary(4000))
Declare @i int, @curi int
Declare @asciiString varchar(8000)
set @i = 1
set @asciiString = ''
while @i < len(@v)
	begin
select 	ascii(substring(@v,@i,1))
--select @asciiString = @asciiString + cast(ascii(substring(@v,@i,1)) as char(3)) + ';'
		set @i = @i+1
	end
select @asciiString

 

[NoCoolHandle]

OK - What original post! I was wondering about the reference to catadmin without seeing cats response.

So don't bring that up without a reference to is qid.

Also
USE NVARCHAR and you wont have a problem

Sorry to have waisted my time helping you to this point.

 

[Recht]

Think there might be some lag in the posting

Catadmin couldnt reply to my post (this is the original post) and so created a new post here:

http://www.tek-tips.com/viewthread.cfm?qid=1096304&page=1

Thank you for the assistance so far, I will try your last example in a short while and try converting to nvarchar.

I am sorry you feel like you have wasted your time in replying to this post.

 

[Recht]

I am not sure what is up with the conversion to nvarchar, I see data like:

????????†††††††???????????????

I should be seeing data like:

"Notify payroll of new employee"

I must be doing something wrong still!

 

[Recht]

%4E%6F%74%69%66%79%20%70%61%79%72%6F%6C%6C%20%6F%66%20%6E%65%77%20%65%6D%70%6C%6F%79%65%65

Notify payroll of new employee