mysql split string between two characters

on

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
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!

 

 

 

Posted in MySql and tagged by .

About Gowri

I am professional web developer with 8+ years experience. PHP, jQuery, WordPress, Angular and Ionic are my key skills in web development. I am working with strong enthusiastic team with spirit. We provide all web related solution like HTML/CSS development, Web graphic design and Logo.