concatenation Nullfields in T-SQL

Hi,
I have a list box for Address which shows available addresses for buildings.
I want the list box to concatenate the address and show it.
in Access I used to write :

Select [address] & " " & [city_] & " " & [state] & " " & [bldgzip] AS [Building Address] From tblAdrs

This would show the whole thing even if it had some Null fileds (it wouldn't show if I used + instead of &)

But I don't know why it won't do the same in T-SQL?

Can anyone help?on most servers Null + <any data type> = Null

you can disable this feature or use the ISNULL function.

Select isnull([address],'') + ' '+ isnull([city_],'') + ' '+ isnull([state],'') + ' '+ isnull([bldgzip],'') AS [Building Address] From tblAdrs|||Thanks Paul

Originally posted by Paul Young
on most servers Null + <any data type> = Null

you can disable this feature or use the ISNULL function.

Select isnull([address],'') + ' '+ isnull([city_],'') + ' '+ isnull([state],'') + ' '+ isnull([bldgzip],'') AS [Building Address] From tblAdrs