IP address resolution functions

ip2region

ip2region(ip, level, lang)

• Function description: This function obtains the country/region, province, and city where the IP address resides, and supports language setting.

• Return value type: VARCHAR

• Parameter description:

  • ip: the IP address you want to query

  • level: four options, namely, country, province, city, and isp.

  • lang: CN or EN, which means that the return value is displayed in Chinese or English

• For example:

   select ip2region('31.13.79.1', 'country', 'CN')
     + --------------------------------------------- +
     | India                                         |
     select ip2region('31.13.79.1', 'country', 'EN')
     + --------------------------------------------- +
     | India                                        |

ip_country/ip_province/ip_city/ip_isp

ip_country/ip_province/ip_city/ip_isp is a simple application of the ip2region function.

• ip_country: obtains the country/region where the IP address resides.

• ip_province: obtains the province where the IP address resides.

• ip_city: obtains the city where the IP address resides.

• ip_isp: obtains the Internet service provider (ISP) that allocates the IP address.

select ip_country('31.13.79.1')
 +---------------------------+
 | India                        |
 select ip_country('31.13.79.1','EN')
 +-----------------------------+
 | India                       |
 select ip_province('31.13.79.1')
 +-----------------------------+
 |Maharashtra                 |
 select ip_province('31.13.79.1','EN')
 +------------------------------+
 |Maharashtra                   |
 select ip_city('31.13.79.1')
 +------------------------------+
 |Mumbai                          |
 select ip_city('31.13.79.1','EN')
 +------------------------------+
 |Mumbai                        |
 select ip_isp('115.239.210.27')
 +------------------------------+
 |China Telecom                           |
 select ip_isp('115.239.210.27','EN')
 +-------------------------------+
 |ChinaTelecom                   |

ip2long

ip2long(ip)

• Function description: This function converts an IP address into a digit.

• Return value type: BIGINT

• For example:

   select ip2long('115.239.210.27')
     +----------------------------+
     |1945096731                  |

long2ip

long2ip(longVal)

• Function Description: This function converts a number into an IP address.

• Return value type: VARCHAR

• Parameter description: longVal is a BIGINT value.

• For example:

   select long2ip(1945096731)
     +--------------------+
     | 115.239.210.27     |