Converts Degrees/Minutes/Seconds to Decimals using SQL

Question

Is there any SQL script that converts Degrees/Minutes/Seconds (saved in Column A) to Decimals (to be saved in new Column B)? I googled for existing solutions, but all examples refer to Decimals->Deg/Min/Sec. I appreciate any example, because I'm not strong in SQL scripting.

Answer 1

The following seems to work:

SELECT DMS, DEGREES + (MINUTES / 60) + (SECONDS / (60 * 60)) AS DECIMAL_DEGREES
  FROM (SELECT DMS,
               TRUNC(DMS) AS DEGREES,
               TRUNC((DMS - TRUNC(DMS)) * 100) AS MINUTES,
               ((DMS * 100) - TRUNC(DMS * 100)) * 100 AS SECONDS
          FROM TEST_DMS)

or, if you prefer it all as one expression:

SELECT DMS,
       TRUNC(DMS) +
         (TRUNC((DMS - TRUNC(DMS)) * 100) / 60) +
         (((DMS * 100) - TRUNC(DMS * 100)) * 100) / (60 * 60) AS DECIMAL_DEGREES
  FROM TEST_DMS

At the very least these agree with the results returned by my trusty, rusty HP-41C. :-)

Share and enjoy.

Answer 2

I worked on a project...I used script to solve issue on DMS conversion on oracle db

SELECT 
CASE
WHEN LENGTH(longitude)=7 THEN ROUND (cast(substr(longitude, 1, 1) as numeric(10,5)) +  cast(substr(longitude, 3, 2) as numeric(10,5))/60 +  cast(substr(longitude, 6, 2) as numeric(10,5))/3600,4) 
WHEN LENGTH(longitude)=8  THEN ROUND (cast(substr(longitude, 1, 2) as numeric(10,5)) +  cast(substr(longitude, 4, 2) as numeric(10,5))/60 +  cast(substr(longitude, 7, 2) as numeric(10,5))/3600,4)
ELSE
ROUND (CAST (longitude as Numeric(10,5)),4)
END AS LONGITUDE,
CASE
WHEN LENGTH(latitude)=7 THEN ROUND (cast(substr(latitude, 1, 1) as numeric(10,5)) +  cast(substr(latitude, 3, 2) as numeric(10,5))/60 +  cast(substr(latitude, 6, 2) as numeric(10,5))/3600,4) 
WHEN LENGTH(latitude)=8  THEN ROUND (cast(substr(latitude, 1, 2) as numeric(10,5)) +  cast(substr(latitude, 4, 2) as numeric(10,5))/60 +  cast(substr(latitude, 7, 2) as numeric(10,5))/3600,4)
ELSE
ROUND (CAST (latitude as Numeric(10,5)),4)
END AS LATITUDE