substring_index function for part of a string from a main string

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

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 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
SQL String References elt(): String from the postion

Subscribe to our YouTube Channel here

substring_index string function

First name or Last name from full name

To remove from end the forward slash

Substring and substring_index

Subscribe