Extract ISP name from hostname
I found this tool which is doing exactly what I asked for
https://github.com/rvflash/publicsuffix-sql
It uses publicsuffix.org which maintains a list of public suffixes such as .com, .co.uk and pvt.k12.ma.us.
Using the function provided by the tool, we can simply do something like this:
select DOMAIN('115.20.106.216'); -- null 
select DOMAIN('cpe-45-37-121-56.nc.res.rr.com'); -- rr.com 
select DOMAIN('CPE00fc8d22x-CM023d22f4f0.cpe.net.cable.rogers.com'); -- rogers.com 
select DOMAIN('c-208-168-15-86.wifi.ties.k12.mn.us'); -- k12.mn.us 
select DOMAIN('CPE-128-122-18-181.hdbq1.win.bigpond.net.au'); -- bigpond.net.au