Click here to Skip to main content
14,696,341 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi all...

I want MYSQL query for split the data from MySQL database, the following table view is easy to understand what i want...

client table:

----------------------  |  batch
1     |  1@ravi@1991
2     |  2@kumar@1990
3     |  3@raj@1989

here 1@ravi@1991

1=client id
ravi=client name
1991=client DOB.
Now i want display the details like below
-------------------------  |  id | name | DOB
1     |  1 |ravi  |1991
2     |  2 |kumar |1990
3     |  3 |raj   |1989

pls send your ideas....
Updated 6-Oct-13 7:16am

You can use SUBSTRING_INDEX(str,delim,count)[^] for this.

First value:
SUBSTRING_INDEX( batch, '@', 1 )

Second value:
SUBSTRING_INDEX( SUBSTRING_INDEX( batch, '@', -2 ), '@', 1 )

Third value:
SUBSTRING_INDEX( batch, '@', -1 )

I have not tested this, but based on the documentation this should work.
sv sathish 6-Oct-13 13:06pm
Thank you sir.. it's work..
Manfred Rudolf Bihy 6-Oct-13 14:02pm
Have my five!
A slap on the wrist to OP has also been administered. :D
André Kraak 6-Oct-13 14:13pm
Solution is here::
select SUBSTRING_INDEX(batch,'@',1) as id,
SUBSTRING_INDEX( SUBSTRING_INDEX( batch, '@', -2 ), '@', 1 ) as name,
SUBSTRING_INDEX( batch, '@', -1 ) as DOB
from stock;

Thank for Andre kraak...
Manfred Rudolf Bihy 6-Oct-13 14:01pm
So you think it appropriate to accept your own answer, even though André Kraak's solution helped you construct it in the first place. This is considered abuse and was already reported as such. If you are intending to stay here on CP for a bit longer please rectify this.


This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900