Concatenate Strings in MySQL

In MySQL, you can concatenate strings with the CONCAT and CONCAT_WS functions.

String concatenation is where two or more strings are combined into a single string. This is particularly useful when creating a single output from multiple text fields, formatting text for reports, or combining values with separators.

In this tutorial, we will go through some examples where we use CONCAT() or CONCAT_WS() string functions to concatenate two or more strings.

Concatenating Strings in MySQL

MySQL supports string concatenation with the CONCAT() and CONCAT_WS() functions:

  • CONCAT(): Combines two or more strings into a single string. If any of the values are NULL, the output will also be NULL.
  • CONCAT_WS(): Concatenates strings with a specified separator between each value. NULL values are ignored, so they don’t interfere with the final result.

Setup for Examples: Creating the Table

To demonstrate string concatenation, we’ll use a students table with fields first_name, last_name, and age.

1. Create a new database called school:

</>
Copy
CREATE DATABASE school;

2. Select the school database:

</>
Copy
USE school;

3. Create the students table with fields for first_name, last_name, and age:

</>
Copy
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT
);

4. Insert some sample data into the students table:

</>
Copy
INSERT INTO students (first_name, last_name, age)
VALUES 
('Alice', 'Smith', 14),
('Bob', 'Jones', 15),
('Charlie', 'Brown', 13);
Concatenate Strings in MySQL - Setup data for examples

Examples: Concatenating Strings in MySQL

With the table set up, we can now explore examples of string concatenation.

1. Concatenating First and Last Names Using CONCAT()

To combine first_name and last_name into a single full_name field, use CONCAT():

</>
Copy
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM students;

This query produces a full_name column by joining first_name and last_name with a space. The result will display names like “Alice Smith” and “Bob Jones”.

Concatenate Strings in MySQL - Example - Concatenating First and Last Names Using CONCAT()

2. Using CONCAT_WS to Add Custom Separators

To add separators like commas, use CONCAT_WS (concatenate with separator). Here, we combine first_name, last_name, and age into a single field:

</>
Copy
SELECT CONCAT_WS(', ', first_name, last_name, age) AS student_info
FROM students;

This query returns results like “Alice Smith, 14”, where each field is separated by a comma. If any field is NULL, it will be skipped in the concatenation.

Concatenate Strings in MySQL - Example - Using CONCAT_WS to Add Custom Separators

Conclusion

In MySQL, string concatenation can be easily achieved using CONCAT() or CONCAT_WS(). CONCAT() is ideal for straightforward concatenation, while CONCAT_WS() allows the use of a specified separator and ignores NULL values.