How to remove trailing zeroes from a binary field in SQL Server 2012

Question

I am facing a issue while inserting the binary value to a binary column in SQL Server 2012.

I am able to insert the value to binary column but when I query the inserted binary value, I could see the unwanted trailing zeroes in the binary value inserted.

For example, -- creating the table blob with binary field

create table blob (id int, adata binary(256)) -- inserting binary value 0x730000000000000000000000000000000000000000000000000000000000 into the table insert into blob values(3,0x730000000000000000000000000000000000000000000000000000000000) -- and then when i select it, select * from blob 3 **0x73000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000**

The queried value is highlighted in bold.

I don't require this column to have these unwanted trailing zeroes.

Could anyone please help me in removing these zeroes from the binary field in the table?

NOTE: We are not supposed to change the table structure.

Thanks in advance.

Raghav Chandra Shetty

Joel Coehoorn · Accepted Answer · 2014-09-23 16:27:54Z

I know you said you're not allowed to change the table structure, but if you don't want the trailing zeros, you really should be using a varbinary field instead. With just a binary column, those zeros are and will always be part of the data in the table, and there's no difference between, say, 0x7300 and 0x7300000000.

Collectives™ on Stack Overflow

How to remove trailing zeroes from a binary field in SQL Server 2012

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related