Select statement with condition.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Select statement with condition.

Select statement with condition.

Rate Topic

Display Mode

Topic Options

Author

Message

Minnu

Posted Wednesday, January 23, 2013 2:55 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:34 AM
Points: 138, Visits: 411

Hi Team,

I have below query:

SELECT lname +'.' +fname AS emp_name FROM employees

Result : First_Name.Last_Name

If any one (lname or fname is NULL, then result is showing as NULL)

My requirement is :

If lname is NULL then result should be fname (without dot(.))
If fname is NULL then result should be lname (should not end with dot(.))

Please help

Post #1410431

John Mitchell-245523

Posted Wednesday, January 23, 2013 3:15 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 7:03 AM
Points: 4,443, Visits: 7,249

Use a CASE expression.

John

Post #1410435

subbareddy542

Posted Wednesday, January 23, 2013 3:16 AM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 13, 2013 12:35 AM
Points: 835, Visits: 616

pls use below code...

declare @t1 table(fname varchar(50),lname varchar(20))
insert into @t1(fname,lname) values('sql','server')
insert into @t1(fname) values('ravi')
insert into @t1(lname) values('kumar')
select *,case when (fname is not null) and (lname is not null) then fname+'.'+lname
else isnull(fname,lname)
end from @t1

Post #1410436

Minnu

Posted Wednesday, January 23, 2013 3:21 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:34 AM
Points: 138, Visits: 411

Thank u Subba Reddy.

May i know Where r u from.

Post #1410437

Minnu

Posted Wednesday, January 23, 2013 3:33 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:34 AM
Points: 138, Visits: 411

Hi,

I want result column name should be "Employee_Name"

select *,case when (fname is not null) and (lname is not null) then fname+'.'+lname
AS employee_Name
else isnull(fname,lname)

It is not working,

Can u please help...

Post #1410442

anthony.green

Posted Wednesday, January 23, 2013 3:35 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 2:40 AM
Points: 5,075, Visits: 4,833

select *,case when (fname is not null) and (lname is not null) then fname+'.'+lname
else isnull(fname,lname)
end AS employee_Name

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1410445

Sean Pearce

Posted Wednesday, January 23, 2013 7:12 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 8:18 AM
Points: 386, Visits: 1,424

SELECT COALESCE(lname + '.' + fname, lname, fname) AS emp_name
FROM employees

The SQL Guy @ blogspot

About Me

Post #1410558

« Prev Topic | Next Topic »

Permissions