MySQL: Convert Strings to Numbers with CAST & CONVERT

1. Introduction

When managing a database in MySQL, you’ll frequently encounter situations such as “data that you want to handle as numbers is stored as string type” or “you can’t perform calculations or aggregations because it’s still a string.” For example, data imported from Excel or CSV is often stored as VARCHAR or CHAR types even though it’s numeric. As is, you can’t perform numerical operations like sums, averages, or comparisons smoothly, and it brings your SQL writing to a halt.

On the other hand, there are also cases where you want to handle numeric data as strings. For example, padding IDs or codes with leading zeros, or concatenating them with other columns as text for display. In such situations, you need to convert numeric types to string types.

In this way, “String and Numeric Type Conversion” is one of the fundamental skills for freely manipulating data in MySQL. By mastering type conversions appropriately, you can maintain data integrity while enabling flexible aggregation and processing.

In this article, we’ll explain clearly, incorporating on-site know-how, the methods for converting between strings and numbers in MySQL, how to use them, as well as common pitfalls and best practices. This content is useful not only for beginners but also for practitioners who write SQL on a daily basis, so please refer to it.

2. Types of Type Conversion in MySQL

MySQL provides several convenient functions for converting data types. Here, we will explain the two patterns: “explicit type conversion” and “implicit type conversion,” along with their representative methods.

2.1 Explicit Type Conversion

Explicit type conversion is a method where you clearly specify in SQL to convert a value to a certain type. The following two functions are commonly used.CAST FunctionCAST() is a standard SQL function that converts a specified value to a specified type. It is widely used in MySQL as well.

SELECT CAST('123' AS SIGNED);

In this example, the string '123' is converted to a “signed integer type (SIGNED)”. It can also be converted to various other types such as UNSIGNED (unsigned integer), DECIMAL (decimal), CHAR (string), DATE (date), and so on.CONVERT FunctionCONVERT() is also a function for converting values to other data types. The format is as follows.

SELECT CONVERT('456' , UNSIGNED);

In this example, the string '456' is converted to an “unsigned integer type (UNSIGNED)”. The main difference from CAST() is that CONVERT() can also be used for character set conversion.

2.2 Implicit Type Conversion

Implicit type conversion is a mechanism where MySQL automatically converts types when performing operations or comparisons in SQL.
For example, when adding a number and a string, MySQL automatically converts the string to a number and performs the calculation.

SELECT 1 + '2';
-- The result is 3

Similarly, when you want to concatenate a number as a string.

SELECT CONCAT(10, ' apples');
-- The result is '10 apples'

Implicit type conversion is convenient, but it can lead to unintended results, so it is recommended to use explicit type conversion in complex processing or important scenarios.

3. Practical Examples of String to Numeric Conversion

In MySQL, if numeric values are stored as strings (CHAR type or VARCHAR type), calculations or numeric comparisons cannot be performed directly. To properly aggregate and analyze such data, it is necessary to convert the strings to numeric types. Here, we introduce commonly used conversion methods and precautions.

3.1 Conversion Using the CAST Function

The most basic method is to use the CAST() function. For example, to convert the string '100' to an integer, write it as follows.

SELECT CAST('100' AS SIGNED) AS numeric_result;
-- Result: 100 (integer)

SIGNED converts to a signed integer, and UNSIGNED converts to an unsigned integer. For data with decimal points, DECIMAL or FLOAT can also be used.

SELECT CAST('123.45' AS DECIMAL(10,2)) AS decimal_result;
-- Result: 123.45

3.2 Conversion Using the CONVERT Function

The CONVERT() function can also be used in almost the same way.

SELECT CONVERT('200', SIGNED) AS conversion_result;
-- Result: 200

Both produce the same result, but CAST() has high portability as standard SQL, so it is recommended to choose CAST() when in doubt.

3.3 Implicit Conversion by Operations

When calculating numeric types and string types within SQL arithmetic expressions, the string is automatically converted to a numeric value. For example, it can be used like this.

SELECT '50' + 25 AS total;
-- Result: 75

This mechanism can also be applied to aggregate functions. For example, if you pass a string-type column directly to SUM(), MySQL automatically converts it to numeric and sums it.

SELECT SUM(amount) FROM sales_data;
-- Even if the amount column is VARCHAR, it automatically sums numerically

3.4 Be Careful with Zero-Padded Strings and Non-Numeric Values

Zero-padded strings (e.g., ‘000100’) can also be converted to numeric.

SELECT CAST('000100' AS SIGNED) AS conversion_result;
-- Result: 100

However, be careful if characters other than numbers are mixed in. If you convert like CAST('abc123' AS SIGNED), and there is no numeric part from the beginning, it becomes 0. Depending on the data quality, it is important to perform input value validation before conversion.

3.5 Practical Usage Examples

• Aggregating sales or amount data that has been stringified during import from Excel or CSV
• When sorting IDs stored as string types despite being numeric, such as customer codes, in numeric order
• When you want to sort date strings saved in YYYYMMDD format in date order (described later)

4. Practical Examples of Numeric to String Conversion

In MySQL, there are many situations where you need to handle numeric data as strings. For example, when you want to display IDs or codes with zero padding, or combine numeric data with other text to create messages. This section introduces representative methods for converting numbers to strings and practical application examples.

4.1 Conversion Using the CAST Function

To explicitly convert numeric types (such as INT or DECIMAL) to string types, use the CAST() function.

SELECT CAST(123 AS CHAR) AS string_result;
-- Result: '123'

With this method, you can easily concatenate numeric columns as strings with other strings.

4.2 Conversion Using the CONVERT Function

CONVERT()The function can achieve the same result.

SELECT CONVERT(456, CHAR) AS conversion_result;
-- Result: '456'

There is almost no difference from CAST(), but from the perspective of standard SQL, CAST() is somewhat recommended.

4.3 Implicit Conversion Through Operations

In MySQL, when concatenating numbers and strings using the CONCAT() function or similar, the number is automatically converted to a string.

SELECT CONCAT(2024, ' year') AS year_display;
-- Result: '2024 year'

Such implicit type conversions are commonly used in everyday report generation and data shaping.

4.4 Common Practical Use Cases

• Generating Zero-Padded IDs
  When you want to display a numeric ID with 5 digits, use it in combination with LPAD().

  SELECT LPAD(CAST(id AS CHAR), 5, '0') AS padded_id
  FROM users;
  -- For id=7, it becomes '00007'

• String Concatenation for Dates or Amounts

  SELECT CONCAT('The total amount is ', CAST(total AS CHAR), ' yen.') AS message
  FROM orders;
  -- For total=1500, 'The total amount is 1500 yen.'

4.5 Notes

Converting a number to a string may look the same, but it changes the behavior of sorting (sort order) and comparison operations.
For example, when sorting as strings, ’20’ comes before ‘100’ (lexicographical order), so it is important to choose the method based on the use case.

5. Advanced Use Cases Using Type Conversion

Type conversion goes beyond mere conversion of numbers or strings and offers various practical applications. Here, we introduce commonly used application examples and their key points.

5.1 Comparison and Conversion of Date Strings

If dates are stored in the database as numeric or string types like YYYYMMDD, simple string comparison won’t sort them as expected.
In this case, by converting to a number using CAST(), you can sort or compare in the correct date order.

SELECT *
FROM events
ORDER BY CAST(event_date AS UNSIGNED);
-- Data like '20240501', '20240502', ... will be sorted in date order

Additionally, by combining with the REPLACE() function, you can convert hyphen-separated dates like '2024-05-01' to integers.

SELECT CAST(REPLACE('2024-05-01', '-', '') AS UNSIGNED);
-- Result: 20240501

5.2 Sorting ENUM Types or Code Values

For example, if you want to sort ENUM types or code values that have numeric meaning in numeric order rather than dictionary order, sorting after converting to numeric type with CAST() will give an intuitive order.

SELECT *
FROM products
ORDER BY CAST(product_code AS UNSIGNED);

