Sql string adding problem
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 t开发者_StackOverflow中文版o bypass this problem msSQL 2005
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.
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.
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, '')
精彩评论