Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

sql

How to use the CONCAT_WS() function in SQL

Maria Elijah

Overview

The CONCAT_WS() function joins two or more strings using a separator. Some examples of separators include empty spaces, commas, hyphens, slashes, and so on.

Syntax

The syntax for the CONCAT_WS() function is as follows:

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

Parameters

  • separator: This represents the separator to be used.
  • string1, string2...string_n: This represents the strings to be joined.

Example

Let’s assume we have a table Person with columns first_name, last_name, salary, state, and gender. Now, we want to create a new column containing the full name of each person. We can do this using the CONCAT_WS() function.

The following code demonstrates how we can do this using the CONCAT_WS() function in SQL.

CREATE TABLE Person (
    ID int,
    first_name varchar(50),
    last_name varchar (50),
    salary int,
    gender varchar(10),
    state varchar(15)
);

-- Inserting data in Person table
INSERT INTO Person
VALUES (1,'Sharon', 'Peller',40000,'Female','Kogi');
INSERT INTO Person
VALUES (2,'Paul', 'Dons',150000,'Male','Lagos');
INSERT INTO Person
VALUES (3,'Ameera', 'Abedayo',200000,'Female','Imo');
INSERT INTO Person
VALUES (4,'Maria', 'Elijah',320000,'Female','Lagos');
INSERT INTO Person
VALUES (5,'David', 'Hassan',250000,'Male','Abuja');
INSERT INTO Person
VALUES (6,'Niniola', 'Disu',80000,'Female','Lagos');
INSERT INTO Person
VALUES (8,'Joe', 'Smith',75000, 'Male','Lagos');

-- Getting data from person table
SELECT CONCAT_WS(' ',first_name , last_name ) AS full_name, gender
FROM Person;
Inserting data in person Table and retrieving it using CONCAT_WS() function

Explanation

In the code above:

  • Lines 1–8: We create a table called Person, which has the columns id, first_name, last_name, salary, gender, and state.
  • Lines 11–24: We add data into the Person table.
  • Lines 27–28: Using the CONCAT_WS() function, we combine the first_name and last_name columns to form a new column called full_name.

Note: We use an empty string (space) as the separator.

RELATED TAGS

sql
RELATED COURSES

View all Courses

Keep Exploring