How to check for null/empty/whitespace values with a single test?
SqlOracleOracle10gSql Problem Overview
I'd like to write a SELECT statement that uses just one test to return columns with no value (null, empty, or all spaces).
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.
Sql Solutions
Solution 1 - Sql
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.
Solution 2 - Sql
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 ''
Solution 3 - Sql
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
Solution 4 - Sql
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:
-
Use of Trim to check-
SELECT FirstName FROM UserDetails WHERE TRIM(LastName) IS NULL
-
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 :)
Solution 5 - Sql
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.
Solution 6 - Sql
Use below query and it works
SELECT column_name FROM table_name where isnull(column_name,'') <> ''
Solution 7 - Sql
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
Solution 8 - Sql
What I use for IsNotNullOrEmptyOrWhiteSpace
in T-SQL is:
SELECT [column_name] FROM [table_name]
WHERE LEN(RTRIM(ISNULL([column_name], ''))) > 0
Solution 9 - Sql
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.
Solution 10 - Sql
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'.
Solution 11 - Sql
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 '%_%';
Solution 12 - Sql
you can use
SELECT [column_name]
FROM [table_name]
WHERE [column_name] LIKE '% %'
OR [column_name] IS NULL