How to convert character data in MySQL to numbers

MySQL is currently the most commonly used relational database management system and one of the most commonly used backend database technologies by website developers. When using a MySQL database, if we want to perform some numerical calculations on a certain column of data in the table, we usually need to convert it to numeric format. This article explains how to convert character data in MySQL to numbers.

1. Using the CAST function

CAST is a function in MySQL that is used to convert values ​​of a certain data type to other data types. The following is an example of converting a character string to an integer:

SELECT CAST('123' AS SIGNED INTEGER);

Convert the character string '123' to an integer number 123 by adding AS SIGNED INTEGER. If you want to convert a character string to a floating-point number, you can use the following statement:

SELECT CAST('123.45' AS DECIMAL(10,2));

In the DECIMAL function, 10 is the total number of digits in the number, and 2 is the number of decimal digits. In this way, the character string '123.45' is converted into a floating-point number 123.45.

2. Using the CONVERT function

The CONVERT function is also a function in MySQL that can convert values ​​of a certain data type into other data types. The following is an example of converting a character string to an integer:

SELECT CONVERT('123', SIGNED INTEGER);

Convert the character string '123' to the integer number 123 by adding SIGNED INTEGER. If you want to convert a character string to a floating-point number, you can use the following statement:

SELECT CONVERT('123.45', DECIMAL(10,2));

In the DECIMAL function, 10 is the total number of digits in the number, and 2 is the number of decimal digits. In this way, the character string '123.45' is converted into a floating-point number 123.45.

3. Use ASCII code

ASCII code is a character encoding method used internally by the computer. Each character corresponds to a numerical value. In MySQL, we can use ASCII code to convert character type strings to numeric types. The following is an example of converting a character string to an integer:

SELECT ASCII('2')-48;

Convert the character string '2' to an integer number 2. Because the code value of the number 0 in the ASCII code is 48, subtracting 48 can get the actual value of the number.

This method can also be used to convert multi-digit strings into numbers. For example, convert the character string '123' into the integer number 123:

SELECT (ASCII('1')-48)*100 + (ASCII('2')-48)*10 + (ASCII('3')-48);

The above three methods can convert character data in MySQL into numbers. Just choose the appropriate method according to the actual situation.

The above is the detailed content of How to convert character data in MySQL to numbers. For more information, please follow other related articles on the PHP Chinese website!