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'

user2219963 · Accepted Answer · 2019-09-14 16:38:32Z

3

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

answered Sep 14, 2019 at 16:38

user2219963

1,8402 gold badges12 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MediaGiantDesign Over a year ago

This worked well to extract the basedomain from a sub-domain.

forpas · Accepted Answer · 2019-09-14 16:46:02Z

2

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

answered Sep 14, 2019 at 16:46

forpas

165k10 gold badges51 silver badges85 bronze badges

Collectives™ on Stack Overflow

How to remove subdomain from url in mysql?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related