mySQL Database Function IP Address Octets – Parsing

How to create a mySQL function to get “N” octets of an IP Address

In a recent project, we came across a need to parse out the first two, or first three octets of an IP Address (IPv4) for comparison of records based on a section of the IP. The end-user could choose to match on the first two octets or first three octets depending on what level of granularity they wanted in locating IP blocks to add to “Whitelists” or “Blacklists”.

The data was stored in mySQL with the IP Address column being a varchar(100) column and IP Address stored in typical IPv4 format. mySQL doesn’t have any built in parsing functions that use a delimiter so it was decided to create a mySQL Function.

Business Requirement – Match Rows using the first 2 or 3 Octets of an IP Address

Specifically the application and/or user might need to:

• Match multiple records on IP Address column using two octets of the IPv4 Address. (e.g. 194.168.*.* )
• Match multiple records on IP Address column using three octets of the IPv4 Address. (e.g. 194.168.1.* )
• User to specify how many octets to use depending on the level of granularity desired in creating IP block “white” & “black” lists.  A user may choose to use only two octets to catch larger companies and/or Internet Providers that own large blocks of IP Addresses.
• Must allow for comparing hundreds of thousands of records with no major impact on performance.

New mySQL Function

varchar(16) = fx_get_n_ip_octets( inIP varchar(16), inNumOctets int(1)  )

-- --------------------------------------------------------------------------------
-- mySQL Function: fx_get_n_ip_octets
-- Arguments:      inIP varchar(16) - The IP Address in IPv4 format
--                 in_octets int(1) - The number of octets to get from beginning
-- Note: Send in an IP Address and number of octets of the Ip Address you
-- want returned, can be 1 through 4 covering the full IP address range
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=application_user@% FUNCTION fx_get_n_ip_octets(in_IP varchar(20), in_octets int(1)) RETURNS varchar(16) CHARSET utf8
BEGIN
 DECLARE ip_section varchar(16);

 CASE in_octets
   WHEN 1 THEN
     SET ip_section = SUBSTRING_INDEX( in_IP, '.', 1 ); 
   WHEN 2 THEN
     SET ip_section = 
       CONCAT(SUBSTRING_INDEX( in_IP , '.', 1 ),'.',
       SUBSTRING_INDEX(SUBSTRING_INDEX( in_IP , '.', 2 ),'.',-1));
   WHEN 3 THEN
     SET ip_section = 
       CONCAT(SUBSTRING_INDEX( in_IP , '.', 1 ),'.',
       SUBSTRING_INDEX(SUBSTRING_INDEX( in_IP , '.', 2 ),'.',-1),'.',
       SUBSTRING_INDEX(SUBSTRING_INDEX( in_IP , '.', -2 ),'.',1));
   WHEN NONE THEN
     SET ip_section = in_IP;
 END CASE;

 return ip_section;
END

Sample of usage of the mySQL function that parses the IP Address column

SELECT 
 comment_author_IP
FROM wp_14_comments
WHERE 
 fx_get_n_ip_octets(comment_author_IP, 2) = '94.142';

Result Data:

The function can be used to join a table using a portion of the IP address and the function used on both sides of the join. There is probably another way to write this function, possibly much better and if you know of one please leave a comment and let us know.

For now I hope this was useful.