Q:

Convert DM lat long to decimal with MySql

Im trying to convert my current position Lat: S3251 Long: W07122 position to degree format.
Ie Degrees Lat Long     -32.8500000, -071.3666667  
This data is in mysql column like this:
Name: Position:
Ship1    S3251 W07122
I am trying to figure out mysql query to display it like this:  
Name: Lat: Long:
Ship1   -32.8500000   -071.3666667  
So I can use it on Google Maps, which requires Lat Long in decimals.
How do I convert this using mysql?
Thanks

A:

You can use a few functions to separate the data value to be used in the formula. Example query below:
SELECT CASE WHEN latt LIKE 'S%' THEN (SUBSTRING(latt,2,2)+(RIGHT(latt,2)/60)+(0/3600))*-1 
            WHEN latt LIKE 'N%' THEN SUBSTRING(latt,2,2)+(RIGHT(latt,2)/60)+(0/3600) END AS 'Latitude',
       CASE WHEN longt LIKE 'W%' THEN (SUBSTRING(longt,2,3)+(RIGHT(longt,2)/60)+(0/3600))*-1 
            WHEN longt LIKE 'E%' THEN SUBSTRING(longt,2,3)+(RIGHT(longt,2)/60)+(0/3600) END AS 'Longitude'
FROM test1;

As you can see, I'm using CASE expression to identify the direction, SUBSTRING() and RIGHT() functions to separate the degrees and minutes value for the formula.
P/S: Alternatively you can replace the SUBSTRING() with MID(). Both operates the same and the latter is considerably shorter to write.
This is using a formula from the first link you've provided in the comment:

I've made an example fiddle here: https://www.db-fiddle.com/f/cJjaArst8aZmQesyWDDXim/1

