Bifurcate Data from one column into many column

Expand Post »

Hi All,
I have a table in mysql in which there is a column which have following kind of data
$Category=>Income;$NAV=>10.2181;$Repurchase;Price=>0;$Sale;Price=>0;$Date=>26-Feb-2008;

I want to Bifurcate the data in that column into 5 separate columns in the same table in this way
$Category=>Income in this thing "Income" in column "category".
$NAV=>10.2181 in this thing "10.2181" in column "NAV"
$Repurchase;Price=>0 in this thing "0" in column "Rprice".
$Sale;Price=>0 in this thing "0" in column "Sprice"
$Date=>26-Feb-2008 in this "26-Feb-2008" in column "date"

how can we do this in mysql query?

All this data is in varchar format ...
so Can be save NAV , Repurchase Price and Sale price data in Float format and Date as MySQL Date Format....Though it is not necessary required.

thanks to all viewer...

Similar Threads

php/mysql display data in row and column in MySQL
Listview: How do I get the data from a column in a selected row in C#
Sort by column - I'm stumped in Python
adding and searching newly added column...HELP PLEASE!!!!! :( in ASP
reducing column size in datagrid in VB.NET

theshakti

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Feb 2010

Permalink

Feb 18th, 2010

Re: Bifurcate Data from one column into many column

Following query will help you. Here I assume that you have whole data string in "mytable" in column "data". Also I assume that same table contains all other 5 column. For column 1 index is -5, for 2 index is -4 and so on.

MySQL Syntax (Toggle Plain Text)
UPDATE table  mytable
SET 
category=substr(SUBSTRING_INDEX(data,'>',-5),1,INSTR(SUBSTRING_INDEX(data,'>',-5),';')-1),
nav=substr(SUBSTRING_INDEX(data,'>',-4),1,INSTR(SUBSTRING_INDEX(data,'>',-4),';')-1),
rprice=substr(SUBSTRING_INDEX(data,'>',-3),1,INSTR(SUBSTRING_INDEX(data,'>',-3),';')-1),
sprice=substr(SUBSTRING_INDEX(data,'>',-2),1,INSTR(SUBSTRING_INDEX(data,'>',-2),';')-1),
DATE=substr(SUBSTRING_INDEX(data,'>',-1),1,INSTR(SUBSTRING_INDEX(data,'>',-1),';')-1)
update table  mytable
set 
category=substr(substring_index(data,'>',-5),1,instr(substring_index(data,'>',-5),';')-1),
nav=substr(substring_index(data,'>',-4),1,instr(substring_index(data,'>',-4),';')-1),
rprice=substr(substring_index(data,'>',-3),1,instr(substring_index(data,'>',-3),';')-1),
sprice=substr(substring_index(data,'>',-2),1,instr(substring_index(data,'>',-2),';')-1),
date=substr(substring_index(data,'>',-1),1,instr(substring_index(data,'>',-1),';')-1)

urtrivedi

Reputation Points: 245

Solved Threads: 259

Nearly a Posting Virtuoso

Offline

1,248 posts
since Dec 2008

Permalink

Feb 18th, 2010

Thanks Mr. urtriviedi

It worked like a charm...

Regards
Shakti

theshakti

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Feb 2010

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: How can I sort this so that it displays chronologically??

Next Thread in MySQL Forum Timeline: Schema one to many designing in MySQL

DaniWeb Message

Cancel Changes

User Name
Password