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 :
- 3050 LakedTest,, CityTest
- 3050 LakedTest,CityTest
- (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.