mysql concatenate columns if column is not null

Question

Here is the table schema;

create table campaign (
    id INT NOT NULL,
    custom_param1 VARCHAR(15),
    custom_value1 VARCHAR(15),
    custom_param2 VARCHAR(15),
    custom_value2 VARCHAR(15),
    custom_param3 VARCHAR(15),
    custom_value3 VARCHAR(15),
    PRIMARY KEY (id)
)

and this is the mock data I have;

id  custom_param1   custom_value1   custom_param2   custom_value2   custom_param3   custom_value3
--  -------------   -------------   -------------   -------------   -------------   -------------
1   param1          value1          param2          value2          param3          value3
2   param1          value1          param2          value2          param3          value3
3   param1          value1          param2          value2          param3          value3
4   param1          value1          param2          value2          NULL            NULL
5   param1          value1          NULL            NULL            NULL            NULL

Now I am trying to concatenate params and values columns like a format

param1=value1; param2=value2, param3=value3

if a row has only two params and values it should be like;

param1=value1; param2=value2

well I create a query which I can concatenate row but if one of the is null then whole row is become a null - As I know if concat function tries to concat a NULL value string will completely become null. And this is query I use.

select CONCAT(c.custom_param1, '=', c.custom_value1, '; ',
                c.custom_param2, '=', c.custom_value2, '; ',
                c.custom_param3, '=', c.custom_value3) as 'Custom Parameters'
from campaign as c;

I will appreciate for any kind of help. Thanks anyway

Answer 1

Depending on what you want to do if the value is null, you can try

SELECT CONCAT(
c.custom_param1, '=', IFNULL(c.custom_value1, ''), '; ',
c.custom_param2, '=', IFNULL(c.custom_value2, ''), '; ',
c.custom_param3, '=', IFNULL(c.custom_value3, ''), '; ') as 'Custom Parameters'
FROM campaign as c;

Will return

param1=value1; param2=value2; param3=;

Or you can exclude the whole value pair like this....

SELECT CONCAT(
IFNULL(CONCAT(c.custom_param1, '=', c.custom_value1, '; '), ''),
IFNULL(CONCAT(c.custom_param2, '=', c.custom_value2, '; '), ''),
IFNULL(CONCAT(c.custom_param3, '=', c.custom_value3, '; '), '')) AS 'Custom Parameters'
FROM campaign as c;

which will return

param1=value1; param2=value2;

Hope that helps