asked on

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

ralmada

you can try the following

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

ralmada

or

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

JestersGrind

Try this:

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

Greg

ralmada

or if you want to filter those

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

JestersGrind

Correction:

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

Greg

Giambattista

ASKER

@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

ASKER CERTIFIED SOLUTION

ralmada

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ralmada

or using isnull

select id
, nomehotel
, case when isnull(descrizione, '') = '' then 0 else 1 end as haDescrizione from hotel