从100.64.*.*到100.127.**的ip地址范围？

Question

我想从100.64得到一系列IP地址。到100.127..。

如果我有名为testIp的表和名为PRIVATE_IP_ADDR的列，则记录如下：

1) 100.63.22.55 
2) 100.64.102.558 
3) 100.123.22.12 
4) 100.127.22.55 
5) 100.128.221.55
6) 100.125.355.10
7) 100.64.102.254 

要求：

  1. 2nd part of ip should be from 64 to 127 range
    2. 3rd part of ip should be from 0 to 255 range
    3. 4th part of ip should be from 0 to 255 range 

正则表达式应该选择以下ip地址

3) 100.123.22.12 
4) 100.127.22.55 
7) 100.64.102.254 

到目前为止，我尝试的是：

    select * from testip where REGEXP_LIKE (PRIVATE_IP_ADDR , '^(100\.
(6[4-9]|[7-9][0-9]|1[0-2][0-7])\.
([0-1]?[0-9]?[0-9]|2[0-5][0-5])\.
([0-1]?[0-9]?[0-9]|2[0-5][0-5]))' ) ;

but when i tried the result is 

2) 100.64.102.558 
3) 100.123.22.12 
4) 100.127.22.55 
7) 100.64.102.254 

我不想要唱片

2)100.64.102.558

Answer 1

IPv4地址正则表达式

当然，对于IPv4地址，您可以使用正则表达式，只需选择第二组数字。

在这里，我使用转义字符\d作为数字。正则表达式中的2只表示我们希望第二次出现一组数字。

最后，我将值转换为一个数字，并使用中间操作符。

       SCOTT@db>list
      1  WITH t AS
      2    (SELECT '100.127.22.55' ip_address FROM dual
      3    UNION ALL
      4    SELECT '100.66.22.55' FROM dual
      5    UNION ALL
      6    SELECT '100.123.22.12' FROM dual
      7    UNION ALL
      8      SELECT '100.63.22.55' FROM dual
      9    )
     10  SELECT *
     11  FROM
     12    (SELECT t.ip_address,
     13      regexp_substr(t.ip_address, '^\d+',1,1) first_number,
     14      to_number(regexp_substr(t.ip_address, '\d+',1,2)) second_number
     15    FROM t
     16    )
     17  WHERE 1=1
     18  AND first_number = '100'
     19* AND second_number BETWEEN 66 AND 127
    SCOTT@db>/

    IP_ADDRESS    FIRST_NUMBER                                         SECOND_NUMBER
    ------------- ---------------------------------------------------- -------------
    100.123.22.12 100                                                            123
    100.127.22.55 100                                                            127
    100.66.22.55  100                                                            66

基于op注释的更新

为了采用regexp_like方法(并考虑如何更广泛地描述这个问题)，下面是一个解决方案。在使用量词时需要非常小心，我发现使用锚很有帮助：

SCOTT@db>WITH t AS
  2    (SELECT '100.63.22.55' ip_address FROM dual
  3    UNION ALL
  4    SELECT '100.64.102.558' ip_address FROM dual
  5    UNION ALL
  6    SELECT '100.123.22.12' ip_address FROM dual
  7    UNION ALL
  8    SELECT '100.127.22.55' ip_address FROM dual
  9    UNION ALL
 10    SELECT '100.128.221.55' ip_address FROM dual
 11    UNION ALL
 12    SELECT '100.125.355.10' ip_address FROM dual
 13    UNION ALL
 14    SELECT '100.64.102.254' ip_address FROM dual
 15    )
 16  SELECT *
 17  FROM t
 18  WHERE REGEXP_LIKE (t.ip_address , '^100\.(6[4-9]|[7-9][0-9]|1[0-2][0-7])(\.([1-9][0-9]?|1[0-9][0-9]|2([0-4][0-9]|5[0-5]))){2}$')
 19  /

IP_ADDRESS
--------------
100.123.22.12
100.127.22.55
100.64.102.254