Removing padded zeroes in Hexadecimal column

Question

I have a database in TSQL that contains columns in hexadecimal. However these values are padded with zeroes to fill the column.

I need to remove these zeroes just so I have the hexadecimal string. The problem is I cannot just remove pairs of zeroes as the string may have zeroes in the middle.

I have a feeling I need to convert it to say decimal and then back or something, but everything I try yields strange results.

The column in the database look like this:

1EBC67E6000000 6B1FE9C7830000 C700DBBF000000

The values are different lengths and all padded with pairs of zeroes.

Any ideas would be good!

It seems strange to pad the hexadecimal value with zeroes in the end. This makes the value change, if padding were in the beginning of the string instead the value remains the same. Compare 000111 and 111000 (decimal) the second one is not equal to 111. How can you be sure that the value doesnt end with zeros? — Johan
– Johan, Commented Sep 15, 2011 at 11:14
Huh, that's tricky - if values have different lengths there's no way telling if it should be C700DBBF or C700DBBF0. — Filburt
– Filburt, Commented Sep 15, 2011 at 11:16
Are you looking to permanently remove these zeroes? If so what is the datatype of the column? If it is binary rather than varbinary these are there to pad the length and can't be removed — Martin Smith
– Martin Smith, Commented Sep 15, 2011 at 11:39

matmot · Accepted Answer · 2011-09-15 11:31:58Z

7

Maybe this will be good for you:

Select replace(rtrim(replace('C700DBBF000000','0',' ')),' ','0');

edited Sep 15, 2011 at 11:31

answered Sep 15, 2011 at 11:19

matmot

1631 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

heximal Over a year ago

it will remove zeros in the middle

matmot Over a year ago

no I replace 0 for space ' ' and I remove only this at the end. After that i replace spaces in the midle of string again to 0. Try this code in managment studio or in the database. It's good.

Martin Smith Over a year ago

This does need you to convert the varbinary to a string representation first then back (code not supplied here!)

Collectives™ on Stack Overflow

Removing padded zeroes in Hexadecimal column

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related