We have already seen, How to split string in MySQL query in my previous post. Now I like to expand this with more worth thinks by doing. Lets consider task to split the string between two characters in mysql.
How to get split the tld from url
below example will describe how to extract tld in mysql from website URL or domain.
Example :
String: https://code-cocktail.in/
Result: .in
Task: getting the tld from url.
Sample Data table
| website name | website Url |
|---|---|
| codecoktail | https://code-cocktail.in/ |
| Rootaccez | http://store.rootaccez.com/wp-wiki-userprofile |
| Wild into the society | http://wild-into-the-society.blogspot.in/ |
Mysql Query
SELECT CONCAT('.',SUBSTRING_INDEX(SUBSTRING_INDEX(website_url,'/',3),'.',-1))
tld from website
Little More
okay we got it now, I think lets go little further. we may to exclude the few domain when we find the countries. here we see how to do that. I am going to exclude the blog spot websites when I from extract query
SELECT CONCAT('.',SUBSTRING_INDEX(SUBSTRING_INDEX(website_url,'/',3),'.',-1))
tld from website where LOCATE('.blogspot.',website_url) = 0
Have fun!