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:

----------------------
s.no  |  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
-------------------------
s.no  |  id | name | DOB
-------------------------
1     |  1 |ravi  |1991
2     |  2 |kumar |1990
3     |  3 |raj   |1989
-------------------------


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

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.
   
v2
Comments
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
   
Thanks.
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...
   
Comments
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.

Cheers!

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