HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
thecommanullemptyvaluefrontnotstringadd

Problem

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

Solution

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

Code Snippets

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 = ''

Context

StackExchange Database Administrators Q#205342, answer score: 9

Revisions (0)

No revisions yet.