Selecting non-blank data from 1 of 3 columns

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

General SQL Server Forums

New to SQL Server Programming

Selecting non-blank data from 1 of 3 columns

Author

Topic

CharleyT
Starting Member

2 Posts

Posted - 2010-06-23 : 16:21:10

I have the following statement:
select cust_num, last_name, first_name, company, address, city_state, state, zip, phone, phone_h, phone_w, last_act, total_pur, internet, field8, field9, lastinvdat from cust order by cust_num

Everything is perfect, except the following two items I do not know how to do:

There are three columns that may or may not have data in them, so I would like return a field called "email" that contains the results from:

internet as email if filled
field8 as email if internet is blank and field8 has data
field9 as email if internet and field8 are blank, and field9 has data.

I would also like to return a field called "phone" that contains the results from:

phone as phone is phone is filled
phone_h as phone if phone is blank and phone_w has data
phone_w as phone if phone and phone_h are blank and phone_w has data

Would someone know a way to do this? I would appreciate any help I can get!

Charley

Charley

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-06-23 : 16:42:24

[code]select
case
when internet > '' then internet
when field8 > '' then field8
when field9 > '' then field9
else '' end as email
from
Mytable[/code]

CODO ERGO SUM

CharleyT
Starting Member

2 Posts

Posted - 2010-06-23 : 17:19:36

Thanks Kernel! A question if I may. Can I add two case statements in the middle of my select statement? So it would look like:
select cust_num, last_name, first_name, company, address,;
city_state, state, zip, last_act, total_pur, lastinvdat;

case;
when internet > '' then internet;
when field8 > '' then field8;
when field9 > '' then field9;
else '' end as email;
case;
when phone > '' then phone;
when phone_h > '' then phone_h;
when phone_w > '' then phone_w;
else '' as phone;

from cust order by cust_num

Charley

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-06-23 : 17:26:32

You should just try things like that to see what you get, instead of asking us. You'll find out faster.

Hint: a ; only goes at the ens of a statement.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources