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

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


sql oracle oracle10g

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.

sql oracle oracle10g 

SELECT column_name from table_nameWHERE 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_nameWHERE RTRIM(NVL(column_name, '')) LIKE ''

sql oracle oracle10g

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

Recent Posts
How can I color dots in a xy scatterplot according to column value?
How to update a claim in ASP.NET Identity?
What does {0} mean when initializing an object?
Accessing members of items in a JSONArray with Java
How to log SQL statements in Spring Boot?
Powershell Get-WebSite name parameter is ignored
How to detect scroll to bottom of html element
Java synchronized method
How to test controllers with CodeIgniter?
Detect Visual Composer
Matplotlib: Specify format of floats for tick labels
Rails join a list of strings with commas and "and" before the last