SQL CONCAT_WS() Function

SQL CONCAT_WS()

The SQL CONCAT_WS() function is used to concatenate two or more strings with a specified separator between each value. The “WS” in CONCAT_WS stands for “with separator.” Unlike CONCAT, CONCAT_WS also handles NULL values, skipping any NULL inputs instead of returning NULL for the entire result.

This function is useful when you need to concatenate values while avoiding NULL results and adding separators, such as commas, dashes, or spaces, between values.

In this tutorial, we will go through SQL CONCAT_WS() String function, its syntax, and how to use this function in SQL statements for string operations, with the help of well detailed examples.

---

Syntax of SQL CONCAT_WS() Function

The basic syntax of the SQL CONCAT_WS function is:

CONCAT_WS(separator, string1, string2, ...);

Each part of this syntax has a specific purpose:

• separator: The separator to place between each concatenated string.
• string1, string2, …: The strings to concatenate. You can include as many strings as needed, separated by commas. NULL values are skipped automatically.

---

Setup for Examples: Creating the Database and Table

We’ll create a sample students table to demonstrate the CONCAT_WS function examples, combining different fields for text formatting.

1. First, create a new database called school_db:

CREATE DATABASE school_db;

2. Select the school_db database to work with:

USE school_db;

3. Create a table named students with columns id, first_name, last_name, and age:

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

4. Insert sample data into the students table:

INSERT INTO students (first_name, last_name, age)
VALUES 
('Alice', 'Smith', 14),
('Bob', 'Jones', 15),
('Charlie', NULL, 13),
('David', 'Johnson', NULL);

With this setup complete, we can run the CONCAT_WS function examples to test and view results in the students table.

---

Examples Using CONCAT_WS in SQL Queries

We’ll go through examples demonstrating the CONCAT_WS function in SQL, using sample data from a students table with fields first_name, last_name, and age.

---

1. Concatenating First Name and Last Name with a Space Separator

To combine first_name and last_name into a single column called full_name with a space between them:

SELECT CONCAT_WS(' ', first_name, last_name) AS full_name
FROM students;

This query returns each student’s full name with a space as the separator. If first_name or last_name is NULL, it is skipped in the concatenation.

---

2. Concatenating Multiple Fields with a Comma Separator

To combine first_name, last_name, and age with a comma separator, creating a descriptive field:

SELECT CONCAT_WS(', ', first_name, last_name, age) AS description
FROM students;

This query returns a concatenated description, such as “Alice, Smith, 14”. If any field is NULL, it is skipped in the output without affecting the other values.

Reference: SQL NULL

---

3. Using CONCAT_WS with Custom Separators for Text Formatting

To format text with custom separators, such as a hyphen between first_name and last_name, and parentheses around age:

SELECT CONCAT_WS(' ', CONCAT_WS('-', first_name, last_name), CONCAT('(', age, ')')) AS formatted_text
FROM students;

This query returns results like “Alice-Smith (14)”. CONCAT_WS is used twice to control both the main separator (space) and the secondary separator (hyphen). If first_name or last_name is NULL, it is skipped.

---

4. Using CONCAT_WS in a WHERE Clause

To search for students with a specific formatted name in the full_name column:

SELECT CONCAT_WS(' ', first_name, last_name) AS full_name
FROM students
WHERE CONCAT_WS(' ', first_name, last_name) = 'Alice Smith';

This query returns records where the concatenated full name matches “Alice Smith”. Using CONCAT_WS in the WHERE clause ensures the search is conducted with the formatted output.

Reference: SQL WHERE

---

FAQs for SQL CONCAT_WS

1. What does the SQL CONCAT_WS function do?

The CONCAT_WS function joins two or more strings with a specified separator, skipping any NULL values.

2. What happens if CONCAT_WS encounters a NULL value?

If CONCAT_WS encounters a NULL value, it skips that value and concatenates the other values, instead of returning NULL as CONCAT does.

3. Can CONCAT_WS be used to format values?

Yes, CONCAT_WS is often used to format text values, allowing for separators like commas, hyphens, and spaces between fields.

4. How is CONCAT_WS different from CONCAT?

CONCAT_WS requires a separator and skips NULL values, while CONCAT does not require a separator and returns NULL if any value is NULL.

5. Can CONCAT_WS be used with numeric data?

Yes, CONCAT_WS can combine both numeric and string data. Numeric values are automatically converted to strings during concatenation.