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

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?

Answer 1

try this:

select * from mytable where convert(varchar, mycolumn) = ''

i hope help u!

Answer 2

Use DATALENGTH method, for example:

SELECT length = DATALENGTH(myField)
FROM myTABLE

Answer 3

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 <> ''

And remember use LIKE phrases only when necessary because they will degrade performance compared to other types of searches.

Answer 4

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.

Answer 5

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)> 0)
                            THEN cast((S.UnitsOrdered) as varchar(50))
                    ELSE 'No Labs Available'
                    END

Answer 6

SELECT * FROM TABLE
WHERE ISNULL(FIELD, '')=''