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.
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
What I use for IsNotNullOrEmptyOrWhiteSpace
in T-SQL is:
SELECT [column_name] FROM [table_name]
WHERE LEN(RTRIM(ISNULL([column_name], ''))) > 0
Use below query and it works
SELECT column_name FROM table_name where isnull(column_name,'') <> ''
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:
TRIM(), RTRIM(), LTRIM()
RTRIM(), LTRIM()
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 :)
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.
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 '%_%';
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 ''
you can use
SELECT [column_name]
FROM [table_name]
WHERE [column_name] LIKE '% %'
OR [column_name] IS NULL
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.
Source: Stackoverflow.com