Tuesday, 24 July 2012

Find a sub string from as string splited by delimiter in MYSQL

String operation with MYSQL database is quite simple. But some times it found very ridiculous to find a expected result.That exactly happens with me. Actually I was expecting the result like below :-

But when I run my query it generates the out put like below:-

But i want only the name before the first comma(,) occurrence like :-

 Jon kumar Pattnaik from first row.

To find the expected result from database I have used SUBSTRING_INDEX(str,delim,index count) method.A string operation method .

Query :-

select SUBSTRING_INDEX(GROUP_CONCAT(s.vchSName,' ',vchMidName,' ',vchLastName),',',1) as nam,vchSGudian,vchSGRelation  from t_emp_details ;

Now its running fine with a good expected result.

SUBSTRING_INDEX(str,delim,index count)

Notes :- Parameters

str- is the main string from which we need to find the substring.
delim- is the separator of main string.
index count- is the number of separator you want to find.


select SUBSTRING_INDEX( 'You,are,a,programmer',',',1) from myTable;

Out put:- You

Here in the above query :-

You,are,a,programmer.--( Main String)
comma (,) -- (Separator or delim)
1 --(Index Count)