如何检查SQL Server文本列是否为空?

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:

我使用的是SQL Server 2005。我有一个带文本列的表，表中有很多行，列的值不是null，但它是空的。试图与“产生这种反应:

The data types text and varchar are incompatible in the not equal to operator.

数据类型文本和varchar在不等于运算符时是不兼容的。

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

是否有一个特殊的函数来确定文本列的值是否为空?

14 个解决方案

#1

248

where datalength(mytextfield)=0

#2

ISNULL(
case textcolum1
    WHEN '' THEN NULL
    ELSE textcolum1
END 
,textcolum2) textcolum1

#3

Actually, you just have to use the LIKE operator.

实际上，你只需要使用LIKE运算符。

SELECT * FROM mytable WHERE mytextfield LIKE ''

#4

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.

记住，只有在必要时才使用短语，因为与其他类型的搜索相比，它们会降低性能。

#5

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)

#6

You could do like

你可以像

SELECT * FROM TABLE WHERE FIELDNAME=''

#7

I would test against SUBSTRING(textColumn, 0, 1)

我将对子字符串(textColumn, 0,1)进行测试

#8

Use the IS NULL operator:

使用IS NULL运算符:

Select * from tb_Employee where ename is null

#9

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.

null和空字符串等价吗?如果它们是，我将在我的应用程序中包含逻辑(或者如果应用程序是“开箱即用”的)，以强制字段为空或“，而不是另一个”。如果你使用"，那么你也可以将列设置为NOT NULL。只是一个data-cleanliness的事情。

#10

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

#11

You have to do both:

你必须同时做这两件事:

SELECT * FROM Table WHERE Text IS NULL or Text LIKE ''

从文本为NULL或类似文本的表中选择*

#12

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.

我知道有很多可以替代这个问题的答案，但是我只是想把我发现的最好的解决方案整理一下，@Eric Z Beard和@Tim Cooper， @Enrique Garcia和@Uli Kohler。

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.

如果需要处理这样一个事实:在您的用例场景中，空间只能是空的，因为下面的查询将返回1，而不是0。

SELECT datalength(' ')

Therefore, I would go for something like:

因此，我倾向于:

SELECT datalength(RTRIM(LTRIM(ISNULL([TextColumn], ''))))

#13

Use DATALENGTH method, for example:

使用DATALENGTH方法，例如:

SELECT length = DATALENGTH(myField)
FROM myTABLE

#14

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

#1

248

where datalength(mytextfield)=0

#2

ISNULL(
case textcolum1
    WHEN '' THEN NULL
    ELSE textcolum1
END 
,textcolum2) textcolum1

#3