使用TSQL计算网络掩码中的主机数

问题描述

如何在不使用表的情况下使用TSQL计算网络掩码中的主机数.

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

例如:

IP                   Number of Host
255.255.255.252  =   4
255.255.254.0    =   512

推荐答案

这是SQLFiddel演示

Here is the SQLFiddel Demo

下面是示例查询:

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

以下是功能:

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

这篇关于使用TSQL计算网络掩码中的主机数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！