Why CONCAT and CONCAT_WS in SQL? - Purpose & Use Cases

The Big Idea

Discover how a simple SQL function can save you hours of tedious text joining work!

The Scenario

Imagine you have a list of customer names and addresses in separate columns, and you want to create a full address line by joining these pieces together manually.

You try to do this by copying and pasting each part into a text editor or spreadsheet, hoping to get a neat combined result.

The Problem

This manual method is slow and boring. You might make mistakes like missing spaces or commas between parts. If the list is long, it becomes a huge headache and wastes a lot of time.

Also, if some parts are empty, you might end up with awkward extra commas or spaces.

The Solution

Using CONCAT and CONCAT_WS in SQL lets you join multiple pieces of text quickly and cleanly inside your database query.

CONCAT simply joins strings together, while CONCAT_WS lets you specify a separator like a comma or space, and it smartly skips empty parts to avoid extra separators.

Before vs After

✗ Before

SELECT CONCAT(first_name, ' ', last_name, ', ', city, ', ', state) FROM customers;

✓ After

SELECT CONCAT_WS(', ', CONCAT(first_name, ' ', last_name), city, state) FROM customers;

What It Enables

It makes combining text fields easy, clean, and error-free, saving you time and making your data look professional.

Real Life Example

When sending mailing labels, you can quickly create full address lines by joining street, city, state, and zip code with proper commas and spaces, even if some parts are missing.

Key Takeaways

Manual text joining is slow and error-prone.

CONCAT and CONCAT_WS join text easily inside SQL queries.

CONCAT_WS adds separators and skips empty values for clean results.

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