-1

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.

william.eyidi
  • 2,315
  • 4
  • 27
  • 39
Santosh
  • 17
  • 6
  • So you want to convert from `degrees:minutes:seconds` to degress with decimal number? Or just want to get rid of those colons and convert to number? – Marki555 Jul 14 '15 at 17:35
  • I just want to get rid of those colons – Santosh Jul 14 '15 at 17:40
  • And convert the first color into dot? Anyway, you can't put decimal numbers into field of type `int`. – Marki555 Jul 14 '15 at 17:43
  • what do you mean by first color? I just want to change (+ or -) 36:12:20:0654 to (+ or -) 36.12200654. How do I do that? – Santosh Jul 14 '15 at 17:52
  • Okay I converted first colon into dot and then i can see three dots. How do i remove the last two dots. I tried this.... "update table_name set column_name = replace(column_name, ':','.'); but it shows the number as 36.12..20.0654 – Santosh Jul 14 '15 at 18:00

1 Answers1

0

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)), ':', ''), '.', ''))
Ishamael
  • 12,583
  • 4
  • 34
  • 52
  • I did exactly what you said but in the end it turned up filling zero in the column. – Santosh Jul 14 '15 at 18:15
  • Paste couple real examples of data you have. Do you actually have a space after +/-? If yes, do `update table_name set temp = concat(replace(substr(column_name, 1, position(':' in column_name) - 1), ' ', ''), '.', replace(substr(column_name, position(':' in column_name)), ':', ''));` instead of the update in the answer. – Ishamael Jul 14 '15 at 18:35
  • -99:13:42.1773, 36:25:78.3658 etc are the real examples. – Santosh Jul 14 '15 at 18:44
  • This is not the same format as in the question. The question didn't have any dots. Updated the answer with a new `UPDATE` – Ishamael Jul 14 '15 at 18:50
  • Make the column `varchar` instead of double. Double cannot hold those zeroes. – Ishamael Jul 14 '15 at 19:02
  • @Santosh, Nice, I didn't know that. – Ishamael Jul 14 '15 at 19:16
  • Thank you but it doesn't work for negative values. for example -99:23:21.0268 changes to -9.23210268 and some change from -99:23:21.2301 to 0.23212301.... Is there anything i need to change – Santosh Jul 14 '15 at 19:25
  • I got the exact values but still the datatype is varchar. I want it to be float or double without losing any data. I want the values to be same but the type should be float or double. please help me and this will be my last question – Santosh Jul 14 '15 at 20:48