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:

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

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

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

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

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.