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
Visitors Rating
Your Rating




Google+

Post Comment This is for short comments only. Use the forum for more discussions.








HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us
©2000-2018 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer