Skip to content
Tutorials Center
My learning

Topics

Lesson 31 of 75 ยท Querying with MySQL

CAST, CONVERT and Implicit Conversion

Learn MySQL type conversion: CAST and CONVERT, CONVERT USING for character sets, STR_TO_DATE, and the implicit coercion surprises that break comparisons and indexes.

Every value in MySQL has a type, and sooner or later two values of different types meet: a string from a form field is added to a number, a text column is compared with an integer, or a date arrives as 09/03/2026. MySQL does not refuse. It quietly converts one side so the operation can proceed. This implicit conversion is convenient, and it is also the source of some of the most confusing bugs in MySQL.

This lesson shows how to convert explicitly with CAST, CONVERT and STR_TO_DATE, and what MySQL does on its own when you do not. For the general idea of types and casting in standard SQL, see Types and CAST. The results below were produced on a real MySQL 8.4 server. The live examples run in SQLite, which converts in similar but not identical ways, and each one says where MySQL differs.

Two ways to convert

Explicit You write CAST or CONVERT. The intent is clear.
Implicit MySQL converts for you. Quiet, and sometimes wrong.

Prefer explicit conversions in code you want to trust.

1 / 2

CAST: the standard way

CAST(expression AS type) converts a value to a target type. MySQL accepts a limited list of target types: SIGNED, UNSIGNED, DECIMAL(p, s), CHAR(n), BINARY, DATE, DATETIME, TIME, JSON, FLOAT, DOUBLE and a few others. Note the names: you cast to SIGNED or UNSIGNED, not INT.

SQL
SELECT CAST('123' AS SIGNED),         -- 123
       CAST('12.7' AS DECIMAL(5,1)),  -- 12.7
       CAST(12.7 AS SIGNED),          -- 13  (rounded, not cut off)
       CAST('2026-03-09' AS DATE),    -- 2026-03-09
       CAST(5 AS CHAR),               -- '5'
       CAST(-1 AS UNSIGNED);          -- 18446744073709551615

Three details are worth remembering:

  • Casting a decimal number to an integer rounds it. CAST(12.7 AS SIGNED) is 13. Many languages truncate instead, and so does SQLite, so do not assume.
  • Negative to UNSIGNED wraps around. -1 becomes the largest 64-bit value. It does not raise an error.
  • Casting a number that is too large also wraps or clips silently in some cases: CAST('9999999999999999999' AS SIGNED) returns the negative number -8446744073709551617. Choose the target type for the biggest value you expect.

You can also give a length to CHAR: CAST('abc' AS CHAR(2)) returns ab, with warning 1292 ("Truncated incorrect CHAR(2) value").

What happens to text that is not a number

When a string that is not a clean number is cast, MySQL reads the leading numeric part and ignores the rest. If there is no leading number the result is 0. In both cases you get a warning rather than an error:

SQL
SELECT CAST('12abc' AS SIGNED);  -- 12, warning 1292: Truncated incorrect INTEGER value: '12abc'
SELECT CAST('abc' AS SIGNED);    -- 0,  warning 1292: Truncated incorrect INTEGER value: 'abc'
SHOW WARNINGS;

Because these are only warnings, application code usually never sees them. If bad input matters, validate it first (a regular expression with REGEXP, or in your application) instead of relying on CAST to reject it.

The next example runs a few of these conversions in the in-browser SQLite engine. Its CAST also keeps the leading digits of '12abc' and gives 0 for 'abc', so those lines match MySQL. The line with 12.7 is where it differs: SQLite cuts off the fraction instead of rounding.

SQL CAST in SQLite (compare with the MySQL results above)

Press Run (or Ctrl + Enter) to see the result here.

The first query returns 123, 12 and 0. The second returns 12, -12 and 13.0: SQLite truncates unless you call ROUND. In MySQL, write ROUND(x) or TRUNCATE(x, 0) when you need a specific behavior, instead of depending on the cast.

CONVERT: the same, plus character sets

CONVERT(expression, type) is a synonym for CAST(expression AS type):

SQL
SELECT CONVERT('123', SIGNED);                        -- 123
SELECT CONVERT('2026-03-09 10:11:12', DATETIME);      -- 2026-03-09 10:11:12

