Calculating the number of hosts in a netmask using TSQL

Question

How do I calculate the number of hosts in a netmask using TSQL, WITHOUT using a table.

For example :

IP                   Number of Host
255.255.255.252  =   4 
255.255.254.0    =   512

Answer 1

Here is the SQLFiddel Demo

Below is the sample Query :

select (256-T.I1)*(256-T.I2)*(256-T.I3)*(256-T.I4)
  from (
    select 
      dbo.fnParseString(-1, '.', IP) 'I1',
      dbo.fnParseString(-2, '.', IP) 'I2',
      dbo.fnParseString(-3, '.', IP) 'I3',
      dbo.fnParseString(-4, '.', IP) 'I4'
      from (select '255.255.255.252' as IP
            union
            select '255.255.254.0') T1
       )
as T

Below is the Function :

CREATE FUNCTION dbo.fnParseString
(
    @Section SMALLINT,
    @Delimiter CHAR,
    @Text varchar(100)
)
RETURNS VARCHAR(8000)
AS

BEGIN
DECLARE @NextPos SMALLINT,
        @LastPos SMALLINT,
        @Found SMALLINT

IF @Section > 0
SELECT  @Text = REVERSE(@Text)
SELECT  @NextPos = CHARINDEX(@Delimiter, @Text, 1),
@LastPos = 0,
@Found = 1

WHILE @NextPos > 0 AND ABS(@Section) <> @Found
SELECT  @LastPos = @NextPos,
    @NextPos = CHARINDEX(@Delimiter, @Text, @NextPos + 1),
    @Found = @Found + 1
    RETURN  CASE
    WHEN @Found <> ABS(@Section) OR @Section = 0 THEN NULL

WHEN @Section > 0 THEN REVERSE(SUBSTRING(@Text, @LastPos + 1, CASE WHEN @NextPos = 0 THEN DATALENGTH(@Text) - @LastPos ELSE @NextPos - @LastPos - 1 END))

ELSE SUBSTRING(@Text, @LastPos + 1, CASE WHEN @NextPos = 0 THEN DATALENGTH(@Text) - @LastPos ELSE @NextPos - @LastPos - 1 END)
END

END