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.
The syntax for the CONCAT_WS() function is as follows:
CONCAT_WS(separator, string1, string2, ...., string_n)
separator: This represents the separator to be used.string1, string2...string_n: This represents the strings to be joined.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 tableINSERT INTO PersonVALUES (1,'Sharon', 'Peller',40000,'Female','Kogi');INSERT INTO PersonVALUES (2,'Paul', 'Dons',150000,'Male','Lagos');INSERT INTO PersonVALUES (3,'Ameera', 'Abedayo',200000,'Female','Imo');INSERT INTO PersonVALUES (4,'Maria', 'Elijah',320000,'Female','Lagos');INSERT INTO PersonVALUES (5,'David', 'Hassan',250000,'Male','Abuja');INSERT INTO PersonVALUES (6,'Niniola', 'Disu',80000,'Female','Lagos');INSERT INTO PersonVALUES (8,'Joe', 'Smith',75000, 'Male','Lagos');-- Getting data from person tableSELECT CONCAT_WS(' ',first_name , last_name ) AS full_name, genderFROM Person;
In the code above:
Person, which has the columns id, first_name, last_name, salary, gender, and state.Person table.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.