Its second form has no CAST equivalent. CONVERT(expression USING charset) changes the character set of a string:

SQL
SELECT HEX(CONVERT('รฉ' USING utf8mb4)),  -- C3A9  (two bytes in UTF-8)
       HEX(CONVERT('รฉ' USING latin1)),   -- E9    (one byte in latin1)
       CHARSET(CONVERT('abc' USING latin1));  -- latin1

This is how you repair a comparison between columns with different character sets, or prepare a string for a column that uses another one. Remember that characters that do not exist in the target character set are replaced by ?. The background is in Character Sets and Collations.

Parsing and formatting dates

Text that looks like a date is converted by CAST only when it is in the ISO layout YYYY-MM-DD. For other layouts, STR_TO_DATE(text, format) reads a string according to a format you describe, using the same % codes as DATE_FORMAT:

SQL
SELECT STR_TO_DATE('09/03/2026', '%d/%m/%Y');          -- 2026-03-09
SELECT STR_TO_DATE('March 9, 2026', '%M %e, %Y');      -- 2026-03-09
SELECT STR_TO_DATE('31/02/2026', '%d/%m/%Y');          -- NULL (there is no 31 February)
SELECT STR_TO_DATE('nonsense', '%d/%m/%Y');            -- NULL
SELECT DATE_FORMAT(STR_TO_DATE('09/03/2026 14:05', '%d/%m/%Y %H:%i'), '%Y-%m-%d %H:%i');
-- 2026-03-09 14:05

An impossible or unparsable value gives NULL (plus a warning), which is a safe signal: check for it when you import messy data (Bulk Loading). The reverse direction, turning a date into text, is DATE_FORMAT, covered in Strings, Dates and Math Functions.

SQLite has no STR_TO_DATE, so the stand-in below rearranges the text with substr. It does the same job for one fixed layout and shows the idea: parse to ISO format once, then store and compare ISO dates.

SQL Parsing dd/mm/yyyy text (SQLite stand-in for STR_TO_DATE)

Press Run (or Ctrl + Enter) to see the result here.

The first query returns the three dates in true chronological order: 2026-03-09, 2026-11-15, 2027-01-01. The second sorts the raw text, which puts 01/01/2027 first because 01 sorts before 09 and 15. Dates stored as text in a non-ISO layout cannot be ordered correctly. Convert them once on the way in.

Implicit conversion: what MySQL does without being asked

When an operator receives operands of different types, MySQL converts them according to its rules. The main ones:

  • Arithmetic converts strings to numbers. '10' + 5 is 15. A string with no leading number counts as 0: 'abc' + 1 is 1, with warning 1292. '3 apples' + 2 is 5.
  • Comparing a string with a number compares them as numbers. 1 = '1' is true. So is 1 = '1abc', and so are 0 = 'abc' and 'a' = 0.
  • Comparing two strings compares them as text, even when they look like numbers. '2' < '10' is false, because '2' sorts after '1'. With one side numeric, 2 < '10' is true.
  • The / operator returns a decimal. 1/3 is 0.3333. Use DIV for integer division.

All of these were run on MySQL 8.4:

SQL
SELECT '10' + 5, '3 apples' + 2, 'abc' + 1;      -- 15 | 5 | 1
SELECT 1 = '1', 1 = '1abc', 0 = 'abc';           -- 1 | 1 | 1
SELECT '2' < '10', 2 < '10';                     -- 0 | 1
SELECT '10' = '10.0', 10 = '10.0';               -- 0 | 1

The last line shows the principle in one place: '10' = '10.0' compares two strings and is false, while 10 = '10.0' converts the string to a number and is true.

SQLite is stricter in comparisons. It does not convert a plain text literal when comparing it to an integer literal, so 1 = '1' is false there. Run it to see the contrast, and keep in mind that the MySQL answer is "true".

SQL Coercion in SQLite (several results differ from MySQL)

Press Run (or Ctrl + Enter) to see the result here.

