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


  1. 1

    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.

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


  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

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

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