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 & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

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

plus2net.com