CODEWITHSUNDEEP
mysql
404
404

Reputation: 1

Is there a way to not show the separator in CONCAT_WS when trying to concatinate empty or NULL row's?

SELECT DISTINCT id, CONCAT_WS(' & ', Seek1,Seek2,Seek3) FROM sales

It group's the Seek1-3 values which seem to work fine, but it also adds & when the Seek2 and/or Seek3 field is NULL of empty. Is there a way to not show the seperator in CONCAT_WS when trying to concatinate empty or NULL valued rows?

Upvotes: 0

Views: 339

Answers (1)

Paul Spiegel
Paul Spiegel

Reputation: 31832

CONCAT_WS() does ignore NULLs. Example:

SELECT CONCAT_WS(' & ', 'a', NULL, 'b');

will return

a & b

However - It doesn't ignore empty strings:

SELECT CONCAT_WS(' & ', 'a', '', 'b');

returns

a & & b

But you can convert empty strings to NULLs with the NULLIF() function

SELECT CONCAT_WS(' & ', 'a', NULLIF('', ''), 'b');

returns

a & b

So you can write your query as:

SELECT DISTINCT id, CONCAT_WS(' & ', NULLIF(Seek1, ''), NULLIF(Seek2, ''), NULLIF(Seek3, ''))
FROM sales

Upvotes: 3

Related Questions