How to use the CONCAT_WS() function in SQL
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 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;
Explanation
In the code above:
- Lines 1–8: We create a table called
Person, which has the columnsid,first_name,last_name,salary,gender, andstate. - Lines 11–24: We add data into the
Persontable. - Lines 27–28: Using the
CONCAT_WS()function, we combine thefirst_nameandlast_namecolumns to form a new column calledfull_name.
Note: We use an empty string (space) as the separator.
TRENDING TOPICS
