Ad:

ASP.NET Discussion Thread
Unsolved
Views: 881

Mar 5th, 2008

SQL Server Question...

Expand Post »

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.

ASP.NET Syntax (Toggle Plain Text)
Select FNAME +' '+ MNAME +' '+ LNAME as NAME from Cust_Master
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.

Similar Threads

Finding the nth row (SQL)...debative question in MS SQL
MySQL vs. SQL Server in PHP
SQL Server newbi... how to get started?? in MS SQL
Error in SQL Server 2005 in MS SQL
C# failing to drop stored procedure from SQL Server using SMO in C#

sbv

Reputation Points: 15

Solved Threads: 8

Junior Poster

Offline

178 posts
since Jan 2008

Permalink

Mar 5th, 2008

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

serkan sendur

Featured Poster

Reputation Points: 854

Solved Threads: 127

Banned

Offline

2,057 posts
since Jan 2008

Permalink

Mar 5th, 2008

Re: SQL Server Question...

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

ASP.NET Syntax (Toggle Plain Text)
Select COALESCE(FNAME, '') +' '+ COALESCE(MNAME, '') +' '+ COALESCE(LNAME, '') as NAME from Cust_Master
Select COALESCE(FNAME, '') +' '+ COALESCE(MNAME, '') +' '+ COALESCE(LNAME, '') as NAME from Cust_Master

SheSaidImaPregy

Reputation Points: 43

Solved Threads: 68

Veteran Poster

Offline

1,080 posts
since Sep 2007

Permalink

Mar 5th, 2008

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

Last edited by SheSaidImaPregy; Mar 5th, 2008 at 10:17 am.

SheSaidImaPregy

Reputation Points: 43

Solved Threads: 68

Veteran Poster

Offline

1,080 posts
since Sep 2007

Permalink

Mar 5th, 2008

Re: SQL Server Question...

Click to Expand / Collapse Quote originally posted by SheSaidImaPregy ...

Scratch that! Use a predefined function called COALESCE. It's a life saver. Format:
COALESCE(columnname, 'valueifnull')
ASP.NET Syntax (Toggle Plain Text)
Select COALESCE(FNAME, '') +' '+ COALESCE(MNAME, '') +' '+ COALESCE(LNAME, '') as NAME from Cust_Master
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

serkan sendur

Featured Poster

Reputation Points: 854

Solved Threads: 127

Banned

Offline

2,057 posts
since Jan 2008

Permalink

Mar 5th, 2008

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

SheSaidImaPregy

Reputation Points: 43

Solved Threads: 68

Veteran Poster

Offline

1,080 posts
since Sep 2007

Permalink

Mar 5th, 2008

Re: SQL Server Question...

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

serkan sendur

Featured Poster

Reputation Points: 854

Solved Threads: 127

Banned

Offline

2,057 posts
since Jan 2008

Permalink

Mar 6th, 2008

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

Last edited by sbv; Mar 6th, 2008 at 12:21 am.

sbv

Reputation Points: 15

Solved Threads: 8

Junior Poster

Offline

178 posts
since Jan 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in ASP.NET Forum Timeline: how to make output cached page expire(refresh) before its duration completes

Next Thread in ASP.NET Forum Timeline: Session in app_code

DaniWeb Message

Cancel Changes

User Name
Password