How to split the name string in mysql ?
E.g.:
name
-----
Sachin ramesh tendulkar
Rahul dravid
Split the name like firstname,middlename,lastname
:
firstname middlename lastname
--------- ------------ ------------
sachin ramesh tendulkar
rahul dravid
Well, nothing I used worked, so I decided creating a real simple split function, hope it helps:
Explaining:
Find firstname and lastname are easy, you have just to use SUBSTR_INDEX function Magic happens in middlename, where was used SUBSTR with Locate to find the first space position and LENGTH of fullname - (LENGTH firstname + LENGTH lastname) to get all the middlename.
Note that LENGTH of firstname and lastname were calculated using SUBSTR_INDEX
First Create Procedure as Below:
After that create procedure as below:
How call test
There is no string split function in MySQL. so you have to create your own function. This will help you. More details at this link.
Function:
Usage:
Example:
We have stored the value of course Name and chapter name in single column ChapterName.
Value stored like : " JAVA : Polymorphism "
you need to retrieve CourseName : JAVA and ChapterName : Polymorphism
Below is the SQL select query to retrieve .
Please let me know if any question on this.
select case when locate('(', LocationName) = 0 then horse_name else left(LocationName, locate('(', LocationName) - 1) end as Country
from tblcountry;