如何用一个测试检查空/空/空白值?

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语句，它只使用一个测试来返回没有值的列(null、空或所有空格)。

I thought this would work:

我认为这行得通:

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

But this does not work for NULL values.

但这对空值不起作用。

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.

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

8 个解决方案

#1

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)上有一个基于函数的索引。

#2

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.

如果column_name为空，ISNULL(column_name，”)将返回“，否则将返回column_name。

UPDATE

更新

In Oracle, you can use NVL to achieve the same results.

在Oracle中，可以使用NVL实现相同的结果。

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

#3

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

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

Every Database doesn't support TRIM method.

每个数据库都不支持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()
• MySQL:修剪(),空白(),LTRIM()
• Oracle: RTRIM(), LTRIM()
• Oracle:空白(),LTRIM()
• SQL Server: TRIM(), RTRIM(), LTRIM()
• SQL Server: 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

   从UserDetails中选择FirstName，其中TRIM(LastName)为空。

2. Use of LTRIM & RTRIM to check-

   使用LTRIM和RTRIM检查-。

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

   在LTRIM(RTRIM(LastName)为空时，从UserDetails中选择FirstName

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

以上两种方法都提供相同的结果，只是基于您的数据库支持。如果UserDetails表中有一个空的LastName，它就会返回FirstName

Hoping this will help others too :)

希望这也能帮助别人。

#4

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函数将只使用空格的任何列值转换为空值。适用于T-SQL和SQL Server 2008 & up。

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

#5

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

这个phpadmin myquery返回的行不是空行，也不是空行，或者只是白空间:

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。

#6

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 '%_%';

#7

What I use for IsNotNullOrEmptyOrWhiteSpace in T-SQL is:

我使用T-SQL中的IsNotNullOrEmptyOrWhiteSpace是:

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

#8

you can use

您可以使用

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