SQL Server Question...
 

Hi,
I have one question related to SQL Server. But there is not forum on this. so here is my Question.

While accessing multiple columns value in a single alias column we right query as follows.

Select FNAME +' '+ MNAME +' '+ LNAME as NAME from Cust_Master

Am I right ?

But the question is when one of values from FNAME, MNAME, LNAME if comes NULL then my NAME filed will be NULL.

How to avoid this?? Any one have solution for this.

Note : I am using this Query for Creating VIEW.

Thanks. Have a Lovely Time. :pretty:

Re: SQL Server Question...
 

Select CASE WHEN FNAME IS NULL THEN '' ELSE FNAME END +' '+ CASE WHEN MNAME IS NULL THEN '' ELSE MNAME END +' '+ CASE WHEN LNAME IS NULL THEN '' ELSE LNAME END as NAME from Cust_Master

Re: SQL Server Question...
 

Scratch that! Use a predefined function called COALESCE. It's a life saver. Format:
COALESCE(columnname, 'valueifnull')

Select COALESCE(FNAME, '') +' '+ COALESCE(MNAME, '') +' '+ COALESCE(LNAME, '') as NAME from Cust_Master

Re: SQL Server Question...
 

This way if middle nam eis null or something, it will show:

FNAME LNAME

or MNAME LNAME

or FNAME LNAME, etc.


Oh and there is a forum for MS SQL:

http://www.daniweb.com/forums/forum127.html

Re: SQL Server Question...
 

Your t-sql is better than mine shesaid.. Is that what you want to hear from me :D

Re: SQL Server Question...
 

no no no, just spreading the knowledge lol.

You taught me though, cause I never thought it was possible to add case statements with inline sql like that.

So your t-sql is better use to me :)

Re: SQL Server Question...
 

By the way if you replace the "COALESCE" with "ISNULL", it gives exactly the same result

Re: SQL Server Question...
 

Thanks a lot to both of you. Solutions you provided solved my problem.
You guise are brilliant. When I can be as you. :(

Have a nice time. Thanks :)