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

sql servert-sql

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])

Best Answer

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.

Related Question