CODEWITHSUNDEEP
sqlt-sqlconcatenation
joe
joe

Reputation: 1473

Concatenation with Null in SQL Server

I have a simple code below.

Select columnA, columnB, columnC
  From table.

Here is my result.

ColumnA  ColumnB  ColumnC
Apple    G        null
Juice    S        T9

Now, I use the concatenation as follow

Select 
  ColumnA + '_'+ ColumnB + '_' + ISNULL(ColumnC, '') as Name
  From table

My output is as below 

Name
Apple_G_
Juice_S_T9

How do I modify the concatenation above so it would show as Apple_G instead of Apple_G_ Basically, I have an extra _ from my result.

Upvotes: 2

Views: 76

Answers (3)

PhilS
PhilS

Reputation: 1661

Just concatenate the underscore with the nullable column before you apply the IsNull function.

Select 
  ColumnA + '_'+ ColumnB + ISNULL('_' + ColumnC, '') as Name
  From table

This requires the CONCAT_NULL_YIELDS_NULL option being set to ON. - Which it is by default and which is very strongly recommended by Microsoft.

Upvotes: 4

Menelaos Vergis
Menelaos Vergis

Reputation: 3945

You can use COALESCE that it will return the first not null expression

Select COALESCE(ColumnA + '_'+ ColumnB + '_' + ColumnC, ColumnA + '_'+ ColumnB) as Name
  From table

or if you want to customize it in a deeper level

Select COALESCE(ColumnA + '_'+ ColumnB + '_' + ColumnC, 
                ColumnA + '_'+ ColumnB,
                ColumnA ) as Name
  From table

The whole solution relies to the SQL server feature that if any of the concatenated strings is null the complete string becomes null.

Upvotes: 0

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56697

Use either IIF (SQL Server 2014+) or CASE:

IIF 

Select 
    ColumnA + '_' + 
    ColumnB + 
    IIF(ColumnC IS NOT NULL, '_' + ColumnC, '')
From table

CASE 

Select 
    ColumnA + '_' + 
    ColumnB + 
    CASE 
        WHEN ColumnC IS NOT NULL THEN '_' + ColumnC
        ELSE ''
    END
From table

Upvotes: 0

Related Questions