How to Convert Varchar to Float in MySQL

Written by andy carr
  • Share
  • Tweet
  • Share
  • Email

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

Instructions

  1. 1

    Go to a MySQL prompt so that commands can be entered by typing the following:

    mysql>

    From here, varchar values can be converted into floating point numbers.

  2. 2

    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.

  3. 3

    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):

    1.8215376320334e+16

  4. 4

    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.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.