CODEWITHSUNDEEP
mysqltype-conversion
JYelton
JYelton

Reputation: 36546

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.

Upvotes: 65

Views: 178412

Answers (3)

Justas
Justas

Reputation: 5962

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

Upvotes: 22

zloctb
zloctb

Reputation: 11194

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)

Upvotes: 25

JYelton
JYelton

Reputation: 36546

It turns out I was just missing DECIMAL on the CAST() description:

DECIMAL[(M[,D])]

Converts a value to DECIMAL data type. The optional arguments M and D specify the precision (M specifies the total number of digits) and the scale (D specifies the number of digits after the decimal point) of the decimal value. The default precision is two digits after the decimal point.

Thus, the following query worked:

UPDATE table SET
latitude = CAST(old_latitude AS DECIMAL(10,6)),
longitude = CAST(old_longitude AS DECIMAL(10,6));

Upvotes: 99

Related Questions