How to convert ip string to INT in ms sql ? & How to convert INT ip to string ?

string to int :

USE [EMOSENT_LOG_PERF]
GO
/****** Object: UserDefinedFunction [dbo].[ipStringToInt] Script Date: 12/08/2014 11:34:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ipStringToInt]
(
@ip CHAR(15)
)
RETURNS INT
AS
BEGIN
DECLARE @rv INT,
@o1 INT,
@o2 INT,
@o3 INT,
@o4 INT,
@base INT

SELECT
@o1 = CONVERT(INT, PARSENAME(@ip, 4)),
@o2 = CONVERT(INT, PARSENAME(@ip, 3)),
@o3 = CONVERT(INT, PARSENAME(@ip, 2)),
@o4 = CONVERT(INT, PARSENAME(@ip, 1))

IF (@o1 BETWEEN 0 AND 255)
AND (@o2 BETWEEN 0 AND 255)
AND (@o3 BETWEEN 0 AND 255)
AND (@o4 BETWEEN 0 AND 255)
BEGIN
SELECT @base = CASE
WHEN @o1 < 128 THEN
(@o1 * 16777216)
ELSE
(-1*(256 - @o1)) * 16777216
END

SET @rv = @base +
(@o2 * 65536) +
(@o3 * 256) +
(@o4)
END
ELSE
SET @rv = -1
RETURN @rv
END

from int to string:

CREATE FUNCTION dbo.ipIntToString
(
@ip INT
)
RETURNS CHAR(15)
AS
BEGIN
DECLARE @o1 INT,
@o2 INT,
@o3 INT,
@o4 INT

IF ABS(@ip) > 2147483647
RETURN '255.255.255.255'

SET @o1 = @ip / 16777216

IF @o1 = 0
SELECT @o1 = 255, @ip = @ip + 16777216

ELSE IF @o1 < 0
BEGIN
IF @ip % 16777216 = 0
SET @o1 = @o1 + 256
ELSE
BEGIN
SET @o1 = @o1 + 255
IF @o1 = 128
SET @ip = @ip + 2147483648
ELSE
SET @ip = @ip + (16777216 * (256 - @o1))
END
END
ELSE
BEGIN
SET @ip = @ip - (16777216 * @o1)
END

SET @ip = @ip % 16777216
SET @o2 = @ip / 65536
SET @ip = @ip % 65536
SET @o3 = @ip / 256
SET @ip = @ip % 256
SET @o4 = @ip

RETURN
CONVERT(VARCHAR(4), @o1) + '.' +
CONVERT(VARCHAR(4), @o2) + '.' +
CONVERT(VARCHAR(4), @o3) + '.' +
CONVERT(VARCHAR(4), @o4)
END

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s