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