1

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.

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

> 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

  1. Query the table.
  2. Check whether the value can be converted to float.
  3. 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.

3

Let me suggest to add a new column and then update this new column before to drop the old one.

  1. Backup, backup, backup

create table mytable (ConstantValue varchar(100));

insert into mytable values
('1252.36'),('abc'),('55969'),('def'),('66955663'),('A8879'),('-41.25'),('+636');
  1. Add a new column with a default value of 0.

alter table mytable
    add column NewConstantValue double default(0);
  1. 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]+)$';
  1. Update new column with current values.

update mytable
set    NewConstantValue = cast(ConstantValue as double);
  1. 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
  1. Drop ConstantValue column.

alter table mytable
    drop column ConstantValue;
  1. Rename new column as ConstantValue.

alter table mytable
    change column NewConstantValue ConstantValue double default(0);
  1. Check final result.

select * from mytable;
| ConstantValue |
| ------------: |
|       1252.36 |
|             0 |
|         55969 |
|             0 |
|      66955663 |
|             0 |
|        -41.25 |
|           636 |

dbfiddle here

4
  • Why do you format all your code additionally as citation?
    – miracle173
    Feb 23 '18 at 16:47
  • @miracle173 follow dbfiddle.uk link and click markdown button.
    – McNets
    Feb 23 '18 at 18:18
  • thank you very much for this solution and I avoided using Python! this works, the only different thing is when casting to double, MySQL has a different format. CAST(Value as Decimal(4,2)) This is what I used.
    – ellaRT
    Feb 26 '18 at 1:02
  • I'm glad to help
    – McNets
    Feb 26 '18 at 13:49
1

As a complement to the solution by @McNets, you can use a CONTINUE HANDLER to handle casting errors:

delimiter //
create or replace function str2double(s varchar(10))
returns double
deterministic
begin
    declare continue handler for sqlstate '22007' return 0;
    return cast(s as double);
end //
delimiter ;

update mytable set ConstantValue = str2double(ConstantValue);

drop function str2double;

Now all values in column should be valid doubles, and you can alter the table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.