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 )


SELECT SUBSTRING_INDEX('../my_dir/dir_name/page1.php','/',1)
Output is ..
SELECT SUBSTRING_INDEX('../my_dir/dir_name/page1.php','/',2)
Output is ../my_dir
SELECT SUBSTRING_INDEX('../my_dir/dir_name/page1.php','/',-1)
Output is page1.php
SELECT SUBSTRING_INDEX('../my_dir/dir_name/page1.php','/',-2)
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
select email,substring_index(email,"@",-1) from newsletter_table
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.
SELECT substring_index(email,"@",-1), count(substring_index(email,"@",-1) ) as no FROM `newsletter_table` group by substring_index(email,"@",-1) order by no desc 63 43 7

First name or Last name from full name

Using 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.
SELECT SUBSTRING_INDEX(name, ' ', 1) FROM `student`
Now here is the query to get the last name from the full name.
SELECT SUBSTRING_INDEX(name, ' ', -1) FROM `student`

To remove from end the forward slash

SELECT SUBSTRING_INDEX(dir,'/',2) FROM `table_name`
  To remove from starting ( left side )
SELECT SUBSTRING_INDEX( dir,'/',-2)FROM `table_name`

Substring and substring_index

In 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

Post your comments , suggestion , error , requirements etc here .

SQL Video Tutorials

We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2021 All rights reserved worldwide Privacy Policy Disclaimer