Two things on the PARSENAME conversion routines:

First, T-SQL scalar functions have no place in this world - the routines would be much more happily expressed in an in-line table-valued function.

Second, PARSENAME is non-deterministic, despite what Books Online says. See https://connect.microsoft.com/SQLServer/feedback/details/488058/parsename-incorrectly-documented-as-deterministic

A deterministic iTVF IP-to-BIGINT function:

CREATE FUNCTION [dbo].[itfn_IPv4_Octets]

(@IP NVARCHAR(15))

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN

SELECT octet1 = CASE WHEN LEN(@IP) - LEN(REPLACE(@IP, N'.', N'')) = 3 THEN ISNULL(CONVERT(NVARCHAR(3), SUBSTRING(@IP, 1, (CHARINDEX(N'.', @IP, 1)) - 1)), N'') ELSE N'' END,

octet2 = CASE WHEN LEN(@IP) - LEN(REPLACE(@IP, N'.', N'')) = 3 THEN ISNULL(CONVERT(NVARCHAR(3), SUBSTRING(@IP, (CHARINDEX(N'.', @IP, 1)) + 1, (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, 1)) + 1)) - (CHARINDEX(N'.', @IP, 1)) - 1)), N'') ELSE N'' END,

octet3 = CASE WHEN LEN(@IP) - LEN(REPLACE(@IP, N'.', N'')) = 3 THEN ISNULL(CONVERT(NVARCHAR(3), SUBSTRING(@IP, (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, 1)) + 1)) + 1, (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, 1)) + 1)) + 1)) - (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, 1)) + 1)) - 1)), N'') ELSE N'' END,

octet4 = CASE WHEN LEN(@IP) - LEN(REPLACE(@IP, N'.', N'')) = 3 THEN ISNULL(CONVERT(NVARCHAR(3), SUBSTRING(@IP, (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, 1)) + 1)) + 1)) + 1, LEN(@IP) - (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, 1)) + 1)) + 1)))), N'') ELSE N'' END;

That happens to split the IP address into octets, so converting further into a BINARY(4) - as suggested earlier - is not too hard:

SELECT CONVERT

(

BINARY(4),

CHAR(FN.octet1) +

CHAR(FN.octet2) +

CHAR(FN.octet3) +

CHAR(FN.octet4)

)

FROM [dbo].[itfn_IPv4_Octets] ('162.74.5.51') FN;

Conversion from BIGINT to dotted-octets:

CREATE FUNCTION dbo.IntToIP

(@IP BIGINT)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN

SELECT ip_address =

CONVERT(VARCHAR(3), ((@IP & 0xFF000000) / 256 / 256 / 256)) + '.' +

CONVERT(VARCHAR(3), ((@IP & 0xFF0000) / 256 / 256)) + '.' +

CONVERT(VARCHAR(3), ((@IP & 0xFF00) / 256)) + '.' +

CONVERT(VARCHAR(3), (@IP & 0xFF));

Notwithstanding some icky implicit conversions, that function will decode the BINARY(4) representation too.

Now those functions might look a bit hairy, but being deterministic, they're fully foldable, so the estimated execution plans for the following statements:

SELECT * FROM dbo.itfn_IPv4_Octets(N'162.74.5.51');

SELECT * FROM dbo.IntToIP(0xA24A0533);

...are both Constant Scans, with the answers fully listed in the operator - all the maths is done at compilation time, not execution time...which I think is remarkable.