The first query prints 15, 5 and 1. The second prints 0, 0 and 1: in SQLite 1 = '1' is false, while MySQL says true. The third prints 1. The fourth prints 0 and 0.5; in MySQL 1 / 2 would be 0.5000. When two databases disagree like this, the only portable fix is to cast explicitly.

Implicit conversion and indexes

The most expensive conversion bug is not a wrong value but a slow query. Suppose code is a VARCHAR column with an index, and you write:

SQL
SELECT code FROM tickets WHERE code = 100;     -- number compared with a string column

MySQL converts the column to a number for every row, so it cannot use the index to look values up. It has to read the whole index or table. Worse, the comparison has side effects: with the values '100', '1abc', 'abc' and '0' in the table, WHERE code = 0 matches both '0' and 'abc', because 'abc' converts to 0. On the MySQL 8.4 test, EXPLAIN for WHERE code = 100 reported type: index, a full scan of the index, instead of a seek.

The fix is to compare like with like:

SQL
SELECT code FROM tickets WHERE code = '100';   -- string literal, index can be used

The reverse is harmless: if the column is an integer and the literal is a string (WHERE id = '100'), MySQL converts the literal once, and the index still works. The same rule applies to joins: join an INT column to an INT column, not to a VARCHAR holding digits, and keep character sets and collations the same on both sides (Query Optimization in MySQL).

Habits that avoid conversion bugs

  • Give each column the type that matches its meaning, so you rarely compare across types (Choosing Data Types).
  • Compare values of the same type. Pass numbers as numbers from your application, and quote strings for string columns.
  • Use CAST when you must cross types, and say what you mean: CAST(price AS DECIMAL(10,2)), ROUND(x), STR_TO_DATE.
  • Keep strict SQL mode on so out-of-range and bad values in INSERT fail instead of being adjusted (SQL Modes).
  • Look at SHOW WARNINGS after a surprising result. Error 1292 usually means a conversion happened.

What ran live here

The CAST of text, the leading-digit rule, the effect of text sorting, and the explicit numeric comparison ran in SQLite, and its different results (truncating CAST(12.7 AS INTEGER), 1 = '1' being false, integer division) are called out in the text. CONVERT ... USING, STR_TO_DATE, SHOW WARNINGS, the unsigned wrap-around and the index behavior are MySQL-only. They are shown as read-only code, and their results were produced on a MySQL 8.4 server.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does CAST(12.7 AS SIGNED) return in MySQL?

    MySQL rounds a decimal number when it casts to an integer. Some other systems, including SQLite, truncate instead.
  2. 2. Which statement about SELECT 1 = '1abc' in MySQL is true?

    When a string is compared with a number, MySQL converts the string to a number using its leading digits, so '1abc' becomes 1. The result is true, with warning 1292.
  3. 3. Why is SELECT '2' < '10' false in MySQL?

    Two strings are compared character by character, even when they look like numbers. Cast to a numeric type to compare them as numbers.
  4. 4. A VARCHAR column code has an index. Why is WHERE code = 100 slow?

    Comparing a string column with a number forces a conversion of the column on each row. Use the string literal '100' so the index can be used.
  5. 5. Which function turns the text '09/03/2026' into the date 2026-03-09?

    CAST only understands the ISO layout YYYY-MM-DD. STR_TO_DATE reads other layouts using a format string, and returns NULL for impossible dates.

Summary

  • CAST(x AS type) and CONVERT(x, type) convert explicitly. The target types include SIGNED, UNSIGNED, DECIMAL(p,s), CHAR(n), DATE, DATETIME, JSON.
  • Casting a decimal to an integer rounds. A negative to UNSIGNED wraps. Bad text gives its leading number or 0, with warning 1292 instead of an error.
  • CONVERT(x USING charset) changes a string's character set. STR_TO_DATE parses any date layout and returns NULL for impossible dates.
  • Implicit rules: arithmetic and string-versus-number comparisons use numbers, string-versus-string comparisons use text, and / returns a decimal.
  • Comparing a string column with a number converts every row and defeats the index. Compare like with like.
  • Cast explicitly, keep types consistent, and check SHOW WARNINGS when results look odd.

Next: NULL Handling and Comparison Rules.