Asp Forum - How to select one of two columns? - microsoft.public.sqlserver.programming

Doug

3/21/2007 5:21:00 PM

Question for the SQL gurus: I have a table that has two text columns, named
ShortDesc and FullDesc. The users didn't start using the FullDesc field
until a few years ago, it is blank in most of the records that are more than
3 years old. I want to write a query that returns the FullDesc column when
it is not blank, otherwise returns the ShortDesc column.

Kind of like an iff function in Excel -- iff(FullDesc = "", ShortDesc,
FullDesc).

Is this possible in SQL?

6 Answers

Mubashir Khan

3/21/2007 5:35:00 PM

use case expressions ...

"Doug" <dougATloweNOSPAMwriterDOTyou-know-what> wrote in message
news:4601698c$0$17203$39cecf19@news.twtelecom.net...
> Question for the SQL gurus: I have a table that has two text columns,
> named ShortDesc and FullDesc. The users didn't start using the FullDesc
> field until a few years ago, it is blank in most of the records that are
> more than 3 years old. I want to write a query that returns the FullDesc
> column when it is not blank, otherwise returns the ShortDesc column.
>
> Kind of like an iff function in Excel -- iff(FullDesc = "", ShortDesc,
> FullDesc).
>
> Is this possible in SQL?
>

Tibor Karaszi

3/21/2007 5:39:00 PM

Use CASE:

SELECT
col1
,CASE WHEN col2 = '' THEN col3 ELSE col2 END AS colname
,...
FROM...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://sqlblog.com/blogs/tib...

"Doug" <dougATloweNOSPAMwriterDOTyou-know-what> wrote in message
news:4601698c$0$17203$39cecf19@news.twtelecom.net...
> Question for the SQL gurus: I have a table that has two text columns, named ShortDesc and
> FullDesc. The users didn't start using the FullDesc field until a few years ago, it is blank in
> most of the records that are more than 3 years old. I want to write a query that returns the
> FullDesc column when it is not blank, otherwise returns the ShortDesc column.
>
> Kind of like an iff function in Excel -- iff(FullDesc = "", ShortDesc, FullDesc).
>
> Is this possible in SQL?
>

Plamen Ratchev

3/21/2007 5:40:00 PM

Take a look at CASE. It will be something like this:

CASE WHEN FullDesc = '' THEN ShortDesc ELSE FullDesc END

HTH,

Plamen Ratchev
http://www.SQL...

Aaron [SQL Server MVP]

3/21/2007 5:41:00 PM

SELECT [Desc] = CASE WHEN FullDesc > '' THEN FullDesc ELSE ShortDesc END
FROM table;

--
Aaron Bertrand
SQL Server MVP
http://www.sq...
http://www.aspfa...

"Doug" <dougATloweNOSPAMwriterDOTyou-know-what> wrote in message
news:4601698c$0$17203$39cecf19@news.twtelecom.net...
> Question for the SQL gurus: I have a table that has two text columns,
> named ShortDesc and FullDesc. The users didn't start using the FullDesc
> field until a few years ago, it is blank in most of the records that are
> more than 3 years old. I want to write a query that returns the FullDesc
> column when it is not blank, otherwise returns the ShortDesc column.
>
> Kind of like an iff function in Excel -- iff(FullDesc = "", ShortDesc,
> FullDesc).
>
> Is this possible in SQL?
>

AlterEgo

3/21/2007 5:52:00 PM

Doug,

If you mean that the columns are a text datatype then you will need
something like this:

select
case
when FullDesc is null or datalength(FullDesc) = 0 then ShortDesc
else FullDesc
end MyDesc
from ...

-- Bill

"Doug" <dougATloweNOSPAMwriterDOTyou-know-what> wrote in message
news:4601698c$0$17203$39cecf19@news.twtelecom.net...
> Question for the SQL gurus: I have a table that has two text columns,
> named ShortDesc and FullDesc. The users didn't start using the FullDesc
> field until a few years ago, it is blank in most of the records that are
> more than 3 years old. I want to write a query that returns the FullDesc
> column when it is not blank, otherwise returns the ShortDesc column.
>
> Kind of like an iff function in Excel -- iff(FullDesc = "", ShortDesc,
> FullDesc).
>
> Is this possible in SQL?
>

bob

3/22/2007 12:10:00 PM

If the column is NULL, not '', you can use COALESCE, which returns the first
non-null column:

SELECT COALESCE( FullDesc, ShortDesc )

Simpler than CASE right!?

wBob

"Doug" wrote:

> Question for the SQL gurus: I have a table that has two text columns, named
> ShortDesc and FullDesc. The users didn't start using the FullDesc field
> until a few years ago, it is blank in most of the records that are more than
> 3 years old. I want to write a query that returns the FullDesc column when
> it is not blank, otherwise returns the ShortDesc column.
>
> Kind of like an iff function in Excel -- iff(FullDesc = "", ShortDesc,
> FullDesc).
>
> Is this possible in SQL?
>
>
>

microsoft.public.sqlserver.programming

How to select one of two columns?