CODEWITHSUNDEEP
sqlregexoracle-databaseplsql
prashant1988
prashant1988

Reputation: 282

regular expression to get all numbers except in a range

I want to get a list ip addresses whose last 3 digits don't fall in the range 0 to 200. I tried using REGEXP_LIKE as below. But it is giving me wrong result.

 select * from ip_data
 where REGEXP_LIKE(substr(ip_address,instr(ip_address,'.',1,3)+1),'[^0-200]');

It seems to have matched each digits with 0-200 numbers individually.

I want to use only REGEXP_LIKE as I've to take the range from another table where it is stored in the format [0-200].

Any suggestions would be of great help.

Upvotes: 3

Views: 479

Answers (3)

Aramillo
Aramillo

Reputation: 3216

Something like this should works:

select ip_address from ip_data
where NOT REGEXP_LIKE(ip_address,'\.(([0-9])|([1-9][0-9])|([1][0-9][0-9])|(200))$');

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

Using REGEXP_SUBSTR:

SQL> WITH DATA AS(
  2  SELECT '127.0.0.1' ip FROM dual UNION ALL
  3  SELECT '127.0.0.201' ip FROM dual
  4  )
  5  SELECT * FROM DATA
  6  WHERE REGEXP_SUBSTR(ip,'[[:digit:]]{1,3}',1, 4)
  7  NOT BETWEEN 0 AND 200
  8  /

IP
-----------
127.0.0.201

SQL>

Using simple SUBSTR:

SQL> WITH DATA AS(
  2  SELECT '127.0.0.1' ip FROM dual UNION ALL
  3  SELECT '127.0.0.201' ip FROM dual
  4  )
  5  SELECT * FROM DATA
  6  WHERE to_number(substr(ip,instr(ip,'.',1,3)+1))
  7  NOT BETWEEN 0 AND 200
  8  /

IP
-----------
127.0.0.201

SQL>

I would go with the simple SUBSTR approach since it is less resource consuming, less CPU utilization, thus better performance.

Upvotes: 2

Marcin Wroblewski
Marcin Wroblewski

Reputation: 3571

You don't need regular expression for that:

to_number(substr(ip_address,instr(ip_address,'.',1,3)+1)) not between 0 and 200

Upvotes: 1

Related Questions