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
-
1Duplicate of stackoverflow.com/questions/2811733/…Konerak– Konerak2010-05-12 06:15:44 +00:00Commented May 12, 2010 at 6:15
Add a comment
|
3 Answers
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.
Comments
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.
Comments
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, '')