concat_WS to join string with separator : MySQL Query

CONCAT_WS: Concatenate With Separator

SELECT CONCAT_WS(":", "String 1", "String 2", "String 3", "String4") AS my_str;

Output is here

String 1:String 2:String 3:String4

We used ':' as separator here and all strings are joined using this separator. We will use this in our student table. ( download sql_dump from our concat tutorial. )

SELECT concat_ws(':',f_name,' ',l_name) as name,class  FROM `student_name`

name	class
John: :Deo	Four
Larry:	Four
Ronald:	Five
Garry: :Miller	Five
	Five
:Ruller

You can see in above records if any one of the string ( or column ) is null then the value returned by CONCAT_WS is null. We can change this and return any specific string by using COALESCE.

SELECT CONCAT_WS(COALESCE(f_name,'-',' '),' ',COALESCE(l_name,'-')) as name,class  FROM `student_name`

name	class
JohnDeo	Four
Larry-	Four
Ronald-	Five
GarryMiller	Five
--	Five
-Ruller

SQL String References substring_index to get part of string using delimiter

Subhendu Mohapatra

Author

🎥 Join me live on YouTube

Passionate about coding and teaching, I publish practical tutorials on PHP, Python, JavaScript, SQL, and web development. My goal is to make learning simple, engaging, and project‑oriented with real examples and source code.

Subscribe to our YouTube Channel here

This article is written by plus2net.com team. https://www.plus2net.com

plus2net.com