CONCAT and CONCAT_WS in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to concatenate first_name and last_name without any separator.

MySQL

SELECT CONCAT(first_name [1] last_name) AS full_name FROM employees;

Drag options to blanks, or click blank then click option'

A||

B+

C,

D&

Attempts:

3 left

💡 Hint

Common Mistakes

Using + or || instead of comma inside CONCAT.

Trying to concatenate without commas.

The CONCAT function in MySQL takes multiple arguments separated by commas and joins them into one string.

2fill in blank

medium

Complete the code to concatenate first_name and last_name with a space between them using CONCAT_WS.

MySQL

SELECT CONCAT_WS([1], first_name, last_name) AS full_name FROM employees;

Drag options to blanks, or click blank then click option'

A' '

B','

C'-'

D''

Attempts:

3 left

💡 Hint

Common Mistakes

Using CONCAT instead of CONCAT_WS for separator.

Using wrong separator like '-' or ',' instead of space.

CONCAT_WS joins strings with a separator as the first argument. To add a space, use ' ' as the separator.

3fill in blank

hard

Fix the error in the code to concatenate city and state with a comma and space using CONCAT_WS.

MySQL

SELECT CONCAT_WS([1], city, state) AS location FROM addresses;

Drag options to blanks, or click blank then click option'

A' , '

B';'

C','

D', '

Attempts:

3 left

💡 Hint

Common Mistakes

Using ' , ' with spaces around comma inside quotes.

Using only ',' without space.

Using wrong separator like ';'.

The separator should be a comma followed by a space: ', ' (with no extra spaces inside quotes).

4fill in blank

hard

Fill both blanks to concatenate first_name, middle_name, and last_name with spaces, skipping NULL middle_name.

MySQL

SELECT CONCAT_WS([1], first_name, [2], last_name) AS full_name FROM employees;

Drag options to blanks, or click blank then click option'

A' '

Bmiddle_name

CNULL

DmiddleName

Attempts:

3 left

💡 Hint

Common Mistakes

Using NULL instead of middle_name column.

Using wrong separator like comma.

Using wrong column name like middleName.

CONCAT_WS uses the first argument as separator (space here). Middle_name is the column to include, and CONCAT_WS skips NULL values automatically.

5fill in blank

hard

Fill all three blanks to concatenate street, city, and zip code separated by commas and spaces.

MySQL

SELECT CONCAT_WS([1], street, [2], [3]) AS address FROM locations;

Drag options to blanks, or click blank then click option'

A', '

Bcity

Czip_code

Dstate

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong separator like ';'.

Using wrong column names like state instead of zip_code.

Missing commas or spaces in separator.

Use ', ' as separator. Include city and zip_code columns in order to form full address.

Practice

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

easy

A. CONCAT returns NULL if any argument is NULL; CONCAT_WS skips NULLs and uses a separator.

B. CONCAT adds a separator between strings; CONCAT_WS joins strings without separator.

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

D. CONCAT converts all inputs to uppercase; CONCAT_WS converts to lowercase.

Solution

1. Step 1: Understand CONCAT behavior

   CONCAT joins strings directly but returns NULL if any input is NULL.

2. Step 2: Understand CONCAT_WS behavior

   CONCAT_WS joins strings with a separator and ignores NULL values instead of returning NULL.

3. Final Answer:

   CONCAT returns NULL if any argument is NULL; CONCAT_WS skips NULLs and uses a separator. -> Option A

4. Quick Check:

   Difference in NULL handling and separator use = CONCAT returns NULL if any argument is NULL; CONCAT_WS skips NULLs and uses a separator. [OK]

Hint: Remember: CONCAT returns NULL if any NULL; CONCAT_WS skips NULLs [OK]

Common Mistakes:

• Thinking CONCAT_WS adds no separator
• Assuming CONCAT_WS returns NULL if any argument is NULL
• Confusing which function skips NULLs

2. Which of the following is the correct syntax to join 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: Identify CONCAT_WS syntax

   CONCAT_WS takes the separator as the first argument, then the strings to join.

2. Step 2: Match correct argument order

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

3. Final Answer:

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

