CONCAT and CONCAT_WS in SQL

Introduction

CONCAT and CONCAT_WS help join text pieces together to make one longer text. This is useful when you want to combine names, addresses, or other text parts.

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

You need to combine city, state, and zip code into one address line.

You want to create a sentence from several words stored in different columns.

You want to add a separator like a comma or space between text parts.

Syntax

SQL

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

CONCAT joins strings directly without any separator.

CONCAT_WS joins strings with a separator you choose; WS means 'With Separator'.

Examples

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

SQL

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

Joins the parts with '-' separator to get '2024-06-15'.

SQL

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

Joins first and last names without space.

SQL

SELECT CONCAT(first_name, last_name) FROM users;

Joins first and last names with a space in between.

SQL

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

Sample Program

This creates a table with first and last names, inserts two rows, then shows two ways to join names: without space and with space.

SQL

CREATE TABLE people (first_name VARCHAR(20), last_name VARCHAR(20));
INSERT INTO people VALUES ('John', 'Doe'), ('Jane', 'Smith');
SELECT CONCAT(first_name, last_name) AS full_name_no_space, CONCAT_WS(' ', first_name, last_name) AS full_name_with_space FROM people;

Important Notes

If any argument in CONCAT is NULL, it is treated as an empty string.

In CONCAT_WS, NULL values are skipped and do not add extra separators.

Use CONCAT_WS when you want clear separators between parts, like commas or spaces.

Summary

CONCAT joins text parts directly without separators.

CONCAT_WS joins text parts with a separator you choose.

Both help make readable combined text from separate columns.