How do I check if a SQL Server text column is empty

Question

I am using SQL Server 2005   I have a table with a text column and I have many rows in the table where the value of this column is not null  but it is empty   Trying to compare against    yields this response      The data types text and varchar are incompatible in the not equal to operator    Is there a special function to determine whether the value of a text column is not null but empty

User · Answer

I know this post is ancient but  I found it useful    It didn t resolve my issue of returning the record with a non empty text field so I thought I would add my solution   This is the where clause that worked for me   WHERE xyz LIKE CAST       as text

User · Answer

Instead of using isnull use a case  because of performance it is better the case   case when campo is null then    else campo end   In your issue you need to do this   case when campo is null then    else   case when len campo    0 then    else campo en end   Code like this   create table  tabla  id int  campo varchar 10     insert into  tabla values 1 null   insert into  tabla values 2      insert into  tabla values 3 null   insert into  tabla values 4  dato4    insert into  tabla values 5  dato5    select id  case when campo is null then  DATA NULL  else   case when len campo    0 then  DATA EMPTY  else campo end end from  tabla  drop table  tabla

User · Answer

where datalength mytextfield  0

User · Answer

Use DATALENGTH method  for example   SELECT length   DATALENGTH myField  FROM myTABLE

User · Answer

Actually  you just have to use the LIKE operator   SELECT   FROM mytable WHERE mytextfield LIKE

User · Answer

To get only empty values  and not null values    SELECT   FROM myTable WHERE myColumn        To get both null and empty values   SELECT   FROM myTable WHERE myColumn IS NULL OR myColumn        To get only null values   SELECT   FROM myTable WHERE myColumn IS NULL   To get values other than null and empty   SELECT   FROM myTable WHERE myColumn  lt  gt        And remember use LIKE phrases only when necessary because they will degrade performance compared to other types of searches

User · Answer

DECLARE  temp as nvarchar 20   SET  temp   NULL --SET  temp      --SET  temp    Test   SELECT IIF ISNULL  temp          Empty    temp

User · Answer

I know there are plenty answers with alternatives to this problem  but I just would like to put together what I found as the best solution by  Eric Z Beard  amp   Tim Cooper with  Enrique Garcia  amp   Uli K  hler   If needed to deal with the fact that space-only could be the same as empty in your use-case scenario  because the query below will return 1  not 0   SELECT datalength        Therefore  I would go for something like   SELECT datalength RTRIM LTRIM ISNULL  TextColumn

User · Answer

I wanted to have a predefined text  No Labs Available   to be displayed if the value was null or empty and my friend helped me with this   StrengthInfo   CASE WHEN   SELECT COUNT UnitsOrdered  FROM  Data Sub orders   dbo   Snappy Orders Sub  WHERE IdPatient    PatientId and IdDrugService   226  gt  0                              THEN cast  S UnitsOrdered  as varchar 50                       ELSE  No Labs Available                      END

User · Answer

try this   select   from mytable where convert varchar  mycolumn         i hope help u

User · Answer

You have to do both   SELECT   FROM Table WHERE Text IS NULL or Text LIKE

User · Answer

I would test against SUBSTRING textColumn  0  1

User · Answer

ISNULL  case textcolum1     WHEN    THEN NULL     ELSE textcolum1 END   textcolum2  textcolum1

User · Answer

SELECT   FROM TABLE WHERE ISNULL FIELD

User · Answer

Are null and an empty string equivalent   If they are  I would include logic in my application  or maybe a trigger if the app is  out-of-the-box     to force the field to be either null or     but not the other   If you went with     then you could set the column to NOT NULL as well   Just a data-cleanliness thing

User · Answer

Use the IS NULL operator   Select   from tb Employee where ename is null

[sql-server] How do I check if a SQL Server text column is empty?

Examples related to sql-server

Examples related to sql-server-2005

Examples related to sqldatatypes

Examples related to notnull

Examples related to emptydatatext