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.
| Author |
Topic |
|
kpgraci
Yak Posting Veteran
68 Posts |
 Posted - 2011-09-26 : 16:47:37
|
| I have a table with first, last, addr1, addr2, city, state, zip and zip4.I want a query that will return a column called FullAddr.So I could do this:SELECT [First] + ' ' + [Last] + ... AS FullAddr WHERE...One problem is I need to have CRLF's delimite each line, how would I do that in a query.My second and bigger problem, I don't want a line for Addr2 if it is not answered (blank or null) and I don't want a hyphen between zip and zip4 if zip4 is unanswered. (the addr2 issue is more important)Can this be done?kpg |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-27 : 00:45:30
|
for dealing with blank/null address lines you can do likeSELECT [First] + ' ' + [Last] + COALESCE(NULLIF(Addr2,'')+CHAR(13) + CHAR(10),'')+... zip+COALESCE('-'+nullif(zip4,''),'') AS FullAddr WHERE...the CRLF should be implemented at your front end as far as possible------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
kpgraci
Yak Posting Veteran
68 Posts |
Posted - 2011-09-28 : 09:19:39
|
| Resolvedtkizer's suggestion is best, I'm sure, I'm binding directly to a dataset so I just need to figure out how to format with that setup, I'm sure it can be done.thx visakh16 for the CRLF example.kpg |
|
|
|
|
|
|
|
|
Full Address column