CODEWITHSUNDEEP
sql-server
Michael Rosello
Michael Rosello

Reputation: 153

Convert Hex to IP inline query

Problem is i want to write an inline query to convert hexcode to ip format but I am getting the wrong result

I've am trying to write a query that converts the hex to ip but the result I am getting is wrong

SELECT DNS_SERVER1,
        CONVERT(VARCHAR,
         c.DNS_SERVER1/16777216)+'.'+CONVERT(VARCHAR, c.DNS_SERVER1/65535 % 256)+'.'+CONVERT(VARCHAR, c.DNS_SERVER1/256 % 256)+'.'+CONVERT(VARCHAR, c.DNS_SERVER1 % 256) as DNS_SERVER1_CONVERTED
FROM SEPMAdmin.SEM_COMPUTER AS c

Using the query above and an example data of "86921E34", I should get a "134.146.30.52" but what I am getting is "-121.-110.-225.-204"

Upvotes: 1

Views: 1373

Answers (1)

Jason A. Long
Jason A. Long

Reputation: 4442

Try it like this...

DECLARE @hex_val binary(4) = 0x86921E34;

SELECT 
    ip_address = CONCAT(
            CONVERT(int, SUBSTRING(@hex_val, 1,1)), '.',
            CONVERT(int, SUBSTRING(@hex_val, 2,1)), '.',
            CONVERT(int, SUBSTRING(@hex_val, 3,1)), '.',
            CONVERT(int, SUBSTRING(@hex_val, 4,1))
            );

Result...

ip_address
---------------------------------------------------
134.146.30.52

Upvotes: 1

Related Questions