Bifurcate Data from one column into many column

[theshakti]

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

[urtrivedi]

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.

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)

[theshakti]

It worked like a charm...

Regards
Shakti