Do not add a comma in front of the string if value is null or empty

Question

If my result is null or empty I would like it not to add a comma in front of the string. How can I achieve that?

 Update Companies 
 set address4 = concat(address4,','+ t.Eircode) 
 From companies c
 Inner Join Temptbl1 t
 on c.comp_id = t.Comp_ID
 OR address4 = ''

Update :

Just to make it clear when I make this update

 ,V05 TTX1 
 Waterford,X01 B234
 ,B90 E902
 Co Wexford,TD2 PVE2

It adds a comma regardless ,if it was empty or not

What I want to get back is this :

 V05 TTX1 
 Waterford,X01 B234
 B90 E902
 Co Wexford,TD2 PVE2

If there is no value then just add the string without the comma ,in our case V05 TTX1 without , at the start of the string

CREATE TABLE [dbo].[companies](
 [comp_id] [int] NOT NULL,
 [address4] [varchar](32) NOT NULL
)
INSERT INTO [dbo].[companies]
 ([comp_id]
 ,[address4])

• Aaron Bertrand: Why are you storing this calculation? You can always derive it when you need it. Otherwise you're going to have to have triggers on both tables to keep it up to date. ALSO: What is OR address4 = '' supposed to be doing? This will actually corrupt your data by introducing many more matches than you probably intend.
• Lucy: @AaronBertrand You are right I dont need OR address4 = '' The temptbl1 is just an excel sheet I imported from Sql server , that table won't be used ,I'm just using it to accomplish my task ,I was give a list of IDs where I had to update their addresses

Answer 1

I assume t.Eircode is what concerns you. You can use a CASE statement:

SET address4 = concat(address4 
 ,CASE WHEN NULLIF(address4 ,'') IS NOT NULL 
 THEN ','+ t.Eircode 
 ELSE '' 
 END)

I also added a NULLIF function that maps '' to null so that '' and null are treated as the same.

EDIT: Given the information that address4 is NOT NULL the test can be changed to:

SET address4 = concat(address4 
 ,CASE WHEN address4 <> '' 
 THEN ',' 
 ELSE '' 
 END) + t.Eircode

It is my understanding that t.Eircode should always be concatenated, and that it is the "," that should be conditionally concatenated so I moved it out from the CASE.

• Lucy: Just change NULLIF(t.Eircode,'') to NULLIF(t.address4,'')
• Aaron Bertrand: @Lucy t has an address4? I thought that column was only in c? This is why CREATE TABLE and INSERT to set up sample data are useful, as is always prefixing all columns with the table/alias they come from.
• Lucy: NULLIF(t.Eircode,'') to NULLIF(address4,'') ,sorry forgot to leave out t ,I'll add the CREATE TABLE and INSERT just to make it clearer to people
• Lucy: @Lennart ,Yes that's true t.Eircode should always be there

Answer 2

Since you are already combining CONCAT and + to concatenate strings you could just turn them around (although I agree Lennert's solution shows the intent a lot clearer).

Update Companies 
-- set address4 = concat(address4,','+ t.Eircode) 
set address4 = concat(address4 + ',', t.Eircode) 
From companies c
Inner Join Temptbl1 t
on c.comp_id = t.Comp_ID
OR address4 = ''

This works because CONCAT converts NULL into an empty string of type varchar(1), but as long as CONCAT_NULL_YIELDS_NULL is on concatenating a string with NULL will yield NULL.

See this dbfiddle for an example

• RDFozz: I believe this would allow trailing commas if t.Eircode is NULL; presumably, the OP only wants a comma if both address4 and t.Eircode are populated.
• Tom V: @RDFozz That was not in the sample code and the OP's edit to Lennarts answer seems to confirm that, but you could just use + for all three strings
• wp78de: Combining and CONCAT and + is indeed the way to go.

Answer 3

I usually do this by nesting ISNULL and NULLIF:

UPDATE Companies
SET address4 = ISNULL(NULLIF(address4, '') + ',', '') + t.Eircode
FROM Companies c
INNER JOIN Temptbl1 t
 ON c.comp_id = t.Comp_ID
 OR address4 = ''