Concatenating columns in SQL with delimiter. If first column is null, do not want delimiter to show-CodePudding

SELECT TOP 1000 places.*
    , home_Desc
    , CONCAT_WS(' - ', COALESCE(brand, ' '), home_Desc, location_Desc)  AS homeSite

Example:

brand	home_Desc	location_Desc
blue	large	woody
NULL	small	forest

So right now I am getting:

1.' blue - large - woody '
2. ' - small - forest '

But what I want for the second set is:

small - forest

CodePudding user response：

CREATE TABLE TEST(brand VARCHAR(100), home_Desc VARCHAR(100), location_Desc VARCHAR(100))

INSERT INTO TEST VALUES ('blue','large','woody'),
(NULL,'small','forest')

SELECT CONCAT_WS(' - ',brand, home_Desc, location_Desc) FROM TEST

As Squirrel say, you must replace COALESCE(brand, '') to brand

CodePudding user response：

Taking advantage of the rule a non-NULL value (the dash character in this instance) concatenated with a NULL value (branch, in this instance) would yield NULL (and assuming that the other two can never be null/blank:

, COALESCE(brand   '-', ' ')   home_Desc   ' - '   location_Desc AS homeSite