Why does this return .
when col1 contains a blank value?
CONCAT(NULLIF([COL1],''),'.')
I have 3 columns that i need to concatenate with a .
in between, sometimes the column contains a blank value. In that case the trailing .
should not be concatenated. What functions do I use?
col1 col2 col3
A 1 x
B 2
expected results:
A.1.X
B.2
test code:
DECLARE @tbl TABLE(a varchar(100),b varchar(100),c varchar(100))
INSERT INTO @tbl
SELECT 'A','1','X' UNION
SELECT 'B','2','' UNION
SELECT 'C','','' UNION
SELECT '','1','X' UNION
SELECT 'B','','' UNION
SELECT 'C','',''
SELECT CONCAT ( Nullif(a,''),'.' + nullif(b,''), '.' + nullif(c,'')) AS Contact_Result FROM @tbl;
THIS IS AN ANSWER THAT COVERS ALL POSSIBILITIES
SELECT SUBSTRING(CONCAT ('.' + NULLIF(a,''),'.' + NULLIF(b,''),'.' + NULLIF(c,'')),2,10000) AS Contact_Result FROM @tbl;
Complete test cases
DECLARE @tbl TABLE(a varchar(100),b varchar(100),c varchar(100))
INSERT INTO @tbl
SELECT 'a','','' UNION
SELECT 'a','b','' UNION
SELECT 'a','b','c' UNION
SELECT 'a','','c' UNION
SELECT '','b','c' UNION
SELECT '','b','' UNION
SELECT '','','c' UNION
SELECT '','',''
SELECT SUBSTRING(CONCAT ('.' + NULLIF(a,''),'.' + NULLIF(b,''),'.' + NULLIF(c,'')),2,10000) AS Contact_Result FROM @tbl;
Results
c
b
b.c
a
a.c
a.b
a.b.c