How to convert string to floating point number in mysql

Two conversion methods: 1. Using the CAST() function, just set the string value to the DECIMAL type. The conversion syntax is "CAST("String value" AS DECIMAL(numeric width, decimal) Number of digits));". 2. Using the CONVERT() function, you only need to convert the string type to the DECIMAL type. The conversion syntax is "CONVERT("String value",DECIMAL(Number width, number of decimal places));".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Mysql's two methods of converting strings to floating point numbers

Method 1: Using the CAST() function

The CAST() function converts a value of any type to a value of the specified type. The target type can be one of the following types: BINARY, CHAR, DATE, DATETIME, TIME, DECIMAL, SIGNED, UNSIGNED.

CAST(expression AS TYPE);

You only need to set the parameter TYPE to the "DECIMAL (M, D)" type to convert the string into a floating point number.

The valid value range of the DECIMAL type is determined by M and D. If M is changed and D is fixed, the value range will become larger as M becomes larger.

• Among them, M is called precision, indicating the total number of digits; D is called scale, indicating the number of decimal digits.

• M (1 to 255) and D (1 to 30, and cannot be greater than M-2), respectively represent the display width and number of decimal places.

Example 1:

SELECT CAST("00256.36" AS DECIMAL(7,3));

Example 2:

SELECT CAST("00256.36" AS DECIMAL(25,10));

Method 2: Using the CONVERT() function

The CONVERT() function is used to convert a value from one data type to another. It accepts two parameters, the input value and the type to be converted.

CONVERT( input_value, data_type )

You only need to set the value of the parameter data_type to the "DECIMAL (M, D)" type.

Example:

SELECT CONVERT("00568.364",DECIMAL(7,3));

[Related recommendations: mysql video tutorial]

The above is the detailed content of How to convert string to floating point number in mysql. For more information, please follow other related articles on the PHP Chinese website!