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.

MySQL USE DATABASE

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.

MySQL CREATE TABLE

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.

MySQL INSERT INTO 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.

MySQL CONCAT() WITH WHERE CLAUSE

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.

MySQL CONCAT() WITH MULTIPLE COLUMNS

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.

MySQL CONCAT() WITH CONSTANTS

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.