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.

Related: http://stackoverflow.com/questions/4042496/how-should-i-deal-with-null-parameters-in-a-pl-sql-stored-procedure-when-i-want-t/4042572#4042572 — OMG Ponies, Nov 05 '10 at 19:57
Is it not desirable to have multiple tests? i.e. more granularity = better feedback to users about how to correct data. — onedaywhen, Nov 08 '10 at 09:15
@onedaywhen: In general it may be better to have more granularity, but I'm working with an existing code base and I want to to mimic the existing code structure as closely as possible. The current code does only one test, so I was looking for a solution that also had only one test. — John Gordon, Nov 10 '10 at 17:36

Justin Cave · Accepted Answer · 2010-11-05T20:37:03.877

124

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.

edited Nov 05 '10 at 20:37

answered Nov 05 '10 at 19:56

Justin Cave

227,342
24
367
384

41

Note for T-SQL users: there is no TRIM, you'll need LTRIM or RTRIM. – demoncodemonkey Aug 07 '15 at 08:25
16

Since **SQL Server 2017** there is a TRIM function. [Source](https://learn.microsoft.com/en-us/sql/t-sql/functions/trim-transact-sql) – bvwidt May 18 '17 at 10:52
1

@EhMann365 this question is about Oracle, not Sql Server. – M H Jan 30 '18 at 15:07
15

RTRIM(LTRIM(column_name)) - For Microsft SQL Users. – DxTx Apr 23 '18 at 16:21
nice little trick, works well as equivalent to String.IsnullOrWhiteSpace in c#. – Gautam Oct 28 '19 at 18:11
11

In Sql Server TRIM(column_name) returns empty string '', not NULL. Other answers support both empty string and NULL – Michael Freidgeim Jan 29 '20 at 01:00
1

For those who are expecting to cover all cases: TRIM doesn't work for new line characters – montonero Feb 10 '21 at 08:15
1

Use TRIM(ISNULL(column_name, '')) = '' – Ε Г И І И О May 23 '22 at 07:37
This will not work with empty string – Tanu Jul 25 '23 at 15:14
@Tanu - Define "not work". Oracle considers the empty string to be NULL so trim(NULL) would still be NULL. Other databases would have different semantics but the question is tagged for Oracle. – Justin Cave Jul 25 '23 at 15:37
@JustinCave on using select * from table where trim(columnname) is not NULL, gives me the data where column is not null and all the empty strings as well. I tried it on MySQL database. – Tanu Jul 28 '23 at 14:43
@Tanu - MySQL is very different from Oracle. MySQL treats the empty string as distinct from NULL. Oracle was not. The question was tagged for Oracle so the answer works for Oracle. Each database will have a slightly different answer because each dialect of SQL has slightly different semantics, different databases have different built-in functions, etc. – Justin Cave Jul 28 '23 at 15:10

GendoIkari · Answer 2 · 2013-11-13T19:33:05.650

33

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

edited Nov 13 '13 at 19:33

answered Nov 05 '10 at 19:47

GendoIkari

11,734
6
62
104

3

actually this still doesn't work in Oracle, Oracle treats empty strings as NULL so you may not have a clause of "not like ''" since it's comparing against a null value – Harrison Nov 05 '10 at 19:59
Wouldn't I want LIKE '' instead of NOT LIKE '' ? – John Gordon Nov 05 '10 at 20:05
Yes, sorry, misread the original question as wanting everything that has a value. But as tanging pointed out, it doesn't look like this will work in Oracle. – GendoIkari Nov 05 '10 at 20:06
Since I'm using Oracle, it looks like I can use WHERE TRIM(col) IS NULL and that will take care of null, blank, and whitespace values. – John Gordon Nov 05 '10 at 20:22
Obviously, the first part is missing a parentheses, it should be: `WHERE RTRIM(ISNULL(column_name, '')) LIKE ''` – Serj Sagan Nov 13 '13 at 19:29

MerrickPlainview · Answer 3 · 2016-02-05T18:31:30.927

25

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

edited Feb 05 '16 at 18:31

answered Feb 05 '16 at 16:32

MerrickPlainview

261
3
3

2

A good answer would contain an explanation of the code, and why it was written this way. I recommend you update your answer :-) – Qirel Feb 05 '16 at 16:52
1

Whats the point of this? It would be more efficient to just set it to [column_name] LIKE '' – SILENT Dec 26 '16 at 23:09
It needs TRIM to support multiple white spaces – Michael Freidgeim Jan 29 '20 at 06:16
2

Testing on SQL Server 2017, this does work for multiple white spaces without TRIM. It doesn't make sense to me, because according the documentation NULLIF should return NULL only if the two expressions are equal, and a string of spaces does not usually equal an empty string. (I only tested for spaces, not tabs, newlines, or other whitespace characters.) – Dana Jul 17 '20 at 14:48
1

@Jovie SQL Server sometimes ignores trailing whitespace, read: https://stackoverflow.com/questions/17876478/why-the-sql-server-ignore-the-empty-space-at-the-end-automatically – Brian MacKay Nov 11 '20 at 20:45
And this is much more faster than trim :) – Avinash Reddy May 31 '21 at 02:01

score 9 · Answer 4 · edited May 18 '17 at 11:05

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

WITH t as ( select ' ' c union all select '' c union all select NULL) SELECT * FROM t WHERE LTRIM(RTRIM(c)) IS NULL; didn't returned 3 records on SQL server — Waqar, Nov 29 '16 at 12:16
Why would both L & RTRIM be needed? Wouldn't only one of the functions suffice to check if column is empty? — SILENT, Dec 26 '16 at 23:08

score 6 · Answer 5 · answered Aug 02 '17 at 13:33

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.

score 3 · Answer 6 · answered Aug 23 '18 at 03:37

3

Use below query and it works

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

answered Aug 23 '18 at 03:37

dvenkateshreddy

31
3

score 3 · Answer 7 · answered Aug 30 '21 at 14:29

3

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

answered Aug 30 '21 at 14:29

Grandizer

2,819
4
46
75

score 1 · Answer 8 · answered Feb 24 '15 at 11:11

1

What I use for IsNotNullOrEmptyOrWhiteSpace in T-SQL is:

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

answered Feb 24 '15 at 11:11

TiltonJH

51
2

score 1 · Answer 9 · edited Jun 01 '23 at 10:44

1

This covers null and empty spaces

TRIM(ColumnName) <> ''

edited Jun 01 '23 at 10:44

LinFelix

1,026
1
13
23

answered Jun 01 '23 at 07:59

NamotoD

31
2

score 0 · Answer 10 · edited Apr 28 '23 at 11:52

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.

score 0 · Answer 11 · edited Aug 19 '21 at 11:44

0

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

edited Aug 19 '21 at 11:44

Slava Rozhnev

9,510
6
23
39

answered Aug 19 '21 at 09:11

Sowmiya

1
3

score 0 · Answer 12 · answered Jul 01 '22 at 06:35

0

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

answered Jul 01 '22 at 06:35

Suraj Verma

463
6
8

score -3 · Answer 13 · answered Jan 23 '15 at 09:57

-3

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

answered Jan 23 '15 at 09:57

Marina Planells

117
9

score -6 · Answer 14 · answered Aug 20 '15 at 00:47

-6

you can use

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

answered Aug 20 '15 at 00:47

gong15A

77
1
2
3

This just returns any record with a space or NULL – Kaspaar Jun 01 '21 at 14:48

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

14 Answers14

Linked