CODEWITHSUNDEEP
sqloracle-database
Aruna Raghunam
Aruna Raghunam

Reputation: 921

How to convert blank to NULL in oracle query

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

SELECT Name from table

Upvotes: 1

Views: 14642

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

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;

Upvotes: 0

J. Chomel
J. Chomel

Reputation: 8395

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;

Upvotes: 0

Ren&#233; Hoffmann
Ren&#233; Hoffmann

Reputation: 2815

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.

Upvotes: 2

Related Questions