• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2367
  • Last Modified:

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
0
Giambattista
Asked:
Giambattista
  • 5
  • 2
1 Solution
 
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
 
JestersGrindCommented:
Try this:

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

Greg

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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:
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:
or using isnull

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now