Avatar of Allan
Allan
Flag for United States of America asked on

Test for Empty Text

Hi Experts!

Thanks in advance for reading this. For SQL 2K.

There are two fields: description is char(60) and longdescription  as text.

What I want to do is select description only if longdescription is empty or no test in it.

Tried:

select
  description = CASE WHEN (longdescription IS NULL) THEN description
                     ELSE longdescription
                     END

But it just select blank.

Tried:

select
  description = CASE WHEN (longdescription = '') THEN description
                     ELSE longdescription
                     END
from svccode
where codeid = '00007'

But got this error msg:

Server: Msg 306, Level 16, State 1, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.


Can you please help?

T I A !
Microsoft SQL Server

Avatar of undefined
Last Comment
Allan

8/22/2022 - Mon
pootle_flump

select
  description = CASE WHEN COALESCE(longdescription, '') = '' THEN description
                     ELSE longdescription
                     END

HTH
Allan

ASKER
Thanks pootle_flump,

same eror msg:

Server: Msg 306, Level 16, State 1, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
ASKER CERTIFIED SOLUTION
Hypnochu

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Allan

ASKER
Great Hpnochu!

It was the like that I was missing. Thanks!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23