CONCAT and CONCAT_WS in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of CONCAT with NULL values

What is the output of this MySQL query?

SELECT CONCAT('Hello', NULL, 'World');

MySQL

SELECT CONCAT('Hello', NULL, 'World');

A NULL

B 'HelloWorld'

C 'HelloNULLWorld'

D 'Hello World'

💡 Hint

Remember how CONCAT treats NULL values in MySQL.

In MySQL, CONCAT returns NULL if any argument is NULL.

❓ query_result

intermediate

Using CONCAT_WS with NULL values

What is the output of this MySQL query?

SELECT CONCAT_WS('-', '2024', NULL, '06', '15');

MySQL

SELECT CONCAT_WS('-', '2024', NULL, '06', '15');

A NULL

B '2024-NULL-06-15'

C '2024--06-15'

D '2024-06-15'

💡 Hint

CONCAT_WS skips NULL values instead of treating them as strings.

CONCAT_WS skips NULL values and does not add separators for them.

📝 Syntax

advanced

Identify the syntax error in CONCAT usage

Which option contains a syntax error in using CONCAT in MySQL?

A SELECT CONCAT('A' 'B', 'C');

B SELECT CONCAT('A', , 'C');

C SELECT CONCAT('A', 'B' 'C');

D SELECT CONCAT('A', 'B', 'C');

💡 Hint

Look for missing arguments or commas.

Option B has two commas with no argument between them, causing a syntax error.

❓ optimization

advanced

Optimizing string concatenation with CONCAT_WS

You want to concatenate first name, middle name, and last name with spaces between them. Middle name can be NULL. Which query is the most efficient and correct to avoid extra spaces?

A SELECT CONCAT(first_name, ' ', middle_name, ' ', last_name) FROM users;

B SELECT CONCAT(first_name, middle_name, last_name) FROM users;

C SELECT CONCAT_WS(' ', first_name, middle_name, last_name) FROM users;

D SELECT CONCAT_WS('', first_name, middle_name, last_name) FROM users;

💡 Hint

Think about how NULL values and separators are handled.

CONCAT_WS skips NULL values and adds the separator only between non-NULL values, avoiding extra spaces.

🧠 Conceptual

expert

Behavior difference between CONCAT and CONCAT_WS

Which statement correctly describes the difference between CONCAT and CONCAT_WS in MySQL?

A CONCAT returns NULL if any argument is NULL; CONCAT_WS skips NULL arguments and does not return NULL.

B CONCAT_WS returns NULL if any argument is NULL; CONCAT skips NULL arguments.

C Both CONCAT and CONCAT_WS return NULL if any argument is NULL.

D Both CONCAT and CONCAT_WS skip NULL arguments and never return NULL.

💡 Hint

Think about how each function treats NULL values.

CONCAT returns NULL if any argument is NULL, but CONCAT_WS skips NULL arguments and concatenates the rest.