Update column values to integers I have a column in my table that I need to change the data type from VARCHAR to DOUBLE. Now I cannot convert it to DOUBLE data type easily because the values on that column have numbers and strings. 

&gt; 0 41  14:16:57
  ALTER TABLE `ControlAutomation`.`constantcomponentstabular`
     DROP COLUMN `String`,
     CHANGE COLUMN `ConstantValue` `ConstantValue` DOUBLE NULL DEFAULT NULL

&gt; Error Code: 1265. Data truncated for column 'ConstantValue' at row 217966 1.891 sec


I know what is causing this issue, its because there are strings on the column that I want to change to DOUBLE.

Here is the solution that I have in mind using Python


Query the table.
Check whether the value can be converted to float.
If not, UPDATE value of that column to 0.


This process takes a long time, My question is how to best change those values to any number so I will be able to ALTER the table.

I am using MySQL 5.6.32 and InnoDB as storage engine.
Let me suggest to add a new column and then update this new column before to drop the old one.


Backup, backup, backup





create table mytable (ConstantValue varchar(100));

insert into mytable values
('1252.36'),('abc'),('55969'),('def'),('66955663'),('A8879'),('-41.25'),('+636');




Add a new column with a default value of 0.





alter table mytable
    add column NewConstantValue double default(0);




Set alphanumeric values to 0. I've borrowed this regex expression from MySql forums





update mytable
set    ConstantValue = '0'
where  ConstantValue not regexp '^(-|\\+)?([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';




Update new column with current values.





update mytable
set    NewConstantValue = cast(ConstantValue as double);




Check new values before to drop old column.





select * from mytable;



ConstantValue | NewConstantValue
:------------ | ---------------:
1252.36       |          1252.36
0             |                0
55969         |            55969
0             |                0
66955663      |         66955663
0             |                0
-41.25        |           -41.25
+636          |              636




Drop ConstantValue column.





alter table mytable
    drop column ConstantValue;




Rename new column as ConstantValue.





alter table mytable
    change column NewConstantValue ConstantValue double default(0);




Check final result.





select * from mytable;



| ConstantValue |
| ------------: |
|       1252.36 |
|             0 |
|         55969 |
|             0 |
|      66955663 |
|             0 |
|        -41.25 |
|           636 |



dbfiddle here