How to check if a field of type text or ntext is null or empty?

Question

How can I check whether a field of the type ntext is null or empty in where?

I got the following select

SELECT  Subject, Notes from DocumentNotes
where Notes is null or Notes = ' '

but when comparing it returns the following error:

Message 402, Level 16, Status 1, Line 3 Data types ntext and varchar are incompatible in Equal to.

Example:

declare @textos table
(
  Subject  int,
  Notes ntext
)

insert into @textos values
(1, ' '),
(2, ''),
(3, 'teste')

I only want Subject <> 3

Note: Using Sql Server 2008.

Answer 1

You can use datalength(Notes)=0 or Notes like ''.

SELECT  Subject, Notes from DocumentNotes
where Notes is null or Notes like ' '

Ou 

SELECT  Subject, Notes from DocumentNotes
where Notes is null or datalength(Notes)=0

Answer 2

See if this solves:

SELECT  Subject, Notes from DocumentNotes
where Notes is null or datalength(Notes)=0

Reference:

https://stackoverflow.com/questions/33409/how-do-i-check-if-a-sql-server-text-column-is-empty

https://msdn.microsoft.com/en-us/library/ms173486.aspx?f=255&MSPPError=-2147217396

Answer 3

With COALESCE you get a more elegant solution:

SELECT  Subject, Notes 
FROM DocumentNotes
WHERE CONVERT(nvarchar, COALESCE(Notes, '')) = ''

In the case of SQL Server, the same result can be obtained with ISNULL:

SELECT  Subject, Notes
FROM DocumentNotes
WHERE CONVERT(nvarchar, ISNULL(Notes, '')) = ''

That is, if the value is NULL he will be converted into '', then just compare with ''. The conversion to nvarchar solves the type incompatibility error with the operator you were getting.

Answer 4

To test the proposed solutions, the following table was created:

  use tempDB;
  CREATE TABLE DocumentNotes (Subject varchar(50), Notes ntext);

  INSERT into DocumentNotes 
    values (1, space(1)), (2, N''), (3, N'teste 3'), 
           (4, N'   teste 4'), (5, NULL), (6, space(10)),
           (7, replicate(cast(N' ' as nvarchar(max)), 10000) + N'teste 7');
  go

In case 4 starts with spaces but there is content in the column. Case 7 has 10 thousand spaces before text 'test 7'. The code will be correct if it returns only lines 1, 2, 5 and 6, because lines 3, 4 and 7 contain text.

When creating the code, it is necessary to be aware of the peculiarities of columns of type ntext, whose storage capacity is up to (2 30 - 1).

The Ltrim() function can be used to delete spaces on the left. If there are only spaces, the result is space(0) or ''. But the Ltrim() function does not accept ntext columns as parameter, so just convert previously to nvarchar(max).

The code suggestion is:

  SELECT  Subject, Notes 
    from DocumentNotes
    where Ltrim(Cast(IsNull(Notes, space(0)) as nvarchar(max))) = space(0);

Answer 5

A different version of a form already presented here.

In this case, only Notes is converted and then compared.

select * from tmp_textos
where isnull(convert(varchar,Notes),'')=''