如何在 SQL 中使用 NULL 或空字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15663207/
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 use NULL or empty string in SQL
提问by poshan
I would like to know how to use NULL and an empty string at the same time in a WHEREclause in SQL Server. I need to find records that have either null values or an empty string. Thanks.
我想知道如何WHERE在 SQL Server的子句中同时使用 NULL 和空字符串。我需要查找具有空值或空字符串的记录。谢谢。
回答by codingbadger
Select *
From Table
Where (col is null or col = '')
Or
或者
Select *
From Table
Where IsNull(col, '') = ''
回答by Praveen Nambiar
You can simply do this:
你可以简单地这样做：
SELECT *
FROM yourTable
WHERE yourColumn IS NULL OR yourColumn = ''
回答by Sameera R.
If you need it in SELECTsection can use like this.
如果您在SELECT部分需要它可以像这样使用。
SELECT ct.ID,
ISNULL(NULLIF(ct.LaunchDate, ''), null) [LaunchDate]
FROM [dbo].[CustomerTable] ct
you can replace the nullwith your substitution value.
您可以null用您的替换值替换 。
回答by John Woo
SELECT *
FROM TableName
WHERE columnNAme IS NULL OR
LTRIM(RTRIM(columnName)) = ''
回答by Scott Sterling
To find rows where col is NULL, empty string or whitespace (spaces, tabs):
要查找 col 为NULL空字符串或空格（空格、制表符）的行：
SELECT *
FROM table
WHERE ISNULL(LTRIM(RTRIM(col)),'')=''
To find rows where col is NOT NULL, empty string or whitespace (spaces, tabs):
要查找 col 为NOT NULL空字符串或空格（空格、制表符）的行：
SELECT *
FROM table
WHERE ISNULL(LTRIM(RTRIM(col)),'')<>''
回答by Martin Smith
Some sargablemethods...
一些sargable方法...
SELECT *
FROM #T
WHERE SomeCol = '' OR SomeCol IS NULL;
SELECT *
FROM #T
WHERE SomeCol = ''
UNION ALL
SELECT *
FROM #T
WHERE SomeCol IS NULL;
SELECT *
FROM #T
WHERE EXISTS ((SELECT NULL UNION SELECT '') INTERSECT SELECT SomeCol);
And some non-sargable ones...
还有一些不可交易的......
SELECT *
FROM #T
WHERE IIF(SomeCol <> '',0,1) = 1;
SELECT *
FROM #T
WHERE NULLIF(SomeCol,'') IS NULL;
SELECT *
FROM #T
WHERE ISNULL(SomeCol,'') = '';
回答by Anouar Mokhtari
my best solution :
我最好的解决方案：
WHERE
COALESCE(char_length(fieldValue), 0) = 0
COALESCE returns the first non-null expr in the expression list().
COALESCE 返回表达式 list() 中的第一个非空 expr。
if the fieldValue is null or empty string then: we will return the second element then 0.
如果 fieldValue 为 null 或空字符串，则：我们将返回第二个元素，然后是 0。
so 0 is equal to 0 then this fieldValue is a null or empty string.
所以 0 等于 0 那么这个 fieldValue 是一个 null 或空字符串。
in python for exemple:
以python为例：
def coalesce(fieldValue):
if fieldValue in (null,''):
return 0
good luck
祝你好运
回答by ADM-IT
This is ugly MSSQL:
这是丑陋的 MSSQL：
CASE WHEN LTRIM(RTRIM(ISNULL([Address1], ''))) <> '' THEN [Address2] ELSE '' END
回答by Alireza Shabani
youe check null With IS NULL and string Empty With LEN(RTRIM(LTRIM(Column))) = 0 in
你检查 null With IS NULL 和 string Empty With LEN(RTRIM(LTRIM(Column))) = 0 in
SELECT *
FROM AppInfra.Person
WHERE LEN(RTRIM(LTRIM(NationalCode))) = 0 OR NationalCode IS NULL
回答by mohammed hussamuddin hussamudd
In sproc, you can use the following condition:
在 sproc 中，您可以使用以下条件：
DECLARE @USER_ID VARCAHR(15)=NULL --THIS VALUE IS NULL OR EMPTY DON'T MATTER
IF(COALESCE(@USER_ID,'')='')
PRINT 'HUSSAM'
