1
SELECT 1 FROM dual where trim('X ') = 'X'

Gives 1.

SELECT 1 FROM dual where trim(' ') = ''

Gives no rows.

Is there an easy way to select all rows where field is whitespace short of regex?

Is there a way to "trick" TRIM into TRIMming ' ' and giving ''?

ruffin
  • 16,507
  • 9
  • 88
  • 138

1 Answers1

6

In Oracle, the empty string is NULL. So the conventional approach would be something like

SELECT 1 
  FROM dual 
 WHERE trim(' ') IS NULL

That will also, of course, return results where the column is NULL. If you want cases where the column is non-NULL and is solely composed of spaces

SQL> ed
Wrote file afiedt.buf

  1  with t as (
  2    select 1 id, ' ' str from dual union all
  3    select 2, null from dual union all
  4    select 3, 'a' from dual
  5  )
  6  select *
  7    from t
  8   where trim(str) is null
  9*    and str is not null
SQL> /

        ID S
---------- -
         1

Just to round out the answer, if you want to use a simple regexp_like as well

SQL> ed
Wrote file afiedt.buf

  1  with t as (
  2    select 1 id, ' ' str from dual union all
  3    select 2, null from dual union all
  4    select 3, 'a ' from dual
  5  )
  6  select *
  7    from t
  8*  where regexp_like( str, '^[[:space:]]+$' )
SQL> /

        ID ST
---------- --
         1
Community
  • 1
  • 1
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • 2
    This might not work as expected if the OP is trying to tell NULLs from BLANKs. Perhaps something like select count(*) from ( select ' ' txt from dual union select '' from dual union select null from dual ) where txt is not null and trim(txt) is null – archimede Jun 20 '12 at 13:56
  • @arch -- yep, you're on the money, combined with the secret sauce of "the empty string is NULL". /sigh. Would not have guessed that. Thanks Justin & archimede. Money. Justin -- Great answer on the history of '' == NULL iff Oracle at that link too, **which implies** `WHERE trim(' ') IS NULL` might not always work for VARCHAR fields (but will for VARCHAR2). – ruffin Jun 20 '12 at 18:30