CONCAT and CONCAT_WS in MySQL - Mini Project: Build & Apply

Using CONCAT and CONCAT_WS in MySQL

📖 Scenario: You are managing a customer database for a small online store. You want to create full names and formatted contact information by combining different columns in your database.

🎯 Goal: Build SQL queries that use CONCAT and CONCAT_WS functions to combine customer first names, last names, and phone numbers into readable strings.

📋 What You'll Learn

Create a table called customers with columns id, first_name, last_name, and phone.

Insert three specific customer records with exact values.

Write a query using CONCAT to combine first_name and last_name with a space in between.

Write a query using CONCAT_WS to combine first_name, last_name, and phone separated by commas.

💡 Why This Matters

🌍 Real World

Combining columns into readable strings is common for displaying full names, addresses, or contact info in reports and user interfaces.

💼 Career

Database developers and analysts often use CONCAT and CONCAT_WS to prepare data for reports, exports, or application display.

1

Create the customers table and insert data

Create a table called customers with columns id (integer primary key), first_name (varchar 50), last_name (varchar 50), and phone (varchar 15). Then insert these three rows exactly: (1, 'John', 'Doe', '123-456-7890'), (2, 'Jane', 'Smith', '234-567-8901'), and (3, 'Emily', 'Jones', '345-678-9012').

MySQL

-- Create the customers table and insert the three rows
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Set up a query to combine first and last names

Write a SELECT query that uses CONCAT to combine first_name and last_name from the customers table with a space between them. Name the combined column full_name.

MySQL

CREATE TABLE customers (
  id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  phone VARCHAR(15)
);

INSERT INTO customers (id, first_name, last_name, phone) VALUES
(1, 'John', 'Doe', '123-456-7890'),
(2, 'Jane', 'Smith', '234-567-8901'),
(3, 'Emily', 'Jones', '345-678-9012');

-- Write a SELECT query using CONCAT to create full_name
-- Your code here

Hint

Use CONCAT(first_name, ' ', last_name) to join names with a space.

3

Use CONCAT_WS to combine name and phone with commas

Write a SELECT query that uses CONCAT_WS with a comma and space separator to combine first_name, last_name, and phone from the customers table. Name the combined column contact_info.

MySQL

CREATE TABLE customers (
  id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  phone VARCHAR(15)
);

INSERT INTO customers (id, first_name, last_name, phone) VALUES
(1, 'John', 'Doe', '123-456-7890'),
(2, 'Jane', 'Smith', '234-567-8901'),
(3, 'Emily', 'Jones', '345-678-9012');

SELECT id, CONCAT(first_name, ' ', last_name) AS full_name FROM customers;

-- Write a SELECT query using CONCAT_WS to create contact_info
-- Your code here

Hint

Use CONCAT_WS(', ', first_name, last_name, phone) to join with commas and spaces.

4

Combine both CONCAT and CONCAT_WS queries in one statement

Write a SELECT query that returns id, full_name using CONCAT (first and last name with space), and contact_info using CONCAT_WS (first name, last name, phone separated by commas) from the customers table.

MySQL

CREATE TABLE customers (
  id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  phone VARCHAR(15)
);

INSERT INTO customers (id, first_name, last_name, phone) VALUES
(1, 'John', 'Doe', '123-456-7890'),
(2, 'Jane', 'Smith', '234-567-8901'),
(3, 'Emily', 'Jones', '345-678-9012');

-- Write a SELECT query that returns id, full_name, and contact_info
-- Your code here

Hint

Include both CONCAT and CONCAT_WS in the SELECT clause.

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