How can I convert a string to a float in mysql

Question

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

User · Answer

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

User · Answer

mysql gt  SELECT CAST 4 AS DECIMAL 4 3     -------------------------    CAST 4 AS DECIMAL 4 3      -------------------------                      4 000    -------------------------  1 row in set  0 00 sec   mysql gt  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 gt  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

User · Answer

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

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

Examples related to mysql

Examples related to type-conversion