patternsqlMinor
Do not add a comma in front of the string if value is null or empty
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 :
Just to make it clear when I make this update
It adds a comma regardless ,if it was empty or not
What I want to get back is this :
If there is no value then just add the string without the comma ,in our case
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 PVE2It 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 PVE2If there is no value then just add the string without the comma ,in our case
V05 TTX1 without , at the start of the stringCREATE 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
This works because
See this dbfiddle for an example
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.