Test if a ntext field id empty in a select query

I have this table of Hotels:

ID, HOTELNAME, DESCRIPTION

The field description is NTEXT and contains many text related to the Hotel.

I want to show a list of all the hotel with a link to a description page, if it is not empty.

The stupidest way to do it I imagine is:

SELECT ID, HOTELNAME, DESCRIPTION from HOTEL

<OUTPUT RECORDS>
*HOTELNAME*
   IF DESCRIPTION <> "" ==> link to description page
</OUTPUT RECORDS>

But is a very brutal query ... I have to select all the text inserted in a NTEXT field slowing a lot the query.

Is there a way to test if DESCRIPTION is empty or NULL without to select the whole text contained in it in the query?

Something like this:

select ID, HOTELNAME, if description not null or not "" ==> it_has_description = yes
GiambattistaAsked:
Who is Participating?
 
ralmadaCommented:
oops, missed a when there:

select id
     , nomehotel
     , case when datalength(descrizione) = 0 or datalength(descrizione) is null then 0 else 1 end as haDescrizione from hotel
0
 
ralmadaCommented:
you can try the following

select id, HotelName, case datalength(Description) = 0 then 'Yes' else 'No' end as it_has_description from yourtable
0
 
ralmadaCommented:
or

select id, HotelName, case datalength(Description) = 0 or datalength(Description) is null then 'Yes' else 'No' end as it_has_description from yourtable

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
JestersGrindCommented:
Try this:

SELECT ID, HOTELNAME, DESCRIPTION
from HOTEL
WHERE DESCRIPTION <> ISNULL(DESCRIPTION, '')

Greg

0
 
ralmadaCommented:
or if you want to filter those

select * from yourtable
where datalength(description) = 0 or datalength(description) is null
0
 
JestersGrindCommented:
Correction:

SELECT ID, HOTELNAME, DESCRIPTION
from HOTEL
WHERE ISNULL(DESCRIPTION, '') <> ''

Greg

0
 
GiambattistaAuthor Commented:
@raimada

I'm trying to use your solution, but I obtain a syntax error:

Incorrect syntax near '='.

@JesterGrind

I don't want to filter results. I want to select all hotels.
select id
     , nomehotel
     , case datalength(descrizione) = 0 or datalength(descrizione) is null then 0 else 1 end as haDescrizione from hotel

Open in new window

0
 
ralmadaCommented:
or using isnull

select id
     , nomehotel
     , case when isnull(descrizione, '') = '' then 0 else 1 end as haDescrizione from hotel
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.