如何检查空/空/空格值与一个单一的测试？

我想编写一个 SELECT 语句，它只使用一个测试来返回没有值的列(空、空或所有空格)。

我以为这样能行:

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

但是这对于 NULL 值不起作用。

当然可以

OR column_name IS NULL

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

401863 次浏览
小开 
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.

UPDATE

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

SELECT column_name from table_name
WHERE RTRIM(NVL(column_name, '')) LIKE ''
小开
最佳答案

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.

小开

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

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

What I use for IsNotNullOrEmptyOrWhiteSpace in T-SQL is:

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

you can use

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

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

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

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

Every Database doesn't support TRIM method.

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:

  • MySQL: TRIM(), RTRIM(), LTRIM()
  • Oracle: 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

  2. Use of LTRIM & RTRIM to check-

    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 FirstName from UserDetails table if it has an empty LastName

Hoping this will help others too :)

小开

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

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.

小开

Use below query and it works

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

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.

*****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

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

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.

小开

You can also check this. This works for me, when you want to fetch both null value and another condition (i.e) my query should return the rows where columnname should be null and should have the word 'questiontext'.

SELECT *
FROM `table_name`
WHERE (('columnname' IS NULL) OR (TRIM('columnname') LIKE 'questiontext'))

Here, my 'columnname' should be equal to 'questiontext'.

小开

Although @MerrickPlainview answer seems close and small, the full answer (to also deal with white space as the OP asked for) would be this:

SELECT column_name FROM table_name WHERE NULLIF(TRIM(column_name), '') IS NOT NULL
小开

select * from table_name where column_name NOT Like '%_%' or column_name is null


提交答案