<p>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:</p>

<blockquote>
  <p>The data types text and varchar are incompatible in the not equal to operator.</p>
</blockquote>

<p>Is there a special function to determine whether the value of a text column is not null but empty?</p>

[{'answer_id': 33415, 'author': 'bdukes', 'author_id': 2688, 'author_profile': 'https://Stackoverflow.com/users/2688', 'pm_score': 0, 'selected': False, 'text': '<p>I would test against <a href="http://msdn.microsoft.com/en-us/library/ms187748.aspx" rel="nofollow noreferrer">SUBSTRING</a>(textColumn, 0, 1)</p>\n'}, {'answer_id': 33425, 'author': 'Eric Z Beard', 'author_id': 1219, 'author_profile': 'https://Stackoverflow.com/users/1219', 'pm_score': 9, 'selected': True, 'text': '<pre><code>where datalength(mytextfield)=0\n</code></pre>\n'}, {'answer_id': 34036, 'author': 'Dane', 'author_id': 2929, 'author_profile': 'https://Stackoverflow.com/users/2929', 'pm_score': 5, 'selected': False, 'text': "<p>Actually, you just have to use the LIKE operator.</p>\n\n<pre><code>SELECT * FROM mytable WHERE mytextfield LIKE ''\n</code></pre>\n"}, {'answer_id': 35183, 'author': 'Tyler Gooch', 'author_id': 1372, 'author_profile': 'https://Stackoverflow.com/users/1372', 'pm_score': 0, 'selected': False, 'text': '<p>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.</p>\n'}, {'answer_id': 3104438, 'author': 'Eric', 'author_id': 374536, 'author_profile': 'https://Stackoverflow.com/users/374536', 'pm_score': 6, 'selected': False, 'text': "<pre><code>ISNULL(\ncase textcolum1\n    WHEN '' THEN NULL\n    ELSE textcolum1\nEND \n,textcolum2) textcolum1\n</code></pre>\n"}, {'answer_id': 9608085, 'author': 'Yoosaf Abdulla', 'author_id': 676508, 'author_profile': 'https://Stackoverflow.com/users/676508', 'pm_score': 0, 'selected': False, 'text': '<p>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:</p>\n\n<pre><code>StrengthInfo = CASE WHEN ((SELECT COUNT(UnitsOrdered) FROM [Data_Sub_orders].[dbo].[Snappy_Orders_Sub] WHERE IdPatient = @PatientId and IdDrugService = 226)&gt; 0)\n                            THEN cast((S.UnitsOrdered) as varchar(50))\n                    ELSE \'No Labs Available\'\n                    END\n</code></pre>\n'}, {'answer_id': 11017855, 'author': 'Mike Roberts', 'author_id': 1454021, 'author_profile': 'https://Stackoverflow.com/users/1454021', 'pm_score': 1, 'selected': False, 'text': "<p>I know this post is ancient but, I found it useful. </p>\n\n<p>It didn't resolve my issue of returning the record with a non empty text field so I thought I would add my solution.</p>\n\n<p>This is the where clause that worked for me.</p>\n\n<pre><code>WHERE xyz LIKE CAST('% %' as text)\n</code></pre>\n"}, {'answer_id': 12326533, 'author': 'marklark', 'author_id': 462234, 'author_profile': 'https://Stackoverflow.com/users/462234', 'pm_score': 0, 'selected': False, 'text': "<p>You have to do both:</p>\n\n<p><code>SELECT * FROM Table WHERE Text IS NULL or Text LIKE ''</code></p>\n"}, {'answer_id': 16915901, 'author': 'Pearl', 'author_id': 1920827, 'author_profile': 'https://Stackoverflow.com/users/1920827', 'pm_score': 2, 'selected': False, 'text': '<p>Use the IS NULL operator:</p>\n\n<pre><code>Select * from tb_Employee where ename is null\n</code></pre>\n'}, {'answer_id': 21686029, 'author': 'Enrique Garcia', 'author_id': 1231186, 'author_profile': 'https://Stackoverflow.com/users/1231186', 'pm_score': 2, 'selected': False, 'text': "<pre><code>SELECT * FROM TABLE\nWHERE ISNULL(FIELD, '')=''\n</code></pre>\n"}, {'answer_id': 25454764, 'author': 'Nima', 'author_id': 3715742, 'author_profile': 'https://Stackoverflow.com/users/3715742', 'pm_score': 4, 'selected': False, 'text': "<p>To get only empty values (and not null values):</p>\n\n<pre><code>SELECT * FROM myTable WHERE myColumn = ''\n</code></pre>\n\n<p>To get both null and empty values:</p>\n\n<pre><code>SELECT * FROM myTable WHERE myColumn IS NULL OR myColumn = ''\n</code></pre>\n\n<p>To get only null values:</p>\n\n<pre><code>SELECT * FROM myTable WHERE myColumn IS NULL\n</code></pre>\n\n<p>To get values other than null and empty:</p>\n\n<pre><code>SELECT * FROM myTable WHERE myColumn &lt;&gt; ''\n</code></pre>\n\n<p><br>\nAnd remember use LIKE phrases only when necessary because they will degrade performance compared to other types of searches.</p>\n"}, {'answer_id': 31875178, 'author': 'Leo', 'author_id': 4189349, 'author_profile': 'https://Stackoverflow.com/users/4189349', 'pm_score': 0, 'selected': False, 'text': "<p>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.</p>\n\n<p>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.</p>\n\n<pre><code>SELECT datalength(' ')\n</code></pre>\n\n<p>Therefore, I would go for something like:</p>\n\n<pre><code>SELECT datalength(RTRIM(LTRIM(ISNULL([TextColumn], ''))))\n</code></pre>\n"}, {'answer_id': 32258632, 'author': 'Jorgesys', 'author_id': 250260, 'author_profile': 'https://Stackoverflow.com/users/250260', 'pm_score': 1, 'selected': False, 'text': '<p>Use <a href="https://msdn.microsoft.com/en-us/library/ms173486.aspx" rel="nofollow"><strong>DATALENGTH</strong></a> method, for example:</p>\n\n<pre><code>SELECT length = DATALENGTH(myField)\nFROM myTABLE\n</code></pre>\n'}, {'answer_id': 52740656, 'author': 'Luiz Fernando Corrêa Leite', 'author_id': 4267702, 'author_profile': 'https://Stackoverflow.com/users/4267702', 'pm_score': 0, 'selected': False, 'text': "<p>try this:</p>\n\n<pre><code>select * from mytable where convert(varchar, mycolumn) = ''\n</code></pre>\n\n<p>i hope help u!</p>\n"}, {'answer_id': 56599223, 'author': 'Enrique Garcia', 'author_id': 1231186, 'author_profile': 'https://Stackoverflow.com/users/1231186', 'pm_score': 1, 'selected': False, 'text': "<p>Instead of using <code>isnull</code> use a <code>case</code>, because of performance it is better the case.</p>\n\n<pre><code>case when campo is null then '' else campo end\n</code></pre>\n\n<p>In your issue you need to do this:</p>\n\n<pre><code>case when campo is null then '' else\n  case when len(campo) = 0 then '' else campo en\nend\n</code></pre>\n\n<p>Code like this:</p>\n\n<pre><code>create table #tabla(\nid int,\ncampo varchar(10)\n)\n\ninsert into #tabla\nvalues(1,null)\n\ninsert into #tabla\nvalues(2,'')\n\ninsert into #tabla\nvalues(3,null)\n\ninsert into #tabla\nvalues(4,'dato4')\n\ninsert into #tabla\nvalues(5,'dato5')\n\nselect id, case when campo is null then 'DATA NULL' else\n  case when len(campo) = 0 then 'DATA EMPTY' else campo end\nend\nfrom #tabla\n\ndrop table #tabla\n</code></pre>\n"}, {'answer_id': 57628088, 'author': 'user11968038', 'author_id': 11968038, 'author_profile': 'https://Stackoverflow.com/users/11968038', 'pm_score': 0, 'selected': False, 'text': "<pre><code>DECLARE @temp as nvarchar(20)\n\nSET @temp = NULL\n--SET @temp = ''\n--SET @temp = 'Test'\n\nSELECT IIF(ISNULL(@temp,'')='','[Empty]',@temp)\n</code></pre>\n"}, {'answer_id': 68573417, 'author': 'Pallav Chanana', 'author_id': 10538842, 'author_profile': 'https://Stackoverflow.com/users/10538842', 'pm_score': 0, 'selected': False, 'text': "<blockquote>\n<p><strong>It will do two things:</strong></p>\n<ol>\n<li>Null check and string null check</li>\n<li>Replace empty value to default value eg NA.</li>\n</ol>\n</blockquote>\n<pre><code>SELECT coalesce(NULLIF(column_name,''),'NA') as 'desired_name') from table;\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/33409', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1214/']