substring_index function for part of a string from a main string

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 )

Examples

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.

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

gmail.com 63
yahoo.com 43
hotmail.com 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

substring_index string function

Examples

First name or Last name from full name

To remove from end the forward slash

Substring and substring_index

SQL Popular Tutorials