Hi,
I'm administrating a mysql db via phpmyadmin and there's a VARCHAR field 'total' with some data stored in, i.e. 1432,26 (as Euro format).
I want to convert the VARCHAR type in DECIMAL or DOUBLE type because of some calculating problems, and I've tried to change the field's type (using phpmyadmin) from VARCHAR 16 into DECIMAL 16,2 but the result is 1432,00 and decimal ciphers were lost.
In which way can I change field format?
Thanks so much for help.

Remigio

On 2011-10-21 16:21, remigio wrote:
I'm only guessing here, but it might be the case that "," is not valid
as a decimal separator in your setup

create table T ( total varchar(10) not null ) engine = innodb;
insert into T (total) values ('1432,26'); -- ","
insert into T (total) values ('1432.26'); -- "."

mysql> select total, cast(total as decimal(6,2)) from T;
+---------+-----------------------------+
2 rows in set (0.00 sec)

/Lennart

On 10/21/2011 10:21 AM, remigio wrote:
You'll need to do this in stages:
1. Add a new column to the table, e.g.
ALTER TABLE mytable ADD COLUMN total_d DECIMAL (6,2);
2. Populate this column with data converted from VARCHAR to DECIMAL, e.g.
UPDATE mytable SET total_d = CAST (total AS DECIMAL);
3. *After* verifying that the new column has been updated correctly, get
rid of the VARCHAR column:
ALTER TABLE mytable DROP COLUMN total;
4. Rename the new column:
ALTER TABLE mytable CHANGE COLUMN total_d total;

El 21/10/2011 16:21, remigio escribió/wrote:
A little hint:

SELECT
'1432,26',
CAST('1432,26' AS DECIMAL(20,2)),
REPLACE('1432,26', ',', '.'),
CAST(REPLACE('1432,26', ',', '.') AS DECIMAL(20,2))


--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--