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
44
ISNULL(
case textcolum1
WHEN '' THEN NULL
ELSE textcolum1
END
,textcolum2) textcolum1
#3
21
Actually, you just have to use the LIKE operator.
实际上,你只需要使用LIKE运算符。
SELECT * FROM mytable WHERE mytextfield LIKE ''
#4
6
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
2
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
2
You could do like
你可以像
SELECT * FROM TABLE WHERE FIELDNAME=''
#8
1
Use the IS NULL operator:
使用IS NULL运算符:
Select * from tb_Employee where ename is null
#9
0
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
0
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
0
You have to do both:
你必须同时做这两件事:
SELECT * FROM Table WHERE Text IS NULL or Text LIKE ''
从文本为NULL或类似文本的表中选择*
#12
0
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
0
Use DATALENGTH method, for example:
使用DATALENGTH方法,例如:
SELECT length = DATALENGTH(myField)
FROM myTABLE
#14
0
SELECT * FROM TABLE
WHERE ISNULL(FIELD, '')=''
#1
248
where datalength(mytextfield)=0
#2
44
ISNULL(
case textcolum1
WHEN '' THEN NULL
ELSE textcolum1
END
,textcolum2) textcolum1
#3
21
Actually, you just have to use the LIKE operator.
实际上,你只需要使用LIKE运算符。
SELECT * FROM mytable WHERE mytextfield LIKE ''
#4
6
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
2
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
2
You could do like
你可以像
SELECT * FROM TABLE WHERE FIELDNAME=''
#7
#8
1
Use the IS NULL operator:
使用IS NULL运算符:
Select * from tb_Employee where ename is null
#9
0
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
0
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
0
You have to do both:
你必须同时做这两件事:
SELECT * FROM Table WHERE Text IS NULL or Text LIKE ''
从文本为NULL或类似文本的表中选择*
#12
0
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
0
Use DATALENGTH method, for example:
使用DATALENGTH方法,例如:
SELECT length = DATALENGTH(myField)
FROM myTABLE
#14
0
SELECT * FROM TABLE
WHERE ISNULL(FIELD, '')=''