CONCAT and CONCAT_WS in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of CONCAT with NULL values

Consider the following SQL query:

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

What will be the output of this query?

SQL

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

A Syntax Error

B 'Hello NULL World'

C 'HelloWorld'

D NULL

💡 Hint

Remember how CONCAT handles NULL values in SQL.

In many SQL dialects like MySQL, CONCAT treats NULL as an empty string, so CONCAT('Hello', NULL, 'World') returns 'HelloWorld'.

❓ query_result

intermediate

Using CONCAT_WS with NULL values

What is the output of this SQL query?

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

SQL

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

A '2024-06-15'

B '2024--06-15'

C '2024-NULL-06-15'

D NULL

💡 Hint

CONCAT_WS skips NULL values when concatenating.

CONCAT_WS skips NULL values and does not add extra separators for them. So the NULL is ignored and the output is '2024-06-15'.

📝 Syntax

advanced

Identify the syntax error in CONCAT usage

Which of the following SQL statements will cause a syntax error?

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

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

C SELECT CONCAT('A', NULL, 'C') AS result;

D SELECT CONCAT_WS('-', 'A', 'B', 'C') AS result;

💡 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 produces the correct full name without extra spaces?

A SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name FROM users;

B SELECT CONCAT_WS(' ', first_name, COALESCE(middle_name, ''), last_name) AS full_name FROM users;

C SELECT CONCAT(first_name, ' ', middle_name, ' ', last_name) AS full_name FROM users;

D SELECT CONCAT(first_name, middle_name, last_name) AS full_name FROM users;

💡 Hint

CONCAT_WS skips NULL values and adds separators only between non-NULL values.

CONCAT_WS with space separator skips NULL middle_name and avoids extra spaces, producing correct full name.

🧠 Conceptual

expert

Difference between CONCAT and CONCAT_WS behavior

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

A Both CONCAT and CONCAT_WS skip NULL arguments and add separators for them.

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

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

D CONCAT returns NULL if any argument is NULL; CONCAT_WS skips NULL arguments and does not add separators for them.

💡 Hint

Think about how each function treats NULL values and separators.

CONCAT returns NULL if any argument is NULL. CONCAT_WS skips NULL arguments and does not add separators for them.