Combining 2 fields one may be null

[Stever]

I created a seemingly simple query to combine 2 fields. It works as long as
there is information in both fields however if there is a null in either the
field is blank. Fields are of type NVARCHAR

This is what I would like to have happen.
Proj_No Alias_Number Proj_Alias_Number Currently the
results are
123 456 123 456
123 456
123 123
Blank
456 456
Blank


Here is what I have so far.
SELECT Proj_No + N' ' + Alias_Number AS Proj_Alias_Number,
FROM dbo.tblCivil_RecordScan


Thanks in advance!

Steve

 

[bcap]

SELECT RTRIM(ISNULL(Proj_No + N' ','') + ISNULL(Alias_Number,'')) AS
Proj_Alias_Number FROM dbo.tblCivil_RecordScan

 

[Stever]

Thanks for the response. I tried your suggestion however I get the same
result. Is there another way to combine fields that tolerates Nulls?

 

[Sylvain Lafontaine]

My first reaction would be to say that's impossible; unless you have made
some error while testing the piece of code given by Stever or that you are
not using an ADP project working against a SQL-Server.

 

[Stever]

So if I understand what you are saying.... It's impossible to combine them
if one if them is a null? If that's the case do you have an alternate
suggestion to achieve a similar result?

Thanks

Steve

 

[Sylvain Lafontaine]

No: it's impossible that the piece of code given by Stever doesn't work as
expected.

 

[Stever]

Hmm.... Strange... I tested the null problem by inserting a _ in a couple of
the records that had a null and they work fine after that so it must be
something with the null. I'm stumped. It seems so simple...

Steve

 

[Sylvain Lafontaine]

Quick test:

select a, b, RTRIM(ISNULL(a + N' ','') + ISNULL(b,'')) AS r FROM (
select 1 as k, 'a' as a, 'b' as b
union all select 2, null, 'b'
union all select 3, 'a', null
) as q order by k

 

[julien]

CC