MySQL CONCAT() String Function

MySQL CONCAT() String Function

The MySQL CONCAT() string function is used to concatenate two or more strings. This function is essential for combining multiple string values into a single string in SQL queries.

---

Syntax

SELECT CONCAT(string1, string2, ..., stringN) AS result
FROM table_name;

The CONCAT() function has the following components:

• string1, string2, ..., stringN: A list of strings to be concatenated.
• result: An alias for the resulting concatenated string.
• table_name: The name of the table from which to retrieve the data.

---

Example MySQL CONCAT() String Function

Let's look at some examples of the MySQL CONCAT() string function:

Step 1: Using the Database

USE mydatabase;

This query sets the context to the database named mydatabase.

Step 2: Creating a Table

Create a table to work with:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL
);

This query creates a table named employees with columns for id, first_name, and last_name.

Step 3: Inserting Initial Rows

Insert some initial rows into the table:

INSERT INTO employees (first_name, last_name)
VALUES ('John', 'Doe'),
       ('Jane', 'Smith'),
       ('Jim', 'Brown'),
       ('Jake', 'White'),
       ('Jill', 'Green');

This query inserts five rows into the employees table.

Step 4: Using CONCAT() with WHERE Clause

Use the CONCAT() function to concatenate strings:

SELECT first_name, last_name, CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

This query retrieves the first_name and last_name columns from the employees table and returns the concatenated full name.

Step 5: Using CONCAT() with Multiple Columns

Use the CONCAT() function with multiple columns:

SELECT id, first_name, last_name, CONCAT('ID: ', id, ', Name: ', first_name, ' ', last_name) AS full_details
FROM employees;

This query retrieves the id, first_name, and last_name columns from the employees table and returns a concatenated string with full details.

Step 6: Using CONCAT() with Constants

Use the CONCAT() function with constants:

SELECT CONCAT('Welcome, ', first_name, ' ', last_name, '!') AS welcome_message
FROM employees;

This query retrieves the first_name and last_name columns from the employees table and returns a welcome message for each employee.

---

Conclusion

The MySQL CONCAT() function is a powerful tool for combining multiple string values into a single string in SQL queries. Understanding how to use the CONCAT() function is essential for effective data querying and analysis in MySQL.