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?
117 Answers
where datalength(mytextfield)=0 ISNULL( case textcolum1 WHEN '' THEN NULL ELSE textcolum1 END ,textcolum2) textcolum1 Actually, you just have to use the LIKE operator.
SELECT * FROM mytable WHERE mytextfield LIKE '' 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.
SELECT * FROM TABLE WHERE ISNULL(FIELD, '')='' Use the IS NULL operator:
Select * from tb_Employee where ename is null 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) Use DATALENGTH method, for example:
SELECT length = DATALENGTH(myField) FROM myTABLE 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 I would test against SUBSTRING(textColumn, 0, 1)
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.
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 You have to do both:
SELECT * FROM Table WHERE Text IS NULL or Text LIKE ''
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 & @Tim Cooper with @Enrique Garcia & @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], '')))) try this:
select * from mytable where convert(varchar, mycolumn) = '' i hope help u!
DECLARE @temp as nvarchar(20) SET @temp = NULL --SET @temp = '' --SET @temp = 'Test' SELECT IIF(ISNULL(@temp,'')='','[Empty]',@temp) It will do two things:
- Null check and string null check
- Replace empty value to default value eg NA.
SELECT coalesce(NULLIF(column_name,''),'NA') as 'desired_name') from table; 
