99

I am creating a computed column across fields of which some are potentially null.

The problem is that if any of those fields is null, the entire computed column will be null. I understand from the Microsoft documentation that this is expected and can be turned off via the setting SET CONCAT_NULL_YIELDS_NULL. However, there I don't want to change this default behavior because I don't know its implications on other parts of SQL Server.

Is there a way for me to just check if a column is null and only append its contents within the computed column formula if its not null?

Alex
  • 75,813
  • 86
  • 255
  • 348
  • 3
    The accepted answer was right at the time the question was asked but for everyone on SQL Server 2012 and later (and that this stage that should be everyone) @Martin-Smiths answer is the best as it handles nulls automatically. – Dowlers Aug 15 '19 at 17:16
  • It's weird that this is the default, I can't think of a scenario why I'd want it to return null, any suggestions? – Paul McCarthy May 17 '22 at 11:06

10 Answers10

159

You can use ISNULL(....)

SET @Concatenated = ISNULL(@Column1, '') + ISNULL(@Column2, '')

If the value of the column/expression is indeed NULL, then the second value specified (here: empty string) will be used instead.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 28
    "Coalesce" is the ANSI-standard function name, but ISNULL is easier to spell. – Philip Kelley May 26 '10 at 21:08
  • 1
    And ISNULL seems to be a tad faster on SQL Server, too - so if you want to use it in a function that concatenates strings into a computed column, you might forgo the ANSI standard and opt for speed (see Adam Machanic: http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/performance-isnull-vs-coalesce.aspx) – marc_s May 26 '10 at 21:15
  • Just used this Isnull(,) query, it heped a lot as I was concatenating values together and if one of them was null everything became null too. – Sizons Feb 03 '16 at 10:33
  • 1
    Using `ISNULL()` is a good solution but from SQL Server 2012 on, you can also use `CONCAT` function to get the same result: `CONCAT(@Column1, @Column2)` – Muhammad Musavi Jun 24 '19 at 08:20
  • @Mohammadlm71: correct - but this answer was from 2010, where SQL Server 2012 wasn't around yet ; if you're still on 2005, 2008 or 2008 R2, then this is the way to go – marc_s Jun 24 '19 at 08:36
  • 2
    It's worth noting here that if you want to swap `null` for something other than an empty string, i.e. `IsNull(@Column1, 'NULLVALUE')`, with `IsNull` the replacement string length is limited to the length of the column it's replacing, while it isn't with `Coalesce` – Jamie Oct 29 '19 at 16:13
77

From SQL Server 2012 this is all much easier with the CONCAT function.

It treats NULL as empty string

DECLARE @Column1 VARCHAR(50) = 'Foo',
        @Column2 VARCHAR(50) = NULL,
        @Column3 VARCHAR(50) = 'Bar';


SELECT CONCAT(@Column1,@Column2,@Column3); /*Returns FooBar*/
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • For older versions you get "'CONCAT' is not a recognized built-in function name", so use COALESCE – Savage Jul 26 '16 at 15:55
  • 5
    @Savage - COALESCE wont work because it doesn't concatenate, it just returns the first non null argument – codeulike Nov 21 '18 at 16:35
35

Use COALESCE. Instead of your_column use COALESCE(your_column, ''). This will return the empty string instead of NULL.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • OP wants to concat strings together, COALESCE wont do that – codeulike Nov 21 '18 at 16:36
  • @codeulike you would use this to replace null with an empty string and then use the result of that with the old string concatenation operator (`+`) - in the same way as the accepted answer is doing with `ISNULL` – Martin Smith Jan 19 '22 at 10:25
16

You can also use CASE - my code below checks for both null values and empty strings, and adds a seperator only if there is a value to follow:

