query help - 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)

query help

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-07-16 : 03:39:02

i have an sql statement

select participantid,lastname +',' +wifesname as cname from participants order by lastname,firstname

now the problem is if wifesname is blank then it shows a blank value as cname. I would like cname to just show the lastname if wifesname is blank -- can i do that in an sql select statement

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-16 : 04:45:56

Somthing like this


select participantid,lastname +',' +
Case When wifesname = '' or wifesname Is Null Then ' CName' Else wifesname End 
as cname from participants order by lastname,firstname

Chirag

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-16 : 23:16:19

"now the problem is if wifesname is blank then it shows a blank value as cname"
by blank do you mean empty string or NULL ? If column wifesname is an empty string,

lastname + ',' + wifesname

will not be blank but having value of lastname + ','.

Most probably your case of wifesname having NULL value. You can use ISNULL or COALESCE to handle this.

select participantid,
       coalesce(lastname, lastname + ',' + wifesname) as cname 
from   participants 
order by lastname, firstname

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-17 : 06:58:11

Here's my effort.

--data
declare @participants table (participantid int identity(1, 1), 
    lastname varchar(20), firstname varchar(20), wifesname varchar(20))
insert @participants
          select 'Smith', 'John', 'Sarah'
union all select 'Jones', 'Dave', ''
union all select 'Williams', 'Fred', null
union all select 'Cruise', 'Tom', '   '

--calculation
select participantid, lastname + isnull(', ' + nullif(wifesname, ''), '') as cname
from @participants
order by lastname, firstname

/*results
participantid cname                                      
------------- ------------------------------------------ 
4             Cruise
2             Jones
1             Smith, Sarah
3             Williams
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources