Split the string in MySql

Question

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:

VB

----------------------
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

VB

-------------------------
s.no  |  id | name | DOB
-------------------------
1     |  1 |ravi  |1991
2     |  2 |kumar |1990
3     |  3 |raj   |1989
-------------------------

pls send your ideas....

Posted 5-Oct-13 8:48am

sv sathish

Updated 6-Oct-13 6:16am

v3

Add a Solution

2 solutions

Solution 2

Solution is here::

SQL

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...

Posted 6-Oct-13 7:15am

sv sathish

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!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

André Kraak · Accepted Answer · 2013-10-06T06:27:00

Solution 1

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

First value:

SQL

SUBSTRING_INDEX( batch, '@', 1 )

Second value:

SQL

SUBSTRING_INDEX( SUBSTRING_INDEX( batch, '@', -2 ), '@', 1 )

Third value:

SQL

SUBSTRING_INDEX( batch, '@', -1 )

I have not tested this, but based on the documentation this should work.

Posted 6-Oct-13 6:27am

André Kraak

Updated 6-Oct-13 6:31am

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.