Sql string adding problem

Question

SELECT a.one + ' test ' +b.two from table1 a right join table1 on a.id =b.id 

The problem is that when one is null then it the whole string is null, is there some kind of trick to bypass this problem msSQL 2005

Answer 1

You are looking for the ISNULL function:

SELECT ISNULL(a.one,'') + ' test ' + ISNULL(b.two , '')
from table1 a 
right join table1 b 
  on a.id =b.id 

If the first argument of the ISNULL function is null, then the second argument is supplied. This way, none of the concatenated fields will return a null and you will get a string and not a null.

Answer 2

It depends on what you want the outcome to be when one or both inputs is null. If you just want each part to collapse to an empty string, use ISNULL:

ISNULL(a.one, '') + ' test ' + ISNULL(b.two, '')

Otherwise, you'll have to get clever with a CASE expression.

Answer 3

There are several variations depending on what output you want

-- leading/trailing spaces on test
SELECT ISNULL(a.one,'') + ' test ' + ISNULL(b.two , '')

-- no spacing around test
SELECT ISNULL(a.one,' ') + 'test' + ISNULL(' ' + b.two, '')

-- do you want the word test at all if one is blank?
SELECT ISNULL(a.one + ' test','') + ISNULL(' ' + b.two, '')