如何检查 SQL Server 中的 Is not Null 和 Is not Empty 字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8660203/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to check for Is not Null And Is not Empty string in SQL server?
提问by user993935
How can we check in a SQL Server WHEREcondition whether the column is not null and not the empty string ('')?
我们如何在 SQL ServerWHERE条件中检查该列是否为空且是否为空字符串 ( '')？
回答by Martin Smith
If you only want to match "" as an empty string
如果只想匹配 "" 作为空字符串
WHERE DATALENGTH(COLUMN) > 0
If you want to count any string consisting entirely of spaces as empty
如果要将任何完全由空格组成的字符串计为空
WHERE COLUMN <> ''
Both of these will not return NULLvalues when used in a WHEREclause. As NULLwill evaluate as UNKNOWNfor these rather than TRUE.
这两个NULL在WHERE子句中使用时都不会返回值。正如NULL将评估UNKNOWN这些而不是TRUE。
CREATE TABLE T
(
C VARCHAR(10)
);
INSERT INTO T
VALUES ('A'),
(''),
(' '),
(NULL);
SELECT *
FROM T
WHERE C <> ''
Returns just the single row A. I.e. The rows with NULLor an empty string or a string consisting entirely of spaces are all excluded by this query.
仅返回单行A。即带有NULL或 空字符串或完全由空格组成的字符串的行都被此查询排除。
回答by onedaywhen
WHERE NULLIF(your_column, '') IS NOT NULL
Nowadays (4.5 years on), to make it easier for a human to read, I would just use
如今（4.5 年之后），为了让人类更容易阅读，我只会使用
WHERE your_column <> ''
While there is a temptation to make the null check explicit...
虽然有一种诱惑使空检查显式......
WHERE your_column <> ''
AND your_column IS NOT NULL
...as @Martin Smith demonstrates in the accepted answer, it doesn't really add anything (and I personally shun SQL nulls entirely nowadays, so it wouldn't apply to me anyway!).
...正如@Martin Smith 在接受的答案中所展示的那样，它并没有真正添加任何内容（而且我个人现在完全避开 SQL 空值，因此无论如何它都不适用于我！）。
回答by Anoop Verma
Coalesce will fold nulls into a default:
Coalesce 会将空值折叠为默认值：
COALESCE (fieldName, '') <> ''
回答by Saalim Bhoraniya
in basic way
以基本方式
SELECT *
FROM [TableName]
WHERE column_name!='' AND column_name IS NOT NULL
回答by Luc
An index friendly way of doing this is:
这样做的索引友好方式是：
where (field is not null and field <> '')
If there aren't many rows or this field isn't indexed, you can use:
如果行数不多或此字段未编入索引，则可以使用：
where isnull(field,'') <> ''
回答by DxTx
You can use either one of these to check null, whitespace and empty strings.
您可以使用其中任何一种来检查 null、空格和空字符串。
WHERE COLUMN <> ''
WHERE LEN(COLUMN) > 0
WHERE NULLIF(LTRIM(RTRIM(COLUMN)), '') IS NOT NULL
回答by Ellis
Just check: where value > '' -- not null and not empty
只需检查： where value > '' -- 不为空且不为空
-- COLUMN CONTAINS A VALUE (ie string not null and not empty) :
-- (note: "<>" gives a different result than ">")
select iif(null > '', 'true', 'false'); -- false (null)
select iif('' > '', 'true', 'false'); -- false (empty string)
select iif(' ' > '', 'true', 'false'); -- false (space)
select iif(' ' > '', 'true', 'false'); -- false (tab)
select iif('
' > '', 'true', 'false'); -- false (newline)
select iif('xxx' > '', 'true', 'false'); -- true
--
--
-- NOTE - test that tab and newline is processed as expected:
select 'x x' -- tab
select 'x
x' -- newline
