How to get rid of comma if no other value comes after the string

Question

I would like to not have a comma when there is a no value after string but I keep getting back a comma even if there is a value

  select 
                company_no,
                company_name, 
                concat(address_1 
                         ,CASE WHEN NULLIF(address_1,'') <> ''  THEN ', ' 
                               ELSE '' 
                          END) + concat(address_2
                         ,CASE WHEN NULLIF(address_2,'')<> ''  THEN ', ' 
                               ELSE '' 
                          END) + concat(address_3
                         ,CASE WHEN NULLIF(address_3,'')<> '' THEN ', ' 

                               ELSE '' 
                          END)+
                           concat(address_4
                         ,CASE WHEN NULLIF(address_4,'')<> ''  THEN ', ' 
                               ELSE '' 
                          END)+
                           concat(address_5
                         ,CASE WHEN NULLIF(address_5,'')<> ''  THEN ' ' 
                               ELSE '' 
                          END) as Address,
                code.code_desc as company_status

                from company_tbl company
                inner join code_tbl code on company.company_status = code.code and code.code_type = 'COM' and code_sub_type = 'CIR'


                where company_name like company_name +'%'
                and company_type = 'EOC'
                and address_3 is null

What I am getting :

1. 3050 LakedTest,, CityTest
2. TBC,
3. NULL

Desired output

1. 3050 LakedTest,CityTest
2. TBC
3. (Want this just to be blank)

Update : I had to add a the REPLACE function to get rid of commas that were in the String values.

Answer 1

Use STUFF function, or if your server version is 2017 or higher just use CONCAT_WS

DECLARE @Test TABLE (
    address_1   VARCHAR(10),
    address_2   VARCHAR(10),
    address_3   VARCHAR(10),
    address_4   VARCHAR(10),
    address_5   VARCHAR(10)
)

INSERT @Test
VALUES
('AAA', 'BBB', 'CCC', 'DDD', 'EEE'),
(NULL, 'BBB', 'CCC', 'DDD', 'EEE'),
(NULL, NULL, NULL, 'DDD', 'EEE'),
(NULL, NULL, NULL, NULL, NULL),
(NULL, NULL, NULL, NULL, 'EEE'),
('AAA', NULL, NULL, NULL, 'EEE'),
('AAA', NULL, NULL, NULL, NULL)

SELECT 
ISNULL(
    STUFF(
        CONCAT ( 
            ', ' + NULLIF(REPLACE(address_1, ',', ''), ''),
            ', ' + NULLIF(REPLACE(address_2, ',', ''), ''),
            ', ' + NULLIF(REPLACE(address_3, ',', ''), ''),
            ', ' + NULLIF(REPLACE(address_4, ',', ''), ''),
            ', ' + NULLIF(REPLACE(address_5, ',', ''), '')
        ),
        1,
        2,
        ''
    ),
    ''
)
FROM @Test

Answer 2

select company_no,
       company_name, 
       COALESCE(concat_ws(', ',
                          NULLIF(address_1,''),
                          NULLIF(address_2,''),
                          NULLIF(address_3,''),
                          NULLIF(address_4,''),
                          NULLIF(address_5,'')), '') as Address,
       code.code_desc as company_status

from company_tbl company
inner join code_tbl code on company.company_status = code.code 
                        and code.code_type = 'COM' 
                        and code_sub_type = 'CIR'

where company_name like company_name +'%' -- ???
  and company_type = 'EOC'
  and address_3 is null -- ???

---

company_name like company_name +'%' is extremely strange - it is always true except the case when company_name is NULL.

and address_3 is null is strange too - more precise it is strange why this field is nevertheless included into output expression.

Answer 3

Is this due to comparing NULL values? For instance, instead of saying

CASE WHEN NULLIF(address_2,'') <> '' THEN ', ' ELSE '' END

Would you rather say

CASE WHEN ISNULL(NULLIF(address_2,''),'1') <> '1' THEN ', ' ELSE '' END

Using ISNULL avoids the issue of comparing NULL values since you're now setting the expression equal to 1 if it's NULL, then comparing 1 to 1. SQL does not know how to compare NULL to NULL, and so any statement comparing NULLs will always evaluate to TRUE.

Edit: had the wrong comparison operator in the second statement, should be <>.