mysql - MariaDB convert column type varchar to int -

column has longitude values of form (+ or -) 36:12:20:0654. want change them (+ or -) 36.12200654. how do that? tried command:

alter table_name alter column column_name int; 

but changes value 36 no sign.

i not proud of complexity of came with, 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 new format of values in comment, update should 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)), ':', ''), '.', ''))