CODEWITHSUNDEEP
sqlregexvalidationoracle11g
ajmalmhd04
ajmalmhd04

Reputation: 2602

Oracle regular expression for number range and length

I need to validate a given list of ip address. The ip address should be in the range of 1.0.0.0 to 255.255.255.255

Below is what I've done so far:

WITH T(VAL) AS
     ( SELECT '123.1235.231.234.12' FROM dual
     UNION ALL
     SELECT '123123' FROM dual
     UNION ALL
     SELECT '011' FROM dual
     UNION ALL
     SELECT '011.' FROM dual
     UNION ALL
     SELECT '000.' FROM dual
     UNION ALL
     SELECT '123123' FROM dual
     UNION ALL
     SELECT '192.168.1.65' FROM dual
     UNION ALL
     SELECT '255.256.1.65' FROM dual
     )
SELECT *
FROM t
WHERE REGEXP_LIKE(VAL,'^[1-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$')
 AND REGEXP_SUBSTR(VAL, '[1-9]{1,3}',1,1) BETWEEN 1 AND 255
 AND regexp_substr(val, '[0-9]{1,3}',1,2) BETWEEN 0 AND 255
 AND REGEXP_SUBSTR(VAL, '[0-9]{1,3}',1,3) BETWEEN 0 AND 255
 AND REGEXP_SUBSTR(VAL, '[0-9]{1,3}',1,4) BETWEEN 0 AND 255

Its working fine with the data I tested. Please help if I can do the same with simple regular expression.

Thanks in advance:)

Upvotes: 2

Views: 2181

Answers (1)

Toto
Toto

Reputation: 91518

There is an error in your regex, it doesn't match 10.0.0.0 for example.

Change it to:

'^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$'

The test that follow will reject 0.0.0.0

Upvotes: 2

Related Questions