IP address resolution functions

ip2region(ip, level, lang)

• Description: This function obtains information about the country, province, or city to which an IP address belongs, and supports language settings.

• Return value type: VARCHAR.

• Parameters:

  • ip: The IP address that you want to query.

  • level: The level at which information is queried. Valid values: country, province, city, and isp.

  • lang: The language in which results are returned. Valid values: CN and EN.

• Example:

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

ip_country/ip_province/ip_city/ip_isp

• Description: These functions are simple application of the ip2region function.

  • ip_country: obtains the country to which an IP address belongs.

  • ip_province: obtains the province to which an IP address belongs.

  • ip_city: obtains the city to which an IP address belongs.

  • ip_isp: obtains the Internet service provider that provides an IP address.

• Example:

    select ip_country('31.13.79.1')
     +---------------------------+
     |印度                        |
     select ip_country('31.13.79.1','EN')
     +-----------------------------+
     |India                        |
     select ip_province('31.13.79.1')
     +-----------------------------+
     |马哈拉施特拉邦                 |
     select ip_province('31.13.79.1','EN')
     +------------------------------+
     |Maharashtr                    |
     select ip_city('31.13.79.1')
     +------------------------------+
     |孟买                          |
     select ip_city('31.13.79.1','EN')
     +------------------------------+
     |Mumbai                        |
     select ip_isp('115.239.210.27')
     +------------------------------+
     |电信                           |
     select ip_isp('115.239.210.27','EN')
     +-------------------------------+
     |ChinaTelecom                   |

ip2long

ip2long(ip)

• Description: This function converts an IP address into a string of digits.

• Return value type: BIGINT.

• Example:

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

long2ip

long2ip(longVal)

• Description: This function converts a string of digits into an IP address.

• Return value type: VARCHAR.

• Example:

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

ip_city_geo

ip_city_geo(ip)

• Description: This function queries the latitude and longitude of the city to which an IP address belongs.

• Example:

    SELECT ip_city_geo('42.120.74.91');
    +----------------------------+
    |   30.252501,120.165024     |

ip_timezone

ip_timezone(ip)

• Description: This function queries the time zone to which an IP address belongs.

• Example:

    SELECT ip_timezone('42.120.74.91');
    +----------------------------+
    |       Asia/Shanghai        |

ip_utc_offset

ip_utc_offset(ip)

• Description: This function queries the UTC offset of the time zone to which an IP address belongs.

• Example:

    SELECT ip_utc_offset('42.120.74.91');
    +----------------------------+
    |           UTC+8            |

ip_china_admin_code

ip_china_admin_code(ip)

• Description: This function queries the administrative code for an IP address in mainland China.

• Example:

    SELECT ip_china_admin_code('42.120.74.91');
    +----------------------------------------+
    |                 330100                 |

ip_idd_code

ip_idd_code(ip)

• Description: This function queries the code of the country to which an IP address belongs. Example: 86 for mainland China.

• Example:

    SELECT ip_idd_code('42.120.74.91');
    +----------------------------+
    |             86             |

ip_country_code

ip_country_code(ip)

• Description: This function queries the internationalized domain name of the country to which an IP address belongs. Example: CN for mainland China.

• Example:

    SELECT ip_country_code('42.120.74.91');
    +----------------------------+
    |             CN             |

ip_continent_code

ip_continent_code(ip)

• Description: This function queries the continent code for an IP address. Example: AP for the Asia Pacific region.

• Example:

    SELECT ip_continent_code('42.120.74.91');
    +--------------------------------------+
    |                   AP                 |

ip_country_code_3

ip_country_code_3(ip)

• Description: This function queries the 3-letter code of the country to which an IP address belongs. Example: CHN for mainland China.

• Example:

    SELECT ip_country_code_3('42.120.74.91');
    +--------------------------------------+
    |                   CHN                |

ip_currency_code

ip_currency_code(ip)

• Description: This function queries the currency code of the country to which an IP address belongs. Example: CNY for mainland China.

• Example:

    SELECT ip_currency_code('42.120.74.91');
    +--------------------------------------+
    |                   CNY                |

ip_currency_name

ip_currency_name(ip)

• Description: This function queries the currency name of the country to which an IP address belongs. Example: Yuan Renminbi for mainland China.

• Example:

    SELECT ip_currency_name('42.120.74.91');
    +--------------------------------------+
    |             Yuan Renminbi            |