5.3 Use in Aggregation and Data Formatting

For example, when aggregating sales data where amounts are stored as VARCHAR type, using SUM(CAST(amount AS SIGNED)) allows for accurate total calculation.

SELECT SUM(CAST(sales_amount AS SIGNED)) AS total_sales
FROM sales_data;

5.4 Preventing Issues Through Precision and Type Selection

Especially for decimals or large numbers, converting to DECIMAL type preserves precision.
Additionally, the distinction between SIGNED and UNSIGNED is important, and you need to choose based on whether the data includes negative values.

SELECT CAST('1234.567' AS DECIMAL(10, 3));
-- Result: 1234.567

5.5 Detecting Errors and Invalid Data

If unintended NULL or 0 occurs during type conversion, it might be a sign of invalid data.
For example, if a string that cannot be converted to a number is included, CAST() may result in 0 or NULL.
In such cases, it’s important to check the conversion results and connect to error handling or data cleansing.

SELECT original, CAST(original AS SIGNED) AS converted
FROM test_data
WHERE CAST(original AS SIGNED) = 0 AND original <> '0';
-- Extract only data that cannot be converted to numbers

6. Differences Between CAST and CONVERT

When performing type conversion in MySQL, you will mainly use either the CAST function or the CONVERT function. Both may seem similar, but there are differences in their detailed usage and features. In this chapter, we will explain the differences between them and the key points for choosing which to use.

6.1 Basic Differences

• CAST Function
  CAST(value AS type) syntax explicitly converts a value to the specified type.
  It is defined in standard SQL and can be used with the same syntax in many databases.

  SELECT CAST('123' AS SIGNED);

• CONVERT Function
  CONVERT(value, type) syntax, which also converts a value to the specified type.
  In MySQL, it can be used for both data type conversion and character set conversion.

  SELECT CONVERT('123', SIGNED);
  -- Type conversion
  SELECT CONVERT('hello' USING utf8mb4);
  -- Character set conversion

6.2 Compatibility with Standard SQL

CAST is an international SQL standard, so it has the advantage of being easy to port to other databases (PostgreSQL, SQL Server, Oracle, etc.). On the other hand, CONVERT has MySQL-specific extensions and behaviors, and especially for character set conversion, there is a dedicated syntax like CONVERT(expr USING charset_name).

6.3 Key Points for Choosing Which to Use

• For type conversion (numbers, strings, dates, etc.)
  It is generally safer to use CAST. Since it is standard SQL, it has high portability, and you can be assured when migrating to another database in the future.
• For character set conversion (e.g., sjis to utf8mb4)
  You need to use CONVERT in the form CONVERT(expr USING charset_name).
• For MySQL-specific features or special cases
  CONVERT may be more flexible in some cases, but considering generality, it is better to start with CAST and consider CONVERT only when necessary.

6.4 Sample Comparison

-- Type conversion using CAST (converting to integer)
SELECT CAST('456' AS SIGNED);

-- Type conversion using CONVERT
SELECT CONVERT('456', SIGNED);

-- Character set conversion using CONVERT
SELECT CONVERT('Hello' USING utf8mb4);

6.5 Notes

• If the conversion fails with either, it may return NULL or 0.
• Character set conversion cannot be done with CAST.
• The behavior may differ depending on the SQL mode or version, so don’t forget to verify the operation in both the development and production environments.

7. Notes and Best Practices

When utilizing string and numeric type conversions in MySQL, there are several unexpected pitfalls and points to note. Here, we introduce best practices for preventing troubles and utilizing type conversions safely and accurately.

7.1 Errors or NULL/0 Occurrence Due to Invalid Conversions

When performing type conversion, if the source value is not in the correct format, unintendedNULL or0 may be returned.

