Converting Strings to Integers in MySQL

In MySQL, at some point you’ll probably need to convert string values to integers for one data operation or another. This post covers how to achieve this conversion using the CAST() and CONVERT() functions.

How to use CAST() in MySQL?

You can convert a string to an integer in MySQL using the CAST() function:

CAST(string_value AS SIGNED INTEGER)

Replace string_value with the column or string literal you intend to convert to an integer.

Example:

SELECT CAST('123' AS SIGNED INTEGER);

This query returns 123 as an integer.

How to use CONVERT() in MySQL?

Another method to perform this conversion in MySQL is by using the CONVERT() function. Here's the syntax:

CONVERT(string_value, SIGNED INTEGER)

Replace string_value with the value you want to convert.

Example:

SELECT CONVERT('456', SIGNED INTEGER);

This query also returns 456 as an integer.

Stuff to remember

• Make sure the string consists only of numeric characters; otherwise, the conversion might yield unexpected results or errors.
• Watch out for potential data truncation. If the string represents a number larger than the maximum value for an integer, the result may be truncated or produce an error.
• Always test your conversion queries with sample data to verify the accuracy of the results.