concatenating problem two field in case expression

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 2005 Forums

Transact-SQL (2005)

concatenating problem two field in case expression

Author

Topic

imranabdulaziz
Yak Posting Veteran

83 Posts

Posted - 2008-06-19 : 00:30:58

Dear all,
I am using sql server 2005.
I have two field firstname and lastname. Actual requirment is if category is doctor then display name as Dr. name else name but when I use following sql stament
Whenever one of the name is null it show name as null that is whenever firstname or lastname is null I get name as null


select  @DOCNAME  = CASE   
                          WHEN DR_CUST_CATEGORY = 1 THEN 'Dr.' + ' ' + dr_cust_name + dr_cust_lastname   
                          ELSE  dr_cust_name + dr_cust_lastname   
                          END  FROM DOCTOR_MASTER WHERE DR_CUST_DOCTORCODE =@DOCCODE

Please suggest some ideas or any other idea please suggest
Thank you.

raky
Aged Yak Warrior

767 Posts

Posted - 2008-06-19 : 00:48:08

quote:
Originally posted by imranabdulaziz

Dear all,
I am using sql server 2005.
I have two field firstname and lastname. Actual requirment is if category is doctor then display name as Dr. name else name but when I use following sql stament
Whenever one of the name is null it show name as null that is whenever firstname or lastname is null I get name as null

select @DOCNAME = CASE
WHEN DR_CUST_CATEGORY = 1 THEN 'Dr.' + ' ' + dr_cust_name + dr_cust_lastname
ELSE dr_cust_name + dr_cust_lastname
END FROM DOCTOR_MASTER WHERE DR_CUST_DOCTORCODE =@DOCCODE

Please suggest some ideas or any other idea please suggest
Thank you.

try this use ISNULL Function

select @DOCNAME = CASE
WHEN DR_CUST_CATEGORY = 1 THEN 'Dr.' + ' ' +isnull(dr_cust_name,'') + isnull(dr_cust_lastname,'')
ELSE isnull(dr_cust_name,'') +isnull(dr_cust_lastname,'')
END FROM DOCTOR_MASTER WHERE DR_CUST_DOCTORCODE =@DOCCODE[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources