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.