Convert varchar to float without losing data

Question

I have a list of latitude and longitudinal values in my table of type varchar. I want them to be float. How do I convert varchar to float?

For example:
36.26056695 of type varchar should be converted 36.26056695 of type float.

-99.11080846 of type varchar should be converted -99.11080846 of type float

See this for MySQL : https://dev.mysql.com/doc/refman/5.6/en/precision-math-decimal-characteristics.html — PaulF, Jul 16 '15 at 15:51
Please clarify: Are you trying to `alter` the column definitions to a `float` or manipulating them in a `select` statement without loss of precision? — Stavr00, Jul 16 '15 at 17:08
MySQL's `FLOAT` is good to about 1.7 meters or 5.6 feet. Is that OK? — Rick James, Jul 28 '15 at 18:32

score 2 · Answer 1 · answered Jul 16 '15 at 16:00

2

Use Cast operator

For Example

SELECT CAST( '36.26056695' AS float) FROM TABLE

answered Jul 16 '15 at 16:00

devil1212

13
2

I have a list 10,000 values. I want to change the entire column using a single command – Santosh Reddy Jul 16 '15 at 16:25
ALTER TABLE tbl MODIFY COLUMN col FLOAT NOT NULL; – Rick James Jul 28 '15 at 19:50
TO use this cast function it increase query time, any other solution – Md Hasibur Rahaman Sep 01 '15 at 13:09

Uueerdo · Answer 2 · 2015-07-16T16:14:20.380

That depends on what you mean by "losing data".

If you mean without loss of precision, "float" will be unreliable; it can only reliably store multiples of a power of two (.125, .25, .375, etc....)

If you just mean convert the fields without them going blank, the best bet is something like this:

ALTER TABLE `the_table` 
   ADD COLUMN `dec_val` NOT NULL DECIMAL(11, 8)
;

UPDATE `the_table` 
   SET `dec_val` = CAST(`str_val` AS DECIMAL(11, 8))
;

ALTER TABLE `the_table` 
   DROP COLUMN `str_val`
;

-- The below is only needed if you wanted to do something like rename the 
-- new column to the same name as the original. Otherwise, you could just
-- have created the new column with the desired name.
ALTER TABLE `the_table` 
   CHANGE COLUMN `dec_val` `desired_column_name` NOT NULL DECIMAL(11, 8)
;

You could use FLOAT instead of DECIMAL of course, but this way preserves the exact values.

Note: Just changing the original column's definition to DECIMAL(11, 8) might work, but this allows you to inspect the converted values (after the UPDATE) before you drop the original ones.

8 decimal places is overkill -- it's precise enough to distinguish one flake of dandruff from another. 6 places is precise enough to distinguish two people embraced in a kiss (16cm / 6 inches). — Rick James, Jul 28 '15 at 18:36
@RickJames I was just going with the data the question provided. — Uueerdo, Jul 28 '15 at 18:37

Convert varchar to float without losing data

2 Answers2