Need a help in select - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Need a help in select

Author

Topic

divyaram
Posting Yak Master

180 Posts

Posted - 2010-03-16 : 01:26:09

Hi all,

I have select the name from Fis_User table.But one condition is there for selecting the names
from the tables.i have select the firstname and lastname along with first letter of the midname followed by a '.'
if midname is null a space has to be left . i have wriiten the code like this, problem its select only the last value
whole table data for names are not displaying, how can i correct to get the output

The code which i have done is
declare @result1 varchar(max)
select @result1=FIS_User.First_Name + isnull(substring(isnull(FIS_User.Mid_Name, ''),1,1),'' )+ FIS_User.Last_Name
from FIS_User
select @result1

Regards,
Divya

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-03-16 : 01:57:18

check this,
select FIS_User.First_Name + substring(isnull(FIS_User.Mid_Name, ' '),1,1)+ FIS_User.Last_Name

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 04:06:18

it should be

select FIS_User.First_Name + coalesce(left(FIS_User.Mid_Name,1)+'.', ' ')+ FIS_User.Last_Name FROM Table

as per OP's reqmnt. Also if any other column is also nullable you need to use COALESCE for them too to handle NULL values in them also

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

divyaram
Posting Yak Master

180 Posts

Posted - 2010-03-16 : 04:43:45

Thank u!!!!

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 05:10:30

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources