SQL Server Question...

[sbv]

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:

[serkan sendur]

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

[SheSaidImaPregy]

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

[SheSaidImaPregy]

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

[serkan sendur]

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

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

[SheSaidImaPregy]

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 :)

[serkan sendur]

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

[sbv]

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 :)