Search code examples
sqlsql-serverstringstring-concatenation

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?

Solution

  • 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, '')

    ISNULL

    Replaces NULL with the specified replacement value.

    COALESCE

    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