RE: Parsing Binary Data to Varchar

[Catadmin]

Sorry, All.

I started a new thread because I can't reply to the old one. Something in the post is screwing up the reply functions in my browser. Even refreshing my browser didn't help.

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

"0xFFFFFFFFFF303030303030303030312020202020202020202020
20202020200A004E6F746" <Catadmin cut off string>

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

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
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[Recht]

It makes sense, I haven't used PATIndex or CharIndex before, but it seems like now is as good a time as any to learn them!

Thank you for the help. Just in advance, have you tried using CAST on a variable?

 

[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

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???