Search code examples
mysqlsqlalter

How to convert a column from varchar to decimal in MySQL, knowing that the values are separated with commas

I tried the following MySQL requests to convert a column from varchar to decimal type, but I got Errors.

ALTER TABLE `cmd-services` CHANGE `Qte Sortie` `Qte Sortie` CAST(REPLACE(`Qte Sortie`, ',', '.') as DECIMAL(20,2));

ALTER TABLE `cmd-services` MODIFY `Qte Sortie` CAST(REPLACE(`Qte Sortie`, ',', '.') as DECIMAL(20,2));

I need your help. thank's.

Solution

  • You can do it in 2 steps

    1. Update the column and set all non numberic values to null so alter won't fail.
    2. Alter the table and set the type to int

    Step 1:

    UPDATE `cmd-services`
set `Qte Sortie` = CAST(REPLACE(`Qte Sortie`, ',', '.') as DECIMAL(20,2));

    Step 2:

    ALTER TABLE `cmd-services`
MODIFY `Qte Sortie` DECIMAL(20,2);

    This takes the assumption that the columns allow nulls. If not then that needs to be handled as well.

    For example: By altering the column to allow null, then after it has been converted to DECIMAL then set all null values to 0 and alter the table to not allow null.