substring_index string function

Substring_index function returns us a substring from the main string from a landmark which is given by delimiter. From the delimiter to left or right ( towards staring or ending ) of the string can be given by count. If count is positive then everything towards left from final delimiter ( from left ) is returned. If the count is negative then everything towards right from final delimiter is returned.

Here is the syntax

substring_index(string,"delimiter”,count )

We will apply this to our newsletter subscriber table where we stored email address of our subscribers. Here we will apply substring_index to separate domain part and userid part and maintain a list. Here is a sample of email address.

userid@domainname.com

Here is the query to get the

select email,substring_index(email,"@",-1) from newsletter_table

This will give output as

userid@aol.com aol.com
onemore@gmail.com gmail.com

Like this the full list can be displayed. We can use count and group by command to generate a query to which can tell us number of subscribers in each domain. Say how many have yahoo account, how many have gmail account etc….

SELECT count(substring_index(email,"@",-1) ) as no, email FROM `newsletter_table` group by substring_index(email,"@",-1) order by no desc