Simon Foster Simon Foster - 1 year ago 54
SQL Question

Handling NULL in Sql server string concatenation

I have the following SQL query

select s.comments + s.further_comments from dbo.samples s where id = 1234

However if s.comments or s.further_comments is NULL the whole string is returned NULL

How do I convert the NULL value to an empty string or at least only return the non NULL values in this string?


Answer Source

You can use either ISNULL or COALESCE for this.

SELECT ISNULL(s.comments, '') + ISNULL(s.further_comments, '')
SELECT COALESCE(s.comments, '') + COALESCE(s.further_comments, '')


Replaces NULL with the specified replacement value.


Returns the first nonnull expression among its arguments.

Note that there are some differences between the two methods but for all intents and purposes, they most likely don't apply to your situation.

  1. ISNULL(NULL, NULL) -- is int
  2. COALESCE(NULL, NULL) -- Will throw an error
  3. COALESCE(CAST(NULL as int), NULL) -- it valid and returns int
  4. ISNULL takes only 2 parameters whereas COALESCE takes variable number of parameters
  5. COALESCE is based on the ANSI SQL standard whereas ISNULL is a proprietary TSQL function