NULL result - SQL Server Forums

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

Transact-SQL (2000)

NULL result

Author

Topic

NadJ
Starting Member

7 Posts

Posted - 2004-11-27 : 13:07:43

Hi guys,

A little stuck on this one. Would appreciate some assistance.

My Query is below:

Select ID, (Details + ', ' + Address + ', ' + Telephone_No + ', ' + Country) AS All_Details, Return_Date, File_Request from Table4 WHERE Country='USA'

The results I get are such that, if Details, Address or TelephoneNo is NULL but Country is not NULL the result displayed in each column is still NULL. I'd like to see the column display Country even if any of the others aren't present (i.e. are NULL). At present it's not doing this for me.

Many thanks for your help.

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2004-11-27 : 13:30:08

[code]
Select ID, coalesce(Details,'') + ', ' + coalesce(Address,'') + ', ' + coalesce(Telephone_No,'')
+ ', ' + coalesce(Country,'') AS All_Details
[/code]

NadJ
Starting Member

7 Posts

Posted - 2004-11-27 : 14:11:58

Thanks,

This poses another small problem. Where the resulting field is truly NULL, I still get the additional commas and other characters appear. So I can get a result of , , , for example. In my project I have various characters in place not just the commas. Tricky one?

jbkayne
Posting Yak Master

100 Posts

Posted - 2004-11-27 : 14:45:38

I'd go about it this way to start...

Select ID, isnull(Details + ', ','') + isnull(Address + ', ','') + isnull(Telephone_No + ', ','') + isnull(Country + ', ','') AS All_Details

Now just strip the last two characters. You could probably do this in a number of different ways.

Here is one way:

First get a completely blank line to be NULL versus ''.

Select ID, nullif(isnull(Details + ', ','') + isnull(Address + ', ','') + isnull(Telephone_No + ', ','') + isnull(Country + ', ',''),'') AS All_Details

Strip the last ', ' with a REPLACE. (I added \n to signify a newline) Otherwise I'd have to use a lengthy LEFT, LEN solution.

Select ID, replace(nullif(isnull(Details + ', ','') + isnull(Address + ', ','') + isnull(Telephone_No + ', ','') + isnull(Country + ', ',''),'') + '\n',', \n','') AS All_Details

That should do it. This is untested, so use at your own risk.

Sincerely,

jbkayne

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources