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 )

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

Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com



    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 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer