Convert Strings to Integers in MySQL

In MySQL, there are cases where you may need to convert a string to an integer, especially when data is stored as text but you need to perform arithmetic operations. MySQL provides functions such as CAST() and CONVERT() to change data types and make these conversions seamless.

In this tutorial, we will cover how to convert strings to integers, and demonstrate how to use CAST() and CONVERT() in MySQL with examples.

Converting Strings to Integers in MySQL

MySQL allows you to convert strings to integers using the CAST() and CONVERT() functions. These functions help to ensure compatibility between different data types and enable operations that require integer values.

Using CAST() to Convert Strings to Integers

The CAST() function can convert data from one type to another. To convert a string to an integer, you can specify CAST(string AS UNSIGNED) or CAST(string AS SIGNED) to explicitly cast the string to an integer.

</>
Copy
CAST(string AS SIGNED);
CAST(string AS UNSIGNED);

Using CONVERT() to Convert Strings to Integers

Alternatively, you can use the CONVERT() function to change a string to an integer. This function works similarly to CAST(), with a syntax that specifies the target data type.

</>
Copy
CONVERT(string, SIGNED);
CONVERT(string, UNSIGNED);

Setting Up a Sample Table

To demonstrate conversions, we’ll create a transactions table containing string data that represents numbers.

1. Create a new database called test_db (if it doesn’t exist):

</>
Copy
CREATE DATABASE IF NOT EXISTS test_db;

2. Use the test_db database:

</>
Copy
USE test_db;

3. Create a transactions table with a transaction_id and an amount column where amount is stored as a string:

</>
Copy
CREATE TABLE transactions (
    transaction_id INT PRIMARY KEY AUTO_INCREMENT,
    amount VARCHAR(10)
);

4. Insert sample data with amounts as strings:

</>
Copy
INSERT INTO transactions (amount)
VALUES ('100'), ('250'), ('300.5'), ('450'), ('500.75');
Convert Strings to Integers in MySQL - Setup database for examples

With this setup, we can try different conversion examples with the transactions table.

Examples of Converting Strings to Integers

Now that the table is ready, let’s look at examples of converting the amount column from string to integer format.

transactions table

1. Using CAST() to Convert Amount to Integer

To convert the amount column from a string to an integer using CAST():

</>
Copy
SELECT amount, CAST(amount AS SIGNED) AS amount_integer
FROM transactions;

This query converts each amount from string format to an integer, returning the integer value in the amount_integer column. For example, “300.5” becomes 300, as casting to an integer truncates decimal values.

Convert Strings to Integers in MySQL - Example - Using CAST() to Convert Amount to Integer

2. Using CONVERT() to Convert Amount to Integer

You can also use CONVERT() to achieve the same result:

</>
Copy
SELECT amount, CONVERT(amount, SIGNED) AS amount_integer
FROM transactions;

This query provides the same output as the previous example, converting amount to an integer while truncating any decimal values in the string.

Convert Strings to Integers in MySQL - Example - Using CONVERT() to Convert Amount to Integer

3. Using CAST() for Calculations on String-Based Numbers

To perform calculations on the amount column values after converting them to integers, such as calculating a 10% increase:

</>
Copy
SELECT amount, CAST(amount AS SIGNED) * 1.1 AS increased_amount
FROM transactions;

This query converts amount to an integer and then calculates a 10% increase. For example, “250” becomes 275.0 after the calculation.

Convert Strings to Integers in MySQL - Example - Using CAST() for Calculations on String-Based Numbers

By using CAST() or CONVERT(), you can effectively handle strings that represent numbers, converting them to integers for mathematical operations in MySQL.