Convert the IP address and mask in Oracle to the CIDR format

The problem is as follows: the database stores IP addresses and IP address masks, which need to be converted into the CIDR format, and not only convert the mask into a number corresponding to the CIDR.

The problem is as follows: the database stores IP addresses and IP address masks, which need to be converted into the CIDR format, and not only convert the mask into a number corresponding to the CIDR.

The following problems occur: IP addresses and IP address masks are stored in the database, which need to be converted into the CIDR format and not just the numbers corresponding to the CIDR mask, you need to convert the original IP address to the corresponding network address. For example, if the IP address is 58.247.221.238 and the mask is 255.255.255.252, You need to convert it to 58.247.221.236/30.

Solution: we know that the IP address and mask can be obtained through the bitwise and function. Google, and find the function that converts the IPv4 address to a number and converts it back. With these two functions, we can solve the problem by using the bitand function provided by Oracle. You can convert the IP address and mask to a number through the string-to-IP function, and then obtain the number corresponding to the corresponding network address through bitwise and operation. Then, you can convert the number to a string, the corresponding network address is obtained. For the number of CIDR following/, You can import a table corresponding to the mask and CIDR number.

The actual example is as follows: 58.247.221.236 is returned.

SQL code
Select inttoip (BITAND (dottedQuadToNumber ('58. 247.221.238 '),
OttedQuadToNumber ('1970. 00000000252 ') from dual


Appendix: Functions for converting strings into numbers:

SQL code
Create or replace function dottedQuadToNumber (dottedQuad IN VARCHAR2) return number is
Result NUMBER;
Begin
Result: = (substr (dottedQuad,
1,
(Instr (dottedQuad, '.', 1, 1)-1 ))
* 256x256*256
) +
(Substr (dottedQuad,
Instr (dottedQuad, '.', 1, 1) + 1,
Instr (dottedQuad, '.', 1, 2 )-
Instr (dottedQuad, '.', 1, 1)-1) * 256*256
) +
(Substr (dottedQuad,
Instr (dottedQuad, '.', 1, 2) + 1,
Instr (dottedQuad, '.', 1, 3 )-
Instr (dottedQuad, '.', 1, 2)-1) * 256
) +
(Substr (dottedQuad,
Instr (dottedQuad, '.', 1, 3) + 1)
);
Return (Result );
End dottedQuadToNumber;

Function for converting numbers to IP addresses:

SQL code
Create or replace function inttoip (ip_address integer) return varchar2
Deterministic
Is
Begin
Return to_char (mod (trunc (ip_address/256/256 /256), 256 ))
| '.' | To_char (mod (trunc (ip_address/256/256), 256 ))
| '.' | To_char (mod (trunc (ip_address/256), 256 ))
| '.' | To_char (mod (ip_address, 256 ));
End;