CONCAT & CONCAT_WS
In this page:
The CONCAT Function
CONCAT combines two or more string values together into one. It links them end-to-end to create a single text string, such as joining a first and last name into a full name.
Example: The CONCAT Function
CREATE TABLE users (id INT, first_name TEXT, last_name TEXT);
INSERT INTO users VALUES (1, 'Amit', 'Sharma');
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;Handling Null Values in CONCAT
If any input inside CONCAT is NULL, the entire result will become NULL, which can silently blank out a whole combined field. You should use helper functions like IFNULL if your columns might contain empty values, wrapping each argument individually.
Example: Handling Null Values in CONCAT
CREATE TABLE users (id INT, first_name TEXT, middle_name TEXT, last_name TEXT);
INSERT INTO users VALUES (1, 'Amit', NULL, 'Sharma');
SELECT CONCAT(first_name, middle_name, last_name) AS broken_name FROM users;
SELECT CONCAT(first_name, IFNULL(middle_name, ''), last_name) AS safe_name FROM users;Introducing CONCAT_WS
CONCAT_WS stands for 'Concatenate With Separator'. You define the separator first, and MySQL puts it between all the strings that follow, saving you from typing the separator manually between every pair.
Example: Introducing CONCAT_WS
CREATE TABLE users (id INT, first_name TEXT, last_name TEXT);
INSERT INTO users VALUES (1, 'Amit', 'Sharma');
SELECT CONCAT_WS(' ', first_name, last_name) AS full_name FROM users;Null Values in CONCAT_WS
Unlike CONCAT, CONCAT_WS automatically skips NULL values instead of propagating them. It does not turn the entire result into NULL, which is extremely handy when some optional fields, like a middle name, might be missing.
Example: Null Values in CONCAT_WS
CREATE TABLE users (id INT, first_name TEXT, middle_name TEXT, last_name TEXT);
INSERT INTO users VALUES (1, 'Amit', NULL, 'Sharma');
SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name FROM users;Practical Use Cases
You can use string concat functions to build complete URLs, format output strings, or generate full address text blocks by joining street, city, and postal code columns into one readable line.
Example: Practical Use Cases
CREATE TABLE addresses (id INT, street TEXT, city TEXT, postal_code TEXT);
INSERT INTO addresses VALUES (1, '12 MG Road', 'Patna', '800001');
SELECT CONCAT_WS(', ', street, city, postal_code) AS full_address FROM addresses;Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: