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

Subscribe to our YouTube Channel here

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

plus2net.com