Is it possible to use CONCAT_WS add separator only if value not empty

Question

Let say I have this table

id  a       b
1   data    1234
2   data

I want to concat with separator (but) if no data, don't add the separator.

If I do

UPDATE `table` SET `b` = CONCAT_WS(',',`b`,'newData') WHERE `id` = '1'

I get the expected 1234,newData in b

but I I do

UPDATE `table` SET `b` = CONCAT_WS(',',`b`,'newData') WHERE `id` = '2'

I get ,newData in b (( but I want only newData without the separator )).

IS there a way to do this ?

Avidos · Accepted Answer · 2017-04-03T04:15:57.043

You can try this one Mate:

# [A] sample result set for checking
SELECT 
    `a`, `b`,
    IF(
        (`a` IS NOT NULL AND `a` != '')
        AND (`b` IS NOT NULL AND `b` != ''),
        CONCAT_WS(',', `a`, `b`),
        REPLACE(CONCAT_WS(',', `a`, `b`), ',', '')
    ) `result`
FROM `table`
WHERE `id` IN (1, 2);

If the result set in [A] satisfied what you need, you can proceed with the script using [B]

The Update query:

# [B] process query for the new values
UPDATE `table`
SET `b` = IF(
    (`a` IS NOT NULL AND `a` != '')
    AND (`b` IS NOT NULL AND `b` != ''),
    CONCAT_WS(',', `a`, `b`),
    REPLACE(CONCAT_WS(',', `a`, `b`), ',', '')
)
WHERE `id` IN (1, 2);

Hope I can help, cheers!

Is it possible to use CONCAT_WS add separator only if value not empty

1 Answers1