SELECT CAST('abc' AS SIGNED) AS result;
-- Result: 0 (MySQL's default behavior)

As such, attempting to convert a string that is not a number to a numeric value results in 0, so check in advance if there are any invalid values mixed in the original data.
Additionally, depending on the SQL mode, it may result in an error or NULL, so it is important to confirm the settings in the production environment.

7.2 Selection of Precision and Sign (SIGNED/UNSIGNED)

• Convert data containing decimal points to DECIMAL type or FLOAT type
• If there is a possibility of negative values, specify SIGNED; if it is only positive integers, specify UNSIGNED

Select the appropriate type according to the use case.

7.3 Impact on Indexes

WHERE clause orORDER BY clause using type conversion (especiallyCAST orCONVERT) can make indexes ineffective and degrade performance.

SELECT * FROM users WHERE CAST(user_id AS SIGNED) = 1000;
-- Even if there is an index on user_id, it often won't be used

In cases with large amounts of data or where speed is important for search conditions, it is ideal to design with unified original data types, avoiding conversions as much as possible.

7.4 Do Not Rely Too Much on Implicit Type Conversion

MySQL’s implicit type conversion is convenient, but it often leads to unintended behavior, so for important processes, using explicitCAST orCONVERT is safer.

SELECT '100a' + 20;
-- Result: 100 (Only the leading numeric part of '100a' is used)

To avoid unexpected bugs or data inconsistencies, make it a habit to use explicit type conversions.

7.5 Type Checking at the Data Input Stage

At the database design stage, save ‘numbers as numeric types’ and ‘strings as string types’, and aiming for a design that requires no type conversion as much as possible is also the basics of avoiding troubles.

8. Summary

Type conversion between strings and numbers in MySQL is an unavoidable topic in daily data processing, aggregation, and system operations. This article has covered a wide range from the basics to advanced applications and important precautions.

String to numeric conversion uses explicit methods like CAST and CONVERT, and in practice, implicit type conversions are also commonly used. However, considering data quality and security, it’s important to aim for explicit conversions. Conversely, numeric to string conversion is also useful in many scenarios, such as ID formatting, code shaping, and message output.

Furthermore, type conversion techniques, such as applications to date types, aggregation, sorting, and error detection, directly contribute to solving various practical challenges. However, type conversions can have unexpected pitfalls and impacts on performance, so be sure to follow the points listed in “Precautions and Best Practices” for operations.

By mastering type conversions correctly, the scope of data operations with MySQL greatly expands.
I hope this article serves as a helpful resource for your daily work and learning.

9. Frequently Asked Questions (FAQ)

Q1. “What happens when you CAST a string like ‘abc’ to a numeric value?”

In MySQL, when attempting to convert a string to a numeric type (such as SIGNED or UNSIGNED), it returns the numeric part if it’s at the beginning; otherwise, it returns 0.

SELECT CAST('abc' AS SIGNED); -- Result is 0
SELECT CAST('123abc' AS SIGNED); -- Result is 123

However, depending on the SQL mode (for example, STRICT_TRANS_TABLES), it may result in an error or NULL.

Q2. Which should you use, CAST or CONVERT?

For basic type conversions (numeric to/from string or date types, etc.), using CAST, which complies with standard SQL, is recommended. On the other hand, for character set conversions, you need to use the CONVERT function with the USING clause. Choose based on the purpose.

Q3. Is implicit type conversion sufficient?

Implicit type conversion works for small-scale queries or tests, but for important aggregations or system development, explicit type conversion (CAST or CONVERT) is recommended. To prevent unintended behavior or bugs, make it a habit to describe conversions explicitly as much as possible.

Q4. Does using type conversion in WHERE or ORDER BY clauses make indexes ineffective?

Yes. Applying CAST or CONVERT to a column may prevent the index set on that column from being used.
For fast searches on large amounts of data, unify the column types in advance, or use subqueries or generated columns to optimize.

Q5. Is type conversion safe for decimals or large numbers?

It is possible to maintain precision by using conversions to DECIMAL or FLOAT types. However, rounding errors, precision loss, or truncation may occur during conversion, so it’s important to take measures such as specifying the necessary number of digits.

Q6. What’s the difference between sorting as strings and sorting as numbers?

When sorting as strings, “2” comes before “10” (in dictionary or alphabetical order), so if you want numeric order, always perform the type conversion before sorting.