CONCAT and CONCAT_WS in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the CONCAT function do in MySQL?

CONCAT joins two or more strings together into one string. For example, CONCAT('Hello', ' ', 'World') returns 'Hello World'.

beginner

How is CONCAT_WS different from CONCAT?

CONCAT_WS adds a separator between strings while joining them. WS means 'With Separator'. For example, CONCAT_WS('-', '2024', '06', '15') returns '2024-06-15'.

intermediate

What happens if one of the arguments in CONCAT is NULL?

If any argument in CONCAT is NULL, the whole result becomes NULL. For example, CONCAT('Hello', NULL, 'World') returns NULL.

intermediate

How does CONCAT_WS handle NULL values?

CONCAT_WS skips NULL values and does not add separators for them. For example, CONCAT_WS('-', '2024', NULL, '15') returns '2024-15'.

beginner

Write a query using CONCAT_WS to join first_name and last_name with a space.

SELECT CONCAT_WS(' ', first_name, last_name) AS full_name FROM users;

What will CONCAT('My', NULL, 'SQL') return?

A 'MySQL'

B 'My SQL'

C 'My NULL SQL'

D NULL

CONCAT returns NULL if any argument is NULL.

Which function allows you to specify a separator between strings?

A CONCAT_WS

B CONCAT

C CONCAT_SEP

D CONCAT_SEPARATOR

CONCAT_WS stands for CONCAT With Separator.

What does CONCAT_WS('-', '2024', NULL, '06') return?

A '2024-06'

B '2024--06'

C '2024-NULL-06'

D NULL

CONCAT_WS skips NULL values and does not add extra separators.

How many arguments does CONCAT_WS require at minimum?

A Three

B One (separator only)

C Two (separator and at least one string)

D None

CONCAT_WS needs a separator and at least one string to join.

Which of these is a valid use of CONCAT to join 'Hello' and 'World'?

A CONCAT('Hello' + 'World')

B CONCAT('Hello', ' ', 'World')

C CONCAT_WS(' ', 'Hello', 'World')

D CONCAT('Hello' & 'World')

CONCAT joins strings directly; use commas to separate arguments.

Explain how CONCAT and CONCAT_WS handle NULL values differently.

Think about what happens when you join strings with missing parts.

Describe a real-life example where CONCAT_WS is more useful than CONCAT.

Consider formatting addresses or dates.