Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
IP Address conversions in SQL
CREATE FUNCTION [StringIpToBin]
(
@StringIp VARCHAR(15)
)
RETURNS BINARY(4)
AS
BEGIN
RETURN
CAST(CAST(PARSENAME(@StringIp,4) AS INT) AS BINARY(1)) +
CAST(CAST(PARSENAME(@StringIp,3) AS INT) AS BINARY(1)) +
CAST(CAST(PARSENAME(@StringIp,2) AS INT) AS BINARY(1)) +
CAST(CAST(PARSENAME(@StringIp,1) AS INT) AS BINARY(1))
END
GO
CREATE FUNCTION [BinaryIpToString]
(
@BinaryIP BINARY(4)
)
RETURNS VARCHAR(15)
AS
BEGIN
DECLARE @IntIp1 INT
DECLARE @IntIp2 INT
DECLARE @IntIp3 INT
DECLARE @IntIp4 INT
SET @IntIp1 = (@BinaryIP/16777216)
SET @IntIp2 = (@BinaryIP-(@IntIp1*16777216))/65536
SET @IntIp3 = (@BinaryIP-(@IntIp1*16777216)-(@IntIp2*65536))/256
SET @IntIp4 = (@BinaryIP-(@IntIp1*16777216)-(@IntIp2*65536)-(@IntIp3*256))
RETURN
CAST(@IntIP1 AS VARCHAR(3)) + '.' +
CAST(@IntIP2 AS VARCHAR(3)) + '.' +
CAST(@IntIP3 AS VARCHAR(3)) + '.' +
CAST(@IntIP4 AS VARCHAR(3))
END
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.