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!
