CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Luka
Luka

Reputation: 189

SQL Server : update table Convert Int To IP String

I have problem with my example table1

Name                     IP                         OS        Version
----------------------------------------------------------------------
Jonh        0x20010db80a0b12f00000000000000001      Os         8.184
BoB         0x20010db80a0b12f00000000000000092      WIN        10.844
Alice       0x20010db80a0b12f00000000000000051     Linux       Ubuntu

And I need convert or update the IP column (varbinary) to string. I try to update whole table like this:

declare @IP as int  
set @IP =  * from 

select * 
from TABLE1,IP

UPDATE [dbo].[TABLE1]
SET IP = CONVERT(VARCHAR(3), (@IP/16777216) & 255) + '.'
         + CONVERT(VARCHAR(3), (@IP/65536) & 255) + '.'
         + CONVERT(VARCHAR(3), (@IP/256) & 255) + '.'
         + CONVERT(VARCHAR(3), @IP & 255) AS IP

But it does not work. This type of declaration work only for one row/IP but this not update whole table.

My original table has over 1000 rows.

Upvotes: 0

Views: 1234

Answers (2)

Squirrel
Squirrel

Reputation: 24803

Should be just simply like this

UPDATE [dbo].[TABLE1]
 SET IP =     
  convert(varchar(3), (IP/16777216) & 255) + '.'
    + convert(varchar(3), (IP/65536) & 255) + '.'
    + convert(varchar(3), (IP/256) & 255) + '.'
    + convert(varchar(3), IP & 255)

EDIT : convert to varbinary then to bigint before division and then convert result to varchar 

CONVERT(VARCHAR(3), CONVERT(BIGINT, CONVERT(VARBINARY, IP)) / 16777216 & 255)

Upvotes: 0

DhruvJoshi
DhruvJoshi

Reputation: 17146

If your logic for conversion is correct please try the code below

  /*  create table table1
([name] varchar(100), [ip] varCHAR(100),[os] varchar(100),[version] varchar(100));
insert into table1 values
('Jonh',        '0x20010db80a0b12f00000000000000001'      ,'Os',         '8.184')
,('BoB',         '0x20010db80a0b12f00000000000000092'      ,'WIN',        '10.844')
,('Alice',    '0x20010db80a0b12f00000000000000051'     ,'Linux',       'Ubuntu');*/

select 
   Name,
   IP= 
    convert(varchar(3), (cast(IP AS VARBINARY(100))/16777216) & 255) + '.'

    + convert(varchar(3), (cast(IP AS VARBINARY(100))/65536) & 255) + '.'

    + convert(varchar(3), (cast(IP AS VARBINARY(100))/256) & 255) + '.'

    + convert(varchar(3), cast(IP AS VARBINARY(100)) & 255),
  OS,
  Version
from  TABLE1

See demo link

Upvotes: 1

Related Questions