SQL Server String Concatenation with Null

Question

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?

Disparage

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. – Ingenuity

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? – Countless

Answer 1

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.

Elegy

"Coalesce" is the ANSI-standard function name, but ISNULL is easier to spell. – Tram

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: sqlblog.com/blogs/adam_machanic/archive/2006/07/12/…) – Elegy

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. – Desperation

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) – Bolen

@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 – Elegy

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 – Longhorn

Answer 2

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*/

Rockbottom

For older versions you get "'CONCAT' is not a recognized built-in function name", so use COALESCE – Lisp

@Lisp - COALESCE wont work because it doesn't concatenate, it just returns the first non null argument – Virtuosity

Answer 3

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

Jesuitism

OP wants to concat strings together, COALESCE wont do that – Virtuosity

@Virtuosity 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 – Rockbottom

Answer 4

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 

Robespierre

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 :( – Downandout

...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) – Downandout

Answer 5

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.

Date

Answer 6

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!

Pandowdy

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) – Gorizia

Answer 7

ISNULL(ColumnName, '')

Orff

Answer 8

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.

Repetitious

Answer 9

In Sql Server:

insert into Table_Name(PersonName,PersonEmail) values(NULL,'[email protected]')

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+"')

Castellatus

Answer 10

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'

Humus