How to coalesce, concat and ignore nulls during select? MySql

Question

My table is something like this:

Id | Name | ph_office | ph_home   
1  | ab   |  111      | NULL   
2  | cd   |  NULL     | 222   
3  | ef   |  333      | 444  
4  | gh   |  NULL     | NULL

and I want to append the name of that respective column to corresponding value. something like this:

Id | Name | phone   
1  | ab   |  phone_office: 111   
2  | cd   |  phone_home: 222  
3  | ef   |  phone_office: 333, phone_home: 444  
4  | gh   |  NULL

I tried using

select id, Name, 
CONCAT('ph_office: ', COALESCE(ph_office,''),',','ph_home:',COALESCE(ph_home,'')) as phone
from contacts;

Id | Name | phone  
1  | ab   |  ph_office: 111,ph_home:  
2  | cd   |  ph_office:, ph_home: 222  
3  | ef   |  ph_office: 333, ph_home: 444  
4  | gh   |  ph_office:,ph_home

I want to ignore the column which has null. I am not much good at case statements. How can it be done in mysql?

Thanks for your time

https://dev.mysql.com/doc/refman/5.7/en/case.html or even https://stackoverflow.com/questions/9588015/how-do-i-use-properly-case-when-in-mysql — Ben Yitzhaki, Nov 01 '18 at 12:49

Madhur Bhaiya · Answer 1 · 2018-11-01T13:07:18.743

Instead of Concat(), you can use Concat_ws() function. It is used to concatenate multiple substrings with a given separator. If any of the substring is Null, it is skipped. This will help us in cases when either of them are null; so that we are not left with Leading/Trailing commas.
Also, you can use Case .. When to consider a string to concatenate only when its value IS NOT NULL

Try the following instead:

SELECT 
  id, 
  Name, 
  CONCAT_WS(', ', 
            (CASE 
              WHEN ph_office IS NOT NULL THEN CONCAT('ph_office: ', ph_office)
             END),  
            (CASE 
              WHEN ph_home IS NOT NULL THEN CONCAT('ph_home: ', ph_home)
             END)
          ) AS phone
FROM contacts;

EDIT (didn't realize that Gordon Linoff has already answered using the following approach):

However, a neater technique without utilizing Case .. When is possible. From the Docs of Concat() function:

CONCAT() returns NULL if any argument is NULL.

SELECT 
  id, 
  Name, 
  CONCAT_WS(', ', 
            CONCAT('ph_office: ', ph_office), 
            CONCAT('ph_home: ', ph_home)
           ) AS phone
FROM contacts;

Note that this will work only if ph_office or ph_home value is Null instead of empty string.

Thank you Madhur. So what you think? using case will fasten my query? I mean I will try looking at execution plan. — Omkar, Nov 01 '18 at 13:14
@Omkar there wont be a significant difference. Eventually, concat() function would have been doing same internal checks whether the value is null or not. Second approach is neater to write; it will have one limitation that future maintainer of the code may not know that concat returns null when any one of the value is null — Madhur Bhaiya, Nov 01 '18 at 13:18

score 2 · Answer 2 · answered Nov 01 '18 at 12:48

2

Use CONCAT_WS():

select id, Name, 
       concat_ws(', ',
                 concat('phone_office: ', ph_office),
                 concat('ph_home: ', ph_home) 
                ) as phones
from contacts;

answered Nov 01 '18 at 12:48

Gordon Linoff

1,242,037
58
646
786

How to coalesce, concat and ignore nulls during select? MySql

2 Answers2