Type casting and conversion in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of CAST with string to integer conversion

What is the output of the following MySQL query?

SELECT CAST('123abc' AS UNSIGNED) AS result;

MySQL

SELECT CAST('123abc' AS UNSIGNED) AS result;

A 123abc

B 0

C Syntax error

D 123

💡 Hint

MySQL converts strings to numbers by reading digits from the start until a non-digit is found.

MySQL reads the string from left to right and converts the initial numeric part to an integer. Here, '123abc' converts to 123.

❓ query_result

intermediate

Result of CONVERT with incompatible type

What is the result of this MySQL query?

SELECT CONVERT('abc123', SIGNED) AS result;

MySQL

SELECT CONVERT('abc123', SIGNED) AS result;

A abc123

B 123

C 0

D NULL

💡 Hint

When the string does not start with digits, MySQL returns zero for numeric conversion.

Since 'abc123' starts with letters, MySQL cannot convert it to a number and returns 0.

📝 Syntax

advanced

Identify the correct syntax for casting to CHAR

Which of the following MySQL queries correctly casts the integer 100 to a CHAR type?

A SELECT CAST(100 AS CHAR);

B SELECT CAST(100 TO CHAR);

C SELECT CONVERT(100, CHAR);

D SELECT CONVERT(100 AS CHAR);

💡 Hint

CAST uses AS keyword, CONVERT uses comma to separate arguments.

CAST syntax requires AS keyword. CONVERT uses comma without AS. Option A uses correct CAST syntax.

❓ query_result

advanced

Effect of implicit conversion in arithmetic operation

What is the output of this MySQL query?

SELECT '10' + 5 AS result;

MySQL

SELECT '10' + 5 AS result;

A 15

B '105'

C 10 5

D Error

💡 Hint

MySQL converts strings to numbers automatically in arithmetic expressions.

The string '10' is implicitly converted to number 10, then added to 5, resulting in 15.

🧠 Conceptual

expert

Understanding behavior of CAST with floating point to integer

Consider the query:

SELECT CAST(123.987 AS SIGNED) AS result;

What is the value of result?

MySQL

SELECT CAST(123.987 AS SIGNED) AS result;

A 124

B 123

C 123.987

D Error

💡 Hint

Casting from float to integer truncates the decimal part in MySQL.

MySQL truncates the decimal part when casting float to integer, so 123.987 becomes 123.