Concatenation with Null in SQL Server

Question

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.

The COALESCE() or ISNULL() functions or conditions could help. When the CONCAT_NULL_YIELDS_NULL option is ON, you have to handle NULL values manually. It is always recommended to do so. — Pred
– Pred, Commented May 11, 2016 at 14:25

PhilS · Accepted Answer · 2016-05-11 16:02:49Z

4

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.

edited May 11, 2016 at 16:02

answered May 11, 2016 at 14:23

PhilS

1,66114 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Stefan Steinegger Over a year ago

I was just about to write this very same thing.

Thorsten Dittmar Over a year ago

Would that work no matter what ANSI_NULLS is set to? I think I remember there's a setting in which x + null <> null...

PhilS Over a year ago

@ThorstenDittmar: ANSI_NULLS = OFF allows for equality comparison with NULL. E.g. WHERE Something = NULL. However, I added an info on CONCAT_NULL_YIELDS_NULL which probably is what you were remembering.

Thorsten Dittmar · Accepted Answer · 2016-05-11 14:27:54Z

0

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

edited May 11, 2016 at 14:27

answered May 11, 2016 at 14:22

Thorsten Dittmar

56.9k10 gold badges95 silver badges145 bronze badges

Comments

Menelaos Vergis · Accepted Answer · 2016-05-11 14:33:08Z

0

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.

answered May 11, 2016 at 14:33

Menelaos Vergis

3,9855 gold badges35 silver badges51 bronze badges

3 Comments

joe Over a year ago

+Mr. Vergis, very useful techniques. Thanks a lot

Thorsten Dittmar Over a year ago

Is x + null == null always? I think I remember there's a setting where this is not the case...

Menelaos Vergis Over a year ago

@ThorstenDittmar Yes, there is a setting to change this default behavior but I have never encounter any DB with this setting turned off.

Collectives™ on Stack Overflow

Concatenation with Null in SQL Server

3 Answers 3

3 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related