Search code examples
t-sqlconcatenationstring-concatenation

Concatenating NULL returns a value in T-SQL (CONCAT function)


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;

Solution

  • 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