SQL 如何通过单个测试检查空/空/空白值？

How to check for null/empty/whitespace values with a single test?

提问by John Gordon

I'd like to write a SELECT statement that uses just one test to return columns with no value (null, empty, or all spaces).

我想编写一个 SELECT 语句，该语句仅使用一个测试来返回没有值（空、空或所有空格）的列。

I thought this would work:

我认为这会奏效：

SELECT column_name from table_name WHERE column_name NOT LIKE '%_%';

But this does not work for NULL values.

但这不适用于 NULL 值。

Of course I can add

当然我可以添加

OR column_name IS NULL

and it will work, but I'd like a way that uses a single test.

它会起作用，但我想要一种使用单个测试的方法。

回答by Justin Cave

Functionally, you should be able to use

从功能上讲，您应该能够使用

SELECT column_name
  FROM table_name
 WHERE TRIM(column_name) IS NULL

The problem there is that an index on COLUMN_NAME would not be used. You would need to have a function-based index on TRIM(column_name) if that is a selective condition.

问题是不会使用 COLUMN_NAME 上的索引。如果这是一个选择性条件，您将需要在 TRIM(column_name) 上有一个基于函数的索引。

回答by GendoIkari

SELECT column_name from table_name
WHERE RTRIM(ISNULL(column_name, '')) LIKE ''

ISNULL(column_name, '')will return '' if column_name is NULL, otherwise it will return column_name.

ISNULL(column_name, '')如果 column_name 为 NULL，将返回 ''，否则将返回 column_name。

UPDATE

更新

In Oracle, you can use NVLto achieve the same results.

在 Oracle 中，您可以使用它NVL来达到相同的效果。

SELECT column_name from table_name
WHERE RTRIM(NVL(column_name, '')) LIKE ''

回答by MerrickPlainview

The NULLIF function will convert any column value with only whitespace into a NULL value. Works for T-SQL and SQL Server 2008 & up.

NULLIF 函数会将任何只有空格的列值转换为 NULL 值。适用于 T-SQL 和 SQL Server 2008 及更高版本。

SELECT [column_name]
FROM [table_name]
WHERE NULLIF([column_name], '') IS NULL

回答by Vikash Pandey

While checking null or Emptyvalue for a column, I noticed that there are some support concerns in various Databases.

在检查null or Empty列的值时，我注意到各种数据库中存在一些支持问题。

Every Database doesn't support TRIMmethod.

每个数据库都不支持 TRIM方法。

Below is the matrix just to understand the supported methods by different databases.

下面的矩阵只是为了了解不同数据库支持的方法。

The TRIM function in SQL is used to remove specified prefix or suffix from a string. The most common pattern being removed is white spaces. This function is called differently in different databases:

SQL 中的 TRIM 函数用于从字符串中删除指定的前缀或后缀。被删除的最常见模式是空格。该函数在不同数据库中的调用方式不同：

• MySQL:TRIM(), RTRIM(), LTRIM()
• Oracle:RTRIM(), LTRIM()
• SQL Server:TRIM(), RTRIM(), LTRIM()

• MySQL：TRIM(), RTRIM(), LTRIM()
• 甲骨文：RTRIM(), LTRIM()
• SQL 服务器：TRIM(), RTRIM(), LTRIM()

How to Check Empty/Null/Whitespace :-

如何检查空/空/空白：-

Below are two different ways according to different Databases-

以下是根据不同数据库的两种不同方式-

The syntax for these trim functions are:

这些修剪函数的语法是：

1. Use of Trim to check-

   SELECT FirstName FROM UserDetails WHERE TRIM(LastName) IS NULL

2. Use of LTRIM & RTRIM to check-

   SELECT FirstName FROM UserDetails WHERE LTRIM(RTRIM(LastName)) IS NULL

1. 使用 Trim 检查-

   SELECT FirstName FROM UserDetails WHERE TRIM(LastName) IS NULL

2. 使用 LTRIM 和 RTRIM 检查-

   SELECT FirstName FROM UserDetails WHERE LTRIM(RTRIM(LastName)) IS NULL

Above both ways provide same result just use based on your DataBase support. It Just returns the FirstNamefrom UserDetailstable if it has an empty LastName

以上两种方式提供相同的结果，只是根据您的数据库支持使用。如果它有空，它只返回FirstNamefromUserDetails表LastName

Hoping this will help others too :)

希望这也能帮助其他人:)

回答by Chose

This phpMyAdmin query is returning those rows, that are NOT null or empty or just whitespaces:

这个 phpMyAdmin 查询正在返回那些行，这些行不是 null 或空的或只是空格：

SELECT * FROM `table_name` WHERE NOT ((`column_name` IS NULL) OR (TRIM(`column_name`) LIKE ''))

if you want to select rows that are null/empty/just whitespaces just remove NOT.

如果您想选择空/空/只是空格的行，只需删除 NOT。

回答by dvenkateshreddy

Use below query and it works

使用下面的查询，它的工作原理

SELECT column_name FROM table_name where isnull(column_name,'') <> ''

回答by CatWoman

Every single persons suggestion to run a query in Oracle to find records whose specific field is just blank, (this is not including (null) or any other field just a blank line) did not work. I tried every single suggested code. Guess I will keep searching online.

每个人都建议在 Oracle 中运行查询以查找特定字段为空白的记录（这不包括 (null) 或任何其他字段只是一个空行）没有奏效。我尝试了每一个建议的代码。猜猜我会继续在线搜索。

*****UPDATE*****

*****更新*****

I tried this and it worked, not sure why it would not work if < 1 but for some reason < 2 worked and only returned records whose field is just blank

我试过这个并且它起作用了，不知道为什么它在 < 1 但由于某种原因 < 2 起作用并且只返回字段为空的记录时不起作用

select [columnName] from [tableName] where LENGTH(columnName) < 2 ;

select [columnName] from [tableName] where LENGTH(columnName) < 2 ;

I am guessing whatever script that was used to convert data over has left something in the field even though it shows blank, that is my guess anyways as to why the < 2 works but not < 1

我猜测用于转换数据的任何脚本在字段中留下了一些东西，即使它显示为空白，这也是我的猜测，为什么 < 2 有效但不 < 1

However, if you have any other values in that column field that is less than two characters then you might have to come up with another solution. If there are not a lot of other characters then you can single them out.

但是，如果该列字段中的任何其他值少于两个字符，那么您可能必须想出另一种解决方案。如果没有很多其他字符，那么您可以将它们单独列出。

Hope my solution helps someone else out there some day.

希望我的解决方案有一天能帮助其他人。

回答by TiltonJH

What I use for IsNotNullOrEmptyOrWhiteSpacein T-SQL is:

我IsNotNullOrEmptyOrWhiteSpace在 T-SQL 中使用的是：

SELECT [column_name] FROM [table_name]
WHERE LEN(RTRIM(ISNULL([column_name], ''))) > 0

回答by Marina Planells

As in Oracle you can use NVL function in MySQL you can use IFNULL(columnaName, newValue) to achieve your desired result as in this example

与在 Oracle 中一样，您可以在 MySQL 中使用 NVL 函数，您可以使用 IFNULL(columnaName, newValue) 来实现您想要的结果，如本例所示

SELECT column_name from table_name WHERE IFNULL(column_name,'') NOT LIKE '%_%';

回答by gong15A

you can use

您可以使用

SELECT [column_name] 
FROM [table_name]
WHERE [column_name] LIKE '% %' 
OR [column_name] IS NULL