CODEWITHSUNDEEP
mysqlconcatenationconcat-ws
Trying my Best
Trying my Best

Reputation: 35

How do I stop a comma being added using CONCAT_WS if column is empty

SELECT CONCAT_WS(',', Col1, Col2, Col3 ) AS combined FROM table_name

Currently if I have fields Col1 = 'hello', Col2 = 'Bye', Col2 = 'Goodnight'

The above select statement would return hello,Bye,Goodnight which is fine.

What if any of the Cols where blank. A Comma would still be added. like hello,,Goodnight.

Is there a way to say if the Col is empty then ignore it? (This is MySQL)

Upvotes: 3

Views: 2103

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

Function CONCAT_WS ignores NULL values and doesn't generate a separator if either of its arguments is NULL.

For empty, i.e. equal to '', values you can use NULLIF:

SELECT CONCAT_WS(',', NULLIF(Col1, ''), NULLIF(Col2, ''), NULLIF(Col3, '') ) AS combined 
FROM table_name

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1269823

CONCAT_WS() ignores NULL values (after the first argument). Hence, your blank values must be empty strings and not NULL.

You can use the NULLIF() function:

SELECT CONCAT_WS(',', NULLIF(Col1, ''), NULLIF(Col2, ''), NULLIF(Col3, '')
                ) AS combined
FROM table_name;

Upvotes: 3

Related Questions