adding two string fields when one is NULL.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

adding two string fields when one is NULL.

Author

Topic

Brother52
Starting Member

9 Posts

Posted - 2005-04-25 : 13:12:30

Hi there,

This should be pretty straight forward, but I can't seem to find a solution...

I have a table that contact address information, and I want to combine the city, state, and zip fields into one field.

But, if any of them are NULL, then the result is also NULL.

I am using the following to combine the fields....
tOrder.city +' '+tOrder.city+' '+tOrder.zip as AddressInfo

I know I can use SET CONCAT_NULL_YIELDS_NULL OFF to turn off NULL fields option, but I don't want to do this.

Is there way, using a SELECT statement, that will combine all these felds together when one of them is NULL?

Thanks in advance,

Rowan.

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-25 : 13:22:24

[code]
USE Northwind
GO

SELECT COALESCE(ShipAddress,'') + CHAR(13) + CHAR(10)
+ COALESCE(ShipCity,'') + ', ' + COALESCE(ShipRegion,'') + CHAR(13)+CHAR(10)
+ COALESCE(ShipPostalCode,'') + CHAR(13) + CHAR(10)
+ '------------------------------------------------------------------------------'
FROM Orders

[/code]

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources