[mysql] How can I convert a string to a float in mysql?

I have a table containing latitude and longitude values stored as strings (VARCHAR) which I'd like to convert to FLOAT (10,6).

However there doesn't appear to be a straightforward way to do this using CAST() or CONVERT().

How can I convert these columns easily? This is a one-time conversion.

mysql> SELECT CAST(4 AS DECIMAL(4,3));
+-------------------------+
| CAST(4 AS DECIMAL(4,3)) |
+-------------------------+
|                   4.000 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST('4.5s' AS DECIMAL(4,3));
+------------------------------+
| CAST('4.5s' AS DECIMAL(4,3)) |
+------------------------------+
|                        4.500 |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST('a4.5s' AS DECIMAL(4,3));
+-------------------------------+
| CAST('a4.5s' AS DECIMAL(4,3)) |
+-------------------------------+
|                         0.000 |
+-------------------------------+
1 row in set, 1 warning (0.00 sec)

This will convert to a numeric value without the need to cast or specify length or digits:

STRING_COL+0.0

If your column is an INT, can leave off the .0 to avoid decimals:

STRING_COL+0