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