It is not always possible to gather data in the exact format required by a program. Occasionally, it is necessary to convert data from one format to another. Floating point numbers are often held in a MySQL database as character fields. As a result, they must be converted to their floating point number equivalent. This can be achieved in a single select statement by formatting the output so that MySQL assumes that the result is a floating point number.
- Skill level:
- Moderately Easy
Other People Are Reading
Go to a MySQL prompt so that commands can be entered by typing the following:
From here, varchar values can be converted into floating point numbers.
Type in a select statement, formatting the varchar as a floating point number by placing "0.0" after the varchar string to be converted. For example:
sql> Select '1.5'+0.0;
This is displayed as 1.5 in MySQL as expected.
Enter a large floating point number as a float, and consider how the floating point number is rounded off, as in the following example:
mysql> SELECT '18215376320333968'+0.0;
The result of this is the number below (due to rounding):
Include letters in the floating point number to be converted as follows:
Select 'abcdef1234'+5.5 ;
This results in an output of "5.5" as the letters are ignored.
Tips and warnings
- With respect to rounding, the result may be different on some systems due to the architecture and hardware of the machine performing the calculations.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for