Have Geek Brain

Sql row adding issue

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

The problem is when one is null then the whole string is null, is there any trick to get around this msSQL 2005 issue

+2


a source to share


3 answers


You are looking for a function ISNULL

:

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


If the first argument to the function ISNULL

is zero, then the second argument is provided. This way, none of the concatenated fields will return null and you will end up with a string, not null.

+3


a source


It depends on what you want the result to be when one or both inputs are zero. If you just want every part to collapse on an empty string, use ISNULL:

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


Otherwise you will need to get a smart CASE expression.

+2


a source


There are several options depending on which one 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, '')
+1


a source






More articles:


All Articles