How to convert blank to NULL in oracle query

Question

I have blanks in my name column in sql query. How can I replace to show as null.

SELECT Name from table

Answer 1

The TRIM function provides this feature.

It is used like this:

select TRIM(Name) from table

It will remove leading and trailing spaces from the results for field Name.

Answer 2

Maybe you are talking of spaces?

Here is how to remove any commonly known "blank" chars:

• with regexp_replace (interestingly... just to notice the [[:space:]])

---

select '<'||
  regexp_replace(
   'a'||CHR(9)||' b'||CHR(10)||CHR(11)
      ||'c'||CHR(12)||CHR(13)||' d'
 , '[[:space:]]','')
||'>' 
from dual;

• more efficient: avoid regexp_replace...: use TRANSLATE!

---

select '<'||
  TRANSLATE(
   'a'||CHR(9)||' b'||CHR(10)||CHR(11)
      ||'c'||CHR(12)||CHR(13)||' d' -- complicate string with blank
  ,'A'||CHR(9)||CHR(10)||CHR(11)||CHR(12)||CHR(13)||' '
  ,'A') -- this 'A' is a trick to replace by null ('')
||'>'    -- to show where string stops
from dual;

Answer 3

TRIM removes the blank character from left and right, so if your string only consists of blanks then you get an empty string, which is NULL in Oracle.

select trim(name) from mytable;

This would also change ' Mary Smith ' to 'Mary Smith', but I guess you wouldn't mind :-)

If, however, you want to consider any whitespace, e.g. tabs, too, then TRIM doesn't suffice. You can use REGEXP_REPLACE then to replace all names that only consist of whitespace with null.

regexp_replace(name, '^[[:space:]]*$', null) from mytable;

If you also want to trim whitespace from any names (so ' Mary Smith ' becomes 'Mary Smith' again) then:

select regexp_replace(name, '^[[:space:]]*([^[:space:]]*)[[:space:]]*', '\1') from mytable;