SELECT OrganisationName, 
'Address' = 
CASE WHEN Addr1 IS NULL OR Addr1 = '' THEN '' ELSE Addr1 END + 
CASE WHEN Addr2 IS NULL OR Addr2 = '' THEN '' ELSE ', ' + Addr2 END + 
CASE WHEN Addr3 IS NULL OR Addr3 = '' THEN '' ELSE ', ' + Addr3 END + 
CASE WHEN County IS NULL OR County = '' THEN '' ELSE ', ' + County END 
FROM Organisations
Eddie
  • 411
  • 4
  • 6
  • I marked this up but I now think there is a problem with it: if the first string, Addr1, is NULL or empty and the second or third or fourth string, Addr2 or Addr3 or County, is not, then the address will start with an unnecessary comma. Pity, I like the idea and clarity :( – Zeek2 Sep 16 '21 at 15:51
  • ...but you could use TRIM() to fix that (remove leading, & trailing, commas). e.g. I am using SQL variables and use something like this: SET @strAddress = TRIM(',' FROM @strAddress) – Zeek2 Sep 17 '21 at 10:55
11

Use 

SET CONCAT_NULL_YIELDS_NULL  OFF

and concatenation of null values to a string will not result in null.

Please note that this is a deprecated option, avoid using. See the documentation for more details.

GrandMasterFlush
  • 6,269
  • 19
  • 81
  • 104
Simran
  • 539
  • 2
  • 8
  • 28
10

I just wanted to contribute this should someone be looking for help with adding separators between the strings, depending on whether a field is NULL or not.

So in the example of creating a one line address from separate fields

Address1, Address2, Address3, City, PostCode

in my case, I have the following Calculated Column which seems to be working as I want it:

case 
    when [Address1] IS NOT NULL 
    then (((          [Address1]      + 
          isnull(', '+[Address2],'')) +
          isnull(', '+[Address3],'')) +
          isnull(', '+[City]    ,'')) +
          isnull(', '+[PostCode],'')  
end

Hope that helps someone!

Java Devil
  • 10,629
  • 7
  • 33
  • 48
ebooyens
  • 608
  • 3
  • 9
  • 21
  • There's quite a bit of redundant nested bracketing there that could be removed. Another tip is that you could also remove the case statement as if address1 is null the whole expression will evaluate to null (though having the case statement does draw attention that this can happen) – Alternator Feb 24 '14 at 23:03
7

ISNULL(ColumnName, '')

Ian Jacobs
  • 5,456
  • 1
  • 23
  • 38
2

I had a lot of trouble with this too. Couldn't get it working using the case examples above, but this does the job for me:

Replace(rtrim(ltrim(ISNULL(Flat_no, '') + 
' ' + ISNULL(House_no, '') + 
' ' + ISNULL(Street, '') + 
' ' + ISNULL(Town, '') + 
' ' + ISNULL(City, ''))),'  ',' ')

Replace corrects the double spaces caused by concatenating single spaces with nothing between them. r/ltrim gets rid of any spaces at the ends.

Gustavo Morales
  • 2,614
  • 9
  • 29
  • 37
BryDav
  • 21
  • 1
0

In Sql Server:

insert into Table_Name(PersonName,PersonEmail) values(NULL,'xyz@xyz.com')

PersonName is varchar(50), NULL is not a string, because we are not passing with in single codes, so it treat as NULL.

Code Behind:

string name = (txtName.Text=="")? NULL : "'"+ txtName.Text +"'";
string email = txtEmail.Text;

insert into Table_Name(PersonName,PersonEmail) values(name,'"+email+"')
Shreyos Adikari
  • 12,348
  • 19
  • 73
  • 82
Srinivasula Reddy
  • 31
  • 2
-1

This example will help you to handle various types while creating insert statements

select 
'insert into doc(Id, CDate, Str, Code, Price, Tag )' + 
'values(' +
      '''' + convert(nvarchar(50), Id) + ''',' -- uniqueidentifier
    + '''' + LEFT(CONVERT(VARCHAR, CDate, 120), 10) + ''',' -- date
    + '''' + Str+ ''',' -- string
    + '''' + convert(nvarchar(50), Code)  + ''',' -- int
    + convert(nvarchar(50), Price) + ',' -- decimal
    + '''' + ISNULL(Tag, '''''') + '''' + ')'  -- nullable string

 from doc
 where CDate> '2019-01-01 00:00:00.000'
Akmal Salikhov
  • 818
  • 3
  • 12
  • 25