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

Using CONCAT and CONCAT_WS to Combine Text in SQL

📖 Scenario: You are managing a small customer database for a local bookstore. You want to create a full name field by combining first and last names, and also create a contact info field by combining the full name with the email address, separated by a comma.

🎯 Goal: Build SQL queries that use CONCAT and CONCAT_WS functions to combine text columns into new fields.

📋 What You'll Learn

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

Insert three specific customer records into the customers table.

Write a query using CONCAT to create a full_name column by joining first_name and last_name with a space.

Write a query using CONCAT_WS to create a contact_info column by joining full_name and email separated by a comma and space.

💡 Why This Matters

🌍 Real World

Combining text fields like names and contact info is common in customer databases, reports, and user interfaces.

💼 Career

Knowing how to use CONCAT and CONCAT_WS helps database developers and analysts prepare readable and formatted data outputs.

1

Create the customers table and insert data

Create a table called customers with columns id (integer), first_name (text), last_name (text), and email (text). Then insert these three rows exactly: (1, 'Alice', 'Johnson', '[e-mail]'), (2, 'Bob', 'Smith', '[e-mail]'), and (3, 'Carol', 'Davis', '[e-mail]').

SQL

-- 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 the rows.

2

Add a query to combine first and last names

Write a SQL query that selects id and creates a new column called full_name by using CONCAT to join first_name and last_name with a space between them from the customers table.

SQL

CREATE TABLE customers (
  id INTEGER,
  first_name TEXT,
  last_name TEXT,
  email TEXT
);

INSERT INTO customers (id, first_name, last_name, email) VALUES
  (1, 'Alice', 'Johnson', '[e-mail]'),
  (2, 'Bob', 'Smith', '[e-mail]'),
  (3, 'Carol', 'Davis', '[e-mail]');

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

Hint

Use CONCAT with a space string ' ' between the names.

3

Add a query to combine full name and email with CONCAT_WS

Write a SQL query that selects id and creates a new column called contact_info by using CONCAT_WS with a comma and space separator to join first_name and last_name as a full name, and email from the customers table.

SQL

CREATE TABLE customers (
  id INTEGER,
  first_name TEXT,
  last_name TEXT,
  email TEXT
);

INSERT INTO customers (id, first_name, last_name, email) VALUES
  (1, 'Alice', 'Johnson', '[e-mail]'),
  (2, 'Bob', 'Smith', '[e-mail]'),
  (3, 'Carol', 'Davis', '[e-mail]');

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 with ', ' as the separator and nest CONCAT for the full name.

4

Combine all queries into one final query

Write a single SQL query that selects id, creates full_name by concatenating first_name and last_name with a space using CONCAT, and creates contact_info by concatenating full_name and email separated by a comma and space using CONCAT_WS from the customers table.

SQL

CREATE TABLE customers (
  id INTEGER,
  first_name TEXT,
  last_name TEXT,
  email TEXT
);

INSERT INTO customers (id, first_name, last_name, email) VALUES
  (1, 'Alice', 'Johnson', '[e-mail]'),
  (2, 'Bob', 'Smith', '[e-mail]'),
  (3, 'Carol', 'Davis', '[e-mail]');

-- Write a single SELECT query that creates full_name and contact_info
-- Your code here

Hint

Combine the previous two queries into one SELECT statement with multiple columns.

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