substring_index string function

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

Subscribe to our YouTube Channel here


* indicates required
Subscribe to plus2net

    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-2024 All rights reserved worldwide Privacy Policy Disclaimer