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.

Practice

1. What is the main difference between CONCAT and CONCAT_WS in SQL?

easy

A. CONCAT joins strings directly; CONCAT_WS adds a separator between strings.

B. CONCAT adds a separator; CONCAT_WS joins strings directly.

C. CONCAT works only with numbers; CONCAT_WS works only with text.

D. CONCAT is used for arrays; CONCAT_WS is used for tables.

Solution

1. Step 1: Understand CONCAT behavior

   CONCAT joins text parts directly without any separator.

2. Step 2: Understand CONCAT_WS behavior

   CONCAT_WS joins text parts but inserts a separator you specify between them.

3. Final Answer:

   CONCAT joins strings directly; CONCAT_WS adds a separator between strings. -> Option A

4. Quick Check:

   Difference = separator usage [OK]

Hint: Remember WS means With Separator [OK]

Common Mistakes:

• Thinking CONCAT_WS adds no separator
• Confusing which function adds separators
• Assuming CONCAT works only on numbers

2. Which of the following is the correct syntax to join columns first_name and last_name with a space using CONCAT_WS?

easy

A. CONCAT_WS(' ', first_name + last_name)

B. CONCAT_WS(first_name, last_name, ' ')

C. CONCAT_WS(' ', first_name, last_name)

D. CONCAT_WS(first_name || ' ' || last_name)

Solution

1. Step 1: Recall CONCAT_WS syntax

   The first argument is the separator string, followed by the columns to join.

2. Step 2: Match syntax to options

   CONCAT_WS(' ', first_name, last_name) correctly uses a space as separator and then the two columns.

3. Final Answer:

   CONCAT_WS(' ', first_name, last_name) -> Option C

4. Quick Check:

   Separator first, then columns [OK]

Hint: Separator is always first argument in CONCAT_WS [OK]

Common Mistakes:

• Putting separator last instead of first
• Using operators inside CONCAT_WS arguments
• Passing concatenated string as single argument

3. Given a table users with columns city = 'Paris', country = 'France', what is the result of this query?

SELECT CONCAT(city, ', ', country) AS location FROM users;

medium

A. 'Paris, France'

B. 'Paris , France'

C. 'Paris,France'

D. 'ParisFrance'

Solution

1. Step 1: Understand CONCAT arguments

   CONCAT joins city, then the string ', ', then country exactly as given.

2. Step 2: Combine values

   city = 'Paris', separator = ', ', country = 'France' results in 'Paris, France'.

3. Final Answer:

   'Paris, France' -> Option A

4. Quick Check:

   CONCAT joins exactly as given [OK]

Hint: CONCAT joins exactly, including spaces in strings [OK]

Common Mistakes:

• Ignoring spaces inside separator string
• Assuming CONCAT adds spaces automatically
• Confusing CONCAT_WS with CONCAT

4. Identify the error in this SQL query:

SELECT CONCAT_WS(first_name, ' ', last_name) FROM employees;

medium

A. Using CONCAT_WS instead of CONCAT

B. Missing separator argument as first parameter

C. Incorrect column names

D. No error, query is correct

Solution

1. Step 1: Check CONCAT_WS syntax

   First argument must be the separator string, but here first_name is used instead.

2. Step 2: Identify correct usage

   Separator should be a string like ' ', then columns follow.

3. Final Answer:

   Missing separator argument as first parameter -> Option B

4. Quick Check:

   Separator must be first argument [OK]

Hint: Separator always first in CONCAT_WS [OK]

Common Mistakes:

• Putting column name as separator
• Swapping argument order
• Assuming CONCAT_WS works like CONCAT

5. You want to create a full address string from columns street, city, and zipcode separated by commas. Which query correctly uses CONCAT_WS to achieve this, and what will be the output if street = '123 Main St', city = NULL, zipcode = '90210'?

hard

A. SELECT CONCAT_WS(', ', street, city, zipcode) AS address FROM table; Output: '123 Main St, NULL, 90210'

B. SELECT CONCAT_WS(', ', street, city, zipcode) AS address FROM table; Output: '123 Main St, , 90210'

C. SELECT CONCAT(street, ', ', city, ', ', zipcode) AS address FROM table; Output: '123 Main St, , 90210'

D. SELECT CONCAT_WS(', ', street, city, zipcode) AS address FROM table; Output: '123 Main St, 90210'

Solution

1. Step 1: Understand CONCAT_WS behavior with NULLs

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

2. Step 2: Apply to given data

   With city = NULL, CONCAT_WS(', ', street, city, zipcode) returns '123 Main St, 90210' without extra commas.

3. Final Answer:

   SELECT CONCAT_WS(', ', street, city, zipcode) AS address FROM table; Output: '123 Main St, 90210' -> Option D

4. Quick Check:

   CONCAT_WS skips NULLs cleanly [OK]

Hint: CONCAT_WS skips NULLs, no extra separators [OK]

Common Mistakes:

• Expecting NULL to appear as text
• Assuming CONCAT_WS adds separators for NULLs
• Using CONCAT which does not skip NULLs