Search code examples
mysqlurltext-parsing

How to remove subdomain from url in mysql?

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'

Solution

  • 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