Link to home
Start Free TrialLog in
Avatar of Allan
AllanFlag 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 !
Avatar of pootle_flump
pootle_flump

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

HTH
Avatar of 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
Avatar of Hypnochu
Hypnochu

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Allan

ASKER

Great Hpnochu!

It was the like that I was missing. Thanks!