CODEWITHSUNDEEP
sqlsql-servert-sql
Woland
Woland

Reputation: 1080

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 to bypass this problem msSQL 2005

Upvotes: 2

Views: 132

Answers (3)

gbn
gbn

Reputation: 432662

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

Upvotes: 1

Marcelo Cantos
Marcelo Cantos

Reputation: 186108

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.

Upvotes: 2

Oded
Oded

Reputation: 499382

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.

Upvotes: 3

Related Questions