Giambattista
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
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
or
select id, HotelName, case datalength(Description) = 0 or datalength(Description) is null then 'Yes' else 'No' end as it_has_description from yourtable
select id, HotelName, case datalength(Description) = 0 or datalength(Description) is null then 'Yes' else 'No' end as it_has_description from yourtable
Try this:
SELECT ID, HOTELNAME, DESCRIPTION
from HOTEL
WHERE DESCRIPTION <> ISNULL(DESCRIPTION, '')
Greg
SELECT ID, HOTELNAME, DESCRIPTION
from HOTEL
WHERE DESCRIPTION <> ISNULL(DESCRIPTION, '')
Greg
or if you want to filter those
select * from yourtable
where datalength(description) = 0 or datalength(description) is null
select * from yourtable
where datalength(description) = 0 or datalength(description) is null
Correction:
SELECT ID, HOTELNAME, DESCRIPTION
from HOTEL
WHERE ISNULL(DESCRIPTION, '') <> ''
Greg
SELECT ID, HOTELNAME, DESCRIPTION
from HOTEL
WHERE ISNULL(DESCRIPTION, '') <> ''
Greg
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.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
or using isnull
select id
, nomehotel
, case when isnull(descrizione, '') = '' then 0 else 1 end as haDescrizione from hotel
select id
, nomehotel
, case when isnull(descrizione, '') = '' then 0 else 1 end as haDescrizione from hotel
select id, HotelName, case datalength(Description) = 0 then 'Yes' else 'No' end as it_has_description from yourtable