我需要在sqlserver2008中使用Query来获取数据，避免使用null值fileds [英] I need of Query in sqlserver2008 to get data avoiding null values fileds

问题描述

我有一个像雇员一样的表名如下结构

I have one table name like employee as follow structure

FirstName      MiddleName        LastName

Raj               null              null

null            Kumar              null

null            null              Reddy

我想要输出如下

I want output as follows

Result
------
Raj
Kumar
Reddy

你能否提出如何在查询中实现此输出的建议？



谢谢&问候

Syed Chand Basha

Can you guys please give suggestions how to achieve this output in Query?

Thanks & Regards
Syed Chand Basha

推荐答案

试试这个：

Try this:

select isnull(firstname, '') + isnull(middlename, '') + isnull(lastname, '') as result from table1

阅读：ISNULL（Transact-SQL） [ ^ ]

您可以使用COALESCE功能。

You can use COALESCE function.

Select COALESCE(FirstName, MiddleName, LastName) as Result

试试这个：

Try this:

create table #Test1(firstname nvarchar(max) ,middlename nvarchar(max),lastname nvarchar(max) )

insert into #Test1 values('Raj','','')
insert into #Test1 values('','Kumar','')
insert into #Test1 values('','','Reddy')

option 1:
with cte as(
select firstname From #Test union all select middlename From #Test  union all select lastname From #Test
)
select *From cte where firstname!=''

option 2:
select firstname From #Test where firstname!='' union all select middlename From #Test where middlename!=''  union all select lastname From #Test  where lastname!=''

这篇关于我需要在sqlserver2008中使用Query来获取数据，避免使用null值fileds的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！