SUBSTRING and LEFT/RIGHT in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this SUBSTRING query?

Consider the following query on a table products with a column product_code containing the value 'ABCD1234'. What will this query return?

SELECT SUBSTRING(product_code, 2, 3) AS result FROM products WHERE product_code = 'ABCD1234';

MySQL

SELECT SUBSTRING(product_code, 2, 3) AS result FROM products WHERE product_code = 'ABCD1234';

A 'BCD'

B 'ABC'

C 'BCD1'

D 'BCD12'

💡 Hint

Remember SUBSTRING starts at the given position and extracts the specified length.

SUBSTRING(product_code, 2, 3) starts at position 2 (B) and extracts 3 characters: 'BCD'.

❓ query_result

intermediate

What does LEFT() return here?

Given a table users with a column username containing 'charlie123', what is the result of this query?

SELECT LEFT(username, 7) AS short_name FROM users WHERE username = 'charlie123';

MySQL

SELECT LEFT(username, 7) AS short_name FROM users WHERE username = 'charlie123';

A 'charlie12'

B 'charlie1'

C 'charl'

D 'charlie'

💡 Hint

LEFT() returns the first N characters from the string.

LEFT(username, 7) returns the first 7 characters: 'charlie'.

📝 Syntax

advanced

Which query correctly extracts the last 4 characters using RIGHT()?

You want to get the last 4 characters from the column serial in table devices. Which query is correct?

A SELECT RIGHT(serial) FROM devices;

B SELECT RIGHT(serial, 4) FROM devices;

C SELECT RIGHT(serial, -4) FROM devices;

D SELECT RIGHT(serial, 0) FROM devices;

💡 Hint

RIGHT() needs a positive integer length as second argument.

RIGHT(serial, 4) extracts the last 4 characters. Negative or missing length causes errors or empty results.

❓ query_result

advanced

What is the output of this nested SUBSTRING and LEFT query?

Given a table files with a column filename containing 'report_final_2023.pdf', what does this query return?

SELECT LEFT(SUBSTRING(filename, 8), 5) AS part FROM files WHERE filename = 'report_final_2023.pdf';

MySQL

SELECT LEFT(SUBSTRING(filename, 8), 5) AS part FROM files WHERE filename = 'report_final_2023.pdf';

A 'final_2'

B 'final_'

C 'final'

D 'final_2023'

💡 Hint

SUBSTRING starts at position 8, then LEFT takes first 5 characters of that substring.

SUBSTRING(filename, 8) starts at 'f' in 'final_2023.pdf'. LEFT(..., 5) takes 'final'.

🧠 Conceptual

expert

Why might SUBSTRING() and LEFT()/RIGHT() produce different results on multibyte characters?

Consider a table texts with a column content containing multibyte UTF-8 characters like emojis. Why can SUBSTRING() and LEFT()/RIGHT() behave differently when extracting parts of such strings?

A Because SUBSTRING() counts characters while LEFT()/RIGHT() count bytes, causing mismatches with multibyte characters.

B Because LEFT()/RIGHT() only work on ASCII characters and fail on multibyte characters.

C Because SUBSTRING() automatically converts multibyte characters to single-byte before extracting.

D Because LEFT()/RIGHT() ignore multibyte characters and return empty strings.

💡 Hint

Think about how string length is measured in bytes vs characters.

SUBSTRING() counts characters properly in multibyte encodings, but LEFT()/RIGHT() count bytes, which can split characters incorrectly.