MySQL CONCAT_WS() String Function

MySQL CONCAT_WS() String Function

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

Syntax

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

The CONCAT_WS() function has the following components:

  • separator: The separator to be used between the strings.
  • 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_WS() String Function

Let's look at some examples of the MySQL CONCAT_WS() 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_WS() with WHERE Clause

Use the CONCAT_WS() function to concatenate strings with a separator:

SELECT first_name, last_name, CONCAT_WS(' ', 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 with a space separator.

MySQL CONCAT_WS() WITH WHERE CLAUSE

Step 5: Using CONCAT_WS() with Multiple Columns

Use the CONCAT_WS() function with multiple columns:

SELECT id, first_name, last_name, CONCAT_WS(', ', '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, separated by commas.

MySQL CONCAT_WS() WITH MULTIPLE COLUMNS

Step 6: Using CONCAT_WS() with Constants

Use the CONCAT_WS() function with constants:

SELECT CONCAT_WS(' - ', first_name, last_name, 'Employee') AS employee_details
FROM employees;

This query retrieves the first_name and last_name columns from the employees table and returns an employee details string with ' - ' as the separator.

MySQL CONCAT_WS() WITH CONSTANTS

Conclusion

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