CONCAT and CONCAT_WS in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the CONCAT function do in SQL?

CONCAT joins two or more strings together into one string.

beginner

How is CONCAT_WS different from CONCAT?

CONCAT_WS joins strings with a separator between them. The 'WS' means 'With Separator'.

intermediate

What happens if CONCAT receives a NULL value as one of its arguments?

CONCAT treats NULL as an empty string and continues joining other strings.

beginner

What is the first argument in CONCAT_WS?

The first argument in CONCAT_WS is the separator string used between other strings.

beginner

Write a simple SQL example using CONCAT_WS to join 'apple', 'banana', and 'cherry' with a comma and space.

SELECT CONCAT_WS(', ', 'apple', 'banana', 'cherry'); -- Result: 'apple, banana, cherry'

What does CONCAT('Hello', ' ', 'World') return?

A 'Hello, World'

B 'HelloWorld'

C 'Hello-World'

D 'Hello World'

CONCAT joins all strings exactly as given, so it returns 'Hello World' including the space.

Which function allows you to specify a separator between strings?

A CONCAT_WS

B LENGTH

C SUBSTRING

D CONCAT

CONCAT_WS lets you specify a separator as the first argument.

If one argument in CONCAT is NULL, what happens?

A The whole result is NULL

B NULL is treated as empty string

C An error occurs

D NULL is converted to 'NULL' text

CONCAT treats NULL as empty string and continues concatenation.

What is the output of CONCAT_WS('-', '2024', NULL, '06', '15')?

A 2024--06-15

B 2024NULL06-15

C 2024-06-15

D NULL-2024-06-15

CONCAT_WS skips NULL values and places the separator only between non-null strings.

Which of these is a valid use of CONCAT_WS?

A CONCAT_WS(' ', 'Hello', 'World')

B CONCAT_WS('Hello', ' ', 'World')

C CONCAT_WS(' ', 'Hello')

D CONCAT_WS(' ', NULL)

The first argument is the separator, so ' ' is correct as separator. Using only one string or NULL values is valid; NULLs are skipped.

Explain how CONCAT and CONCAT_WS work and how they handle NULL values.

Think about how you join words in a sentence with or without spaces.

Write an example SQL query using CONCAT_WS to join first name, middle name, and last name with spaces, skipping NULL middle name.

Remember CONCAT_WS ignores NULL values automatically.

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