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

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

See if this resolves:

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

Reference:

link

link

With COALESCE you get a more elegant solution:

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

For 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 it will be converted to '' , so just compare with '' . The conversion to nvarchar resolves the type mismatch error with the operator you were getting.

To test the proposed solutions, the following table has been 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, it starts with spaces but there is content in the column. Case 7 has 10,000 spaces before the text 'test 7'. The code is correct if you only return lines 1, 2, 5, and 6, as lines 3, 4, and 7 contain text.

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

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

The code hint is:

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

A different version of a form already presented here.

In this case, you convert only Notes and then compare.

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