Oracle SQL - Get rows with values in IP address format (dotted quad)

Question

I have a table which has SOURCEIP and DESTINATIONIP columns, and their data type is VARCHAR2. A value in these columns is either an IP address in dotted quad format, or an encrypted anonymous address, which in this case it can be a random string of letters and digits in varying lengths.

Which query can I use which will only give me rows with an least one proper (dotted quad) IP in either SOURCEIP or DESTINATIONIP?

Answer 1

To validate the IP address, you could use REGEXP_LIKE.

• Pattern : 0-255.0-255.0-255.0-255

For example,

SQL> WITH DATA AS(
  2  SELECT '10.20.30.40' ip_address FROM dual UNION ALL
  3  SELECT 'a.b.c.d' ip_address FROM dual UNION ALL
  4  SELECT '255.255.255.255' ip_address FROM dual
  5  )
  6  SELECT *
  7  FROM data
  8  WHERE REGEXP_LIKE(ip_address, '^(([0-9]{1}|[0-9][0-9]|1[0-9][0-9]|2[0-4][0-9]|25[0-5])\.){3}([0-9]{1}|[0-9][0-9]|1[0-9][0-9]|2[0-4][0-9]|25[0-5])$');

IP_ADDRESS
---------------
10.20.30.40
255.255.255.255

SQL>

A value in these columns is either an IP address in dotted quad format

If you mean this :: by dotted quad, then you could still use the same REGEXP to validate the IP address.

• Pattern : 0-255::0-255::0-255::0-255

For example,

SQL> WITH DATA AS(
  2  SELECT '10::20::30::40' ip_address FROM dual UNION ALL
  3  SELECT 'a.b.c.d' ip_address FROM dual UNION ALL
  4  SELECT '255.255.255.255' ip_address FROM dual
  5  )
  6  SELECT *
  7  FROM DATA
  8  WHERE REGEXP_LIKE(ip_address, '^(([0-9]{1}|[0-9][0-9]|1[0-9][0-9]|2[0-4][0-9]|25[0-5])\::){3}([0-9]{1}|[0-9][0-9]|1[0-9][0-9]|2[0-4][0-9]|25[0-5])$');

IP_ADDRESS
---------------
10::20::30::40

SQL>