Query with basic calculation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query with basic calculation

Author

Topic

sqln00b9
Starting Member

8 Posts

Posted - 2011-11-05 : 00:01:31

Hello,
I have two fields, ipAddress and NumberEquivalent

ipaddress has data like 192.343.34.34

whereas number equivalent needs to be updated using this formula:
[A x (256*256*256)] + [B x (256*256)] + [C x 256] + [D]

i.e.
[192*(256*256*256)] + [343 x (256*256)] + [34 x 256] + [34]

----
Can an expert help me write an update statement that can do this with a single query?

vikki.seth
Yak Posting Veteran

66 Posts

Posted - 2011-11-05 : 12:52:59

[code]Declare @IP as varchar(15)
Declare @a as int
Declare @b as int
Declare @c as int
Declare @d as int
Declare @pos1 as int
Declare @pos2 as int

set @IP = '15.2.10.5'
set @pos1 =1
set @pos2 = CHARINDEX ('.',@IP , @pos1+1 )
set @a = SUBSTRING (@ip,@pos1, @pos2-@pos1)
set @pos1 =@pos2 +1
set @pos2 = CHARINDEX ('.',@IP , @pos1 +1)
set @b = SUBSTRING (@ip,@pos1, @pos2-@pos1)
set @pos1 =@pos2 +1
set @pos2 = CHARINDEX ('.',@IP , @pos1 +1)
set @c = SUBSTRING (@ip,@pos1, @pos2-@pos1)
set @pos1 =@pos2 +1
set @pos2 = LEN(@ip)
set @d = SUBSTRING (@ip,@pos1, @pos2-@pos1+1)
[/code]

Based on this logic, you can write a query to update the statement.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-05 : 13:20:10

[code]
UPDATE table
SET NumberEquivalent= PARSENAME(ipAddress,4) * POWER(256,3) + PARSENAME(ipAddress,3) * POWER(256,2) + PARSENAME(ipAddress,2) * 256 + PARSENAME(ipAddress,4)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources