CODEWITHSUNDEEP
sqloracle-databaseoracle10g
John Gordon
John Gordon

Reputation: 33335

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

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.

Upvotes: 124

Views: 487880

Answers (14)

NamotoD
NamotoD

Reputation: 49

This covers null and empty spaces

TRIM(ColumnName) <> ''

Upvotes: 0

CatWoman
CatWoman

Reputation: 1

None of the suggestions worked for me. Here is what finally worked for me:

I am not sure why it would not work with < 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 are 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.

Upvotes: 0

Suraj Verma
Suraj Verma

Reputation: 453

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

Upvotes: 0

Grandizer
Grandizer

Reputation: 3025

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

Upvotes: 4

Sowmiya
Sowmiya

Reputation: 1

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'.

Upvotes: 0

dvenkateshreddy
dvenkateshreddy

Reputation: 31

Use below query and it works

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

Upvotes: 3

Chose
Chose

Reputation: 91

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.

Upvotes: 6

Vikash Pandey
Vikash Pandey

Reputation: 5443

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 :)

Upvotes: 9

MerrickPlainview
MerrickPlainview

Reputation: 281

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

Upvotes: 27

gong15A
gong15A

Reputation: 87

you can use

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

Upvotes: -6

TiltonJH
TiltonJH

Reputation: 51

What I use for IsNotNullOrEmptyOrWhiteSpace in T-SQL is:

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

Upvotes: 1

Marina Planells
Marina Planells

Reputation: 117

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

Upvotes: -3

GendoIkari
GendoIkari

Reputation: 11914

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

Upvotes: 35

Justin Cave
Justin Cave

Reputation: 231671

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.

Upvotes: 129

Related Questions