How to remove subdomain from url in mysql?

Question

I found a similar question on this page Mysql query to extract domains from urls

SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(target_url, '/', 3), '://', -1), '/', 1), '?', 1) AS domain

But the result of this code is not correct

'www.abc.com' 'lalala.one.google.com' 'two.one.test.com'

I need to get the 2 last words, separator is dot.

I need this result

'abc.com' 'google.com' 'test.com'

Answer 1

With subtring() and substring_index():

set @url = 'https://stackoverflow.com/questions/57937363/how-to-remove-subdomain-from-url-in-mysql';
select substring_index(substring_index(substring(@url, locate('://', @url) + 3), '/', 1), '.', -2) as domain

See the demo.
Result:

domain
-----------------
stackoverflow.com

Answer 2

select SUBSTRING_INDEX('aaa.bbb.sss.google.com','.',-2);