SQL 判斷使用者IP 是否符合網段要求

單純使用 SQL 語法來檢查使用者 IP 在是在合格的網段中間，查無資料表示不在合格的清單內。本語法只適合檢查 IP v4以"."分隔的IP。

declare @IPUser as varchar(20)
set @IPUser = '202.39.128.0'

SELECT *
FROM (
 SELECT 
  CAST(SUBSTRING(IPStart, 0, S1) as bigint) * 1000000000
  +CAST(SUBSTRING(IPStart, S1+1, S2-S1-1) as bigint) * 1000000
  +CAST(SUBSTRING(IPStart, S2+1, S3-S2-1) as bigint) * 1000
  +CAST(SUBSTRING(IPStart, S3+1, LEN(IPStart)-S3) as bigint) as IPStart
  ,CAST(SUBSTRING(IPEnd, 0, E1) as bigint) * 1000000000
  +CAST(SUBSTRING(IPEnd, E1+1, E2-E1-1) as bigint) * 1000000
  +CAST(SUBSTRING(IPEnd, E2+1, E3-E2-1) as bigint) * 1000
  +CAST(SUBSTRING(IPEnd, E3+1, LEN(IPEnd)-E3) as bigint) as IPEnd
  ,CAST(SUBSTRING(IPUser, 0, C1) as bigint) * 1000000000
  +CAST(SUBSTRING(IPUser, C1+1, C2-C1-1) as bigint) * 1000000
  +CAST(SUBSTRING(IPUser, C2+1, C3-C2-1) as bigint) * 1000
  +CAST(SUBSTRING(IPUser, C3+1, LEN(IPUser)-C3) as bigint) as IPUser
 FROM ( 
 SELECT
    IPUser
    ,CHARINDEX('.',IPUser, 0) as C1
    ,CHARINDEX('.',IPUser, CHARINDEX('.',IPUser, 0) + 1) as C2
    ,CHARINDEX('.',IPUser, CHARINDEX('.',IPUser, CHARINDEX('.',IPUser, 0) + 1) + 1) as C3
    ,Unit
    ,[ID]
    ,[IssueDate]
    ,[IPStart]
    ,[IPEnd]
    ,CHARINDEX('.',IPStart, 0) as S1
    ,CHARINDEX('.',IPStart, CHARINDEX('.',IPStart, 0) + 1) as S2
    ,CHARINDEX('.',IPStart, CHARINDEX('.',IPStart, CHARINDEX('.',IPStart, 0) + 1) + 1) as S3
    ,CHARINDEX('.',IPEnd, 0) as E1
    ,CHARINDEX('.',IPEnd, CHARINDEX('.',IPEnd, 0) + 1) as E2
    ,CHARINDEX('.',IPEnd, CHARINDEX('.',IPEnd, CHARINDEX('.',IPEnd, 0) + 1) + 1) as E3
   FROM .[dbo].[IP_Scope]
   LEFT JOIN 
  (SELECT @IPUser as IPUser ) U 
  ON 1=1 
 ) A
) B
Where IPUser between IPStart and IPEnd