Reading Binary Data

Question

Post reply

Reading Binary Data

David Tooker

Right there with Babe

Points: 746

October 3, 2006 at 9:09 am

#114717

Hi,

I have a customer database that has several fields that are in SQL with the data type of Binary(100 chrs). The data is shown with the client app as normal text. I want to read the text values in these fields but cant figure out how to see the data as ASCII. One of the fields is a normal part description field. Dont know why the vendor set that as the field data type. I have tried cast and convert but only a single number is shown in the result set. Any help would be appreciated.

This is a sample of the binary data for the PartNbr field

0x3800370031003300390039002D00300030003100300035002E003200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

This should be a part number like 850306-00803. They also have the Unit of Measure and many other fields as Binary. Here is an example of the Unit of Measure field that should be = Ea or Each

0x45004100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

Thanks in advance

David

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65141 More actions · Answer 1

Try

CAST([columnname] as nvarchar(100))

Far away is close at hand in the images of elsewhere.
Anon.

David Tooker Right there with Babe Points: 746 More actions · Answer 2

David Tooker

Right there with Babe

Points: 746

October 3, 2006 at 10:33 am

#663901

Works Great! Thanks for the advice.

David

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 3

And they say understanding underlying data structures is no longer important... the '00' unicode country code in these examples sticks out like a sore thumb to anyone who has seen it before... Nice job, David.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Burrows SSC Guru Points: 65141 More actions · Answer 4

...the '00' unicode country code in these examples sticks out like a sore thumb...

Thanks Jeff

It's what you get for working in hex for so long

Must be all that work I did in Assembler in the good old days

Far away is close at hand in the images of elsewhere.
Anon.

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 5

You had an Assembler? Lucky you... Much of the code I wrote in the "good old days" was hand-assembled machine language... now THAT was fun Now adays, I don't think they even teach numbering systems in college. I interviewed 4 folks with Master's Degrees and 1 PHD... all of them in Computer Science... none of them could tell me what 2⁰ or 14₁₆ was. Two of them didn't even know what 2³ was. Forget any Boolean Algebra... guess the old T-Shirt was right... "There are 10 types of people, those who understand binary and those that don't."

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lowell SSC Guru Points: 323518 More actions · Answer 6

After I Saw Davids answer, I gave my head a good slap because I knew better; I had even started writing a function when David posted the right answer.;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!