CODEWITHSUNDEEP
sqlsql-servertype-conversion
Gerald Patriowski
Gerald Patriowski

Reputation: 111

Binary Type to Actual Binary

So, I know this has to be a dumb question, but I can't seem to find a good answer. When I store a binary value in a binary column in SQL server, it's displayed in its hexadecimal format. So, below, the binary number is on the right, and the hexadecimal representation as it's stored in 'Binary' format in the table is on the left.

Hex     Binary
0x0000  0
0x0001  1
0x000A  10
0x000B  11
0x0064  100
0x0065  101
0x006E  110
0x006F  111
0x03E8  1000
0x03E9  1001
0x03F2  1010
0x03F3  1011
0x044C  1100

Dumb question of the day, how do I get it back to its original binary format? It seems it may just be best to store it as a different type.

Upvotes: 1

Views: 126

Answers (1)

Backs
Backs

Reputation: 24903

it's actually stored in its hexadecimal format

It's wrong. MSSQL stores binary data as binary. Hexadecimal format is jus a representation of binary value. For convertion to any N-based representation (binary also) you can write a SP or get it from this page:

CREATE FUNCTION ConvertToBase  
(  
    @value AS BIGINT,  
    @base AS INT  
) RETURNS VARCHAR(MAX) AS BEGIN  

    -- some variables  
    DECLARE @characters CHAR(36),  
            @result VARCHAR(MAX);  

    -- the encoding string and the default result  
    SELECT @characters = '0123456789abcdefghijklmnopqrstuvwxyz',  
           @result = '';  

    -- make sure it's something we can encode.  you can't have  
    -- base 1, but if we extended the length of our @character  
    -- string, we could have greater than base 36  
    IF @value < 0 OR @base < 2 OR @base > 36 RETURN NULL;  

    -- until the value is completely converted, get the modulus  
    -- of the value and prepend it to the result string.  then  
    -- devide the value by the base and truncate the remainder  
    WHILE @value > 0  
        SELECT @result = SUBSTRING(@characters, @value % @base + 1, 1) + @result,  
               @value = @value / @base;  

    -- return our results  
    RETURN @result;

Upvotes: 1

Related Questions