CONCAT and CONCAT_WS in MySQL

Introduction

Use CONCAT and CONCAT_WS to join multiple pieces of text into one string. This helps combine names, addresses, or any text parts easily.

You want to join first name and last name into a full name.

You need to create a full address from street, city, and zip code.

You want to combine multiple columns into one for display.

You want to add separators like commas or spaces between text parts.

Syntax

MySQL

CONCAT(string1, string2, ...)
CONCAT_WS(separator, string1, string2, ...)

CONCAT joins strings directly without any separator.

CONCAT_WS joins strings using the first argument as a separator between each string.

Examples

Joins 'Hello', a space, and 'World' into 'Hello World'.

MySQL

SELECT CONCAT('Hello', ' ', 'World');

Joins the strings with '-' as separator, resulting in '2024-06-15'.

MySQL

SELECT CONCAT_WS('-', '2024', '06', '15');

Joins 'Name: ', first name, and last name with spaces.

MySQL

SELECT CONCAT('Name: ', first_name, ' ', last_name) FROM users;

Joins city, state, and zip with ', ' as separator.

MySQL

SELECT CONCAT_WS(', ', city, state, zip) FROM addresses;

Sample Program

This creates a table with names and locations, inserts two rows, then shows full names and locations with proper joining.

MySQL

CREATE TABLE people (
  first_name VARCHAR(20),
  last_name VARCHAR(20),
  city VARCHAR(30),
  state VARCHAR(20)
);

INSERT INTO people VALUES
('John', 'Doe', 'New York', 'NY'),
('Jane', 'Smith', 'Los Angeles', 'CA');

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM people;
SELECT CONCAT_WS(', ', city, state) AS location FROM people;

Important Notes

If any argument in CONCAT is NULL, the result is NULL.

CONCAT_WS skips NULL values and does not add extra separators for them.

Use CONCAT_WS when you want to avoid extra separators if some parts are missing.

Summary

CONCAT joins strings directly, returning NULL if any part is NULL.

CONCAT_WS joins strings with a separator and skips NULL values.

Both help combine text columns for clearer display or reports.