4. Quick Check:

   Separator first, then strings = CONCAT_WS(' ', first_name, last_name) [OK]

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

Common Mistakes:

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

3. Given a table users with columns first_name, middle_name, and last_name, what is the output of this query?

SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name FROM users WHERE id = 1;

Assuming the row with id=1 has first_name='John', middle_name=NULL, and last_name='Doe'.

medium

A. 'John NULL Doe'

B. 'John Doe'

C. 'John Doe'

D. NULL

Solution

1. Step 1: Understand CONCAT_WS with NULL values

   CONCAT_WS skips NULL values and joins remaining strings with the separator.

2. Step 2: Apply to given data

   Since middle_name is NULL, it is skipped. The result joins 'John' and 'Doe' with a space.

3. Final Answer:

   'John Doe' -> Option B

4. Quick Check:

   NULL skipped, space separator used = 'John Doe' [OK]

Hint: CONCAT_WS skips NULLs, so no extra spaces for NULL columns [OK]

Common Mistakes:

• Expecting NULL to appear as text
• Assuming NULL causes entire result to be NULL
• Thinking NULL inserts extra spaces

4. Identify the error in this query:

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

medium

A. The query will return NULL for full_name if any column is NULL.

B. The syntax is incorrect; CONCAT requires only two arguments.

C. NULL cannot be used inside CONCAT; it causes a syntax error.

D. The query will concatenate NULL as the string 'NULL'.

Solution

1. Step 1: Understand CONCAT behavior with NULL

   CONCAT returns NULL if any argument is NULL.

2. Step 2: Analyze the query

   The query uses CONCAT on columns that may contain NULL, so the entire result for full_name will be NULL if any column is NULL.

3. Final Answer:

   The query will return NULL for full_name if any column is NULL. -> Option A

4. Quick Check:

   CONCAT returns NULL if any argument is NULL = The query will return NULL for full_name if any column is NULL. [OK]

Hint: CONCAT returns NULL if any argument is NULL, no error [OK]

Common Mistakes:

• Thinking CONCAT throws syntax error with NULL
• Expecting NULL to appear as 'NULL' string
• Assuming CONCAT accepts unlimited arguments without NULL effect

5. You want to create a full address string from columns street, city, and zipcode in a table, separated by commas. Some rows have NULL in zipcode. Which query correctly creates the address without extra commas for NULL values?

hard

A. SELECT CONCAT_WS(', ', street, city) || ', ' || zipcode AS full_address FROM addresses;

B. SELECT CONCAT(street, ', ', city, ', ', zipcode) AS full_address FROM addresses;

C. SELECT CONCAT_WS(', ', street, city, zipcode) AS full_address FROM addresses;

D. SELECT CONCAT(street, city, zipcode) AS full_address FROM addresses;

Solution

1. Step 1: Understand handling of NULLs and separators

   CONCAT_WS joins strings with a separator and skips NULL values, avoiding extra separators.

2. Step 2: Analyze each option

   SELECT CONCAT_WS(', ', street, city, zipcode) AS full_address FROM addresses; uses CONCAT_WS with ', ' separator and all columns, so NULL zipcode is skipped without extra commas. SELECT CONCAT(street, ', ', city, ', ', zipcode) AS full_address FROM addresses; uses CONCAT which returns NULL if any argument is NULL, causing missing addresses. SELECT CONCAT_WS(', ', street, city) || ', ' || zipcode AS full_address FROM addresses; uses invalid syntax with || operator in MySQL. SELECT CONCAT(street, city, zipcode) AS full_address FROM addresses; concatenates without separators and returns NULL if any NULL present.

3. Final Answer:

   SELECT CONCAT_WS(', ', street, city, zipcode) AS full_address FROM addresses; -> Option C

4. Quick Check:

   Use CONCAT_WS to skip NULLs and add separators correctly = SELECT CONCAT_WS(', ', street, city, zipcode) AS full_address FROM addresses; [OK]

Hint: Use CONCAT_WS with separator to skip NULLs and avoid extra commas [OK]

Common Mistakes:

• Using CONCAT which returns NULL if any NULL argument
• Trying to concatenate with || which is not MySQL syntax
• Manually adding separators causing extra commas