Search code examples
mysqlsqlmariadbmariasql

MariaDB convert column type varchar to int

Column has longitude values of form (+ or -) 36:12:20:0654. I want to change them to (+ or -) 36.12200654. How do I do that? I tried this command:

alter table_name alter column column_name int;

but it changes the value to 36 with no sign.

Solution

  • I am not proud of the complexity of what I came up with, but it does the trick:

    alter table table_name add column temp double;
update table_name set temp = concat(substr(column_name, 1, position(':' in column_name) - 1), '.', replace(substr(column_name, position(':' in column_name)), ':', ''));
alter table table_name drop column column_name;
alter table table_name change column temp column_name double;

    UPDATE: based on the new format of the values in the comment, update should look like:

    update table_name set temp = concat(substr(column_name, 1, position(':' in column_name) - 1), '.', replace(replace(substr(column_name, position(':' in column_name)), ':', ''), '.', ''))