substring_index string functionSubstring_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
Output is ..
Output is ../my_dir
Output is page1.php
Output is dir_name/page1.php
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.
Here is the query to get the
This will give output as
Like this the full list can be displayed. We can use count and group by command to generate a query by which we can tell number of subscribers using email address of different domains. Say how many have yahoo account, how many have gmail account etc….
First name or Last name from full nameUsing substring_index we can collect the part of the string using a delimiter. Here we will use one blank space as delimiter to get the first name from the full name of student list.
Now here is the query to get the last name from the full name.
To remove from end the forward slash
To remove from starting ( left side )
Substring and substring_indexIn place of delimiter we can use position index to get part of the string by using substring function
Download sql dump of this student table