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

Question

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

User · Accepted Answer

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

User · Answer

What I use for IsNotNullOrEmptyOrWhiteSpace in T-SQL is   SELECT  column name  FROM  table name  WHERE LEN RTRIM ISNULL  column name          gt  0

User · Answer

you can use  SELECT  column name   FROM  table name  WHERE  column name  LIKE        OR  column name  IS NULL

User · Answer

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

User · Answer

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

User · Answer

Use below query and it works  SELECT column name FROM table name where isnull column name      lt  gt

User · Answer

The NULLIF function will convert any column value with only whitespace into a NULL value  Works for T-SQL and SQL Server 2008  amp  up   SELECT  column name  FROM  table name  WHERE NULLIF  column name       IS NULL

User · Answer

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

User · Answer

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  lt  1 but for some reason  lt  2 worked and only returned records whose field is just blank  select  columnName  from  tableName  where LENGTH columnName   lt  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  lt  2 works but not  lt  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

User · Answer

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

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

Examples related to sql

Examples related to oracle

Examples related to oracle10g