CONCAT_WS Function in SQL

The CONCAT_WS string function in the Structured Query Language joins two or more strings in one string with the separator.

Syntax of CONCAT_WS String Function

Syntax1: This syntax uses the CONCAT_WS function with multiple columns of the SQL table:

SELECT CONCAT_WS(separator, Column_Name1, column_Name2, Column_Name3,……. Column_NameN) AS Alias_Name FROM Table_Name;

In the syntax, we have to specify the name of those columns whose values we want to join with the particular separator.

Syntax2: This syntax uses the CONCAT_WS function with two or more strings:

Syntax3: This syntax uses the CONCAT_WS function with two or more characters:

Examples of CONCAT_WS String function

Example 1: The following SELECT query adds two characters to form a new string:

Output:

'H*i*i*i'

Example 2: The following SELECT query gives a new string by adding more than 2 characters:

Output:

Example 3: The following SELECT query adds two strings with underscore sign:

Output:

Example 4: The following SELECT query adds two strings which contain symbols:

Output:

#@##9Ski+Java958@%^&

Example 5: The following SELECT query use space between two strings:

Output:

JavaTpoint Website.

Example 6: This example uses the CONCAT_WS function with the table in Structured Query Language.

In this example, we have to create a new SQL table through which we will execute the CONCAT_WS() function on columns.

The syntax for creating the new table in the SQL database is as follows:

The following CREATE statement creates the Student_Marks table:

The below INSERT queries insert the records of college Faculties in the Student_Marks table:

The following SELECT statement displays the inserted records of the above Student_Marks table:

Query 1: The following SELECT query uses the CONCAT_WS function with the Student_First_Name, Student_Middle_Name, and Student_Last_Name columns of the above Student_Marks table:

This SQL statement joins the values of the first name, middle name, and last name of each student.

Output:

Query 2: The following SELECT query uses the CONCAT_WS function with the Student_City and Student_State column of those students whose Student_Id is greater than 4002 in the above Student_Marks table:

This SQL statement adds the values of city and state with space.

Output: