Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: blank fields in Oracle

Re: blank fields in Oracle

From: DA Morgan <damorgan_at_exesolutions.com>
Date: Thu, 13 Mar 2003 09:09:06 -0800
Message-ID: <3E70BB32.F2DC3E2D@exesolutions.com>


Ray Porter wrote:

> Thanks for the reply. So you're saying that if we use CHAR, Oracle will
> preserve a blank in the field, even if we do not specify PRESERVE BLANKS in
> the control file? If so, that's actually the behavior we want. It's okay
> if Oracle treats a trimmed blank as a null. My problem is that our Database
> Management folks are specifying that all character fields, except those with
> a length of 1, have to be varchar2. If I understand what you're saying, we
> actually need CHAR for all our elements except a few that need greater than
> 2000 byte length. Um, I need to convince our db person to allow me to test
> this. That way I might be able to make a case for an exception to the rule.
>
> Thanks,
> Ray Porter
>
> "FC" <flavio_at_tin.it> wrote in message
> news:Sg0ca.73179$zo2.1898550_at_news2.tin.it...
> >
> > Did you try using the CHAR datatype?
> > If you use CHAR, the minimum length is 1, max is 2000 (at least in version
> > 8.1.7), if you insert a zero length string, Oracle will pad it with a
> blank
> > automatically.
> > Oracle treats zero length strings as nulls, you can easily test this
> > situation executing the following sql:
> >
> > SELECT NVL2(TRIM(' '), 'NOT NULL', 'NULL') FROM DUAL;
> >
> > result: 'NULL'
> >
> > SELECT NVL2(TRIM('X'), 'NOT NULL', 'NULL') FROM DUAL;
> >
> > result: 'NOT NULL'
> >
> >
> > Bye,
> > Flavio
> >
> >

Rule of thumb is to never use CHAR in Oracle. VARCHAR2 is both more efficient for storage and because of the trailing blanks issue ... makes string comparison's problematic.

If moving from CHAR to VARCHAR2 the spaces are not removed. The spaces are just not right padded with new inserts/updates. You can do the following to remove leading and trailing spaces.

UPDATE <table>
SET <column_name> = TRIM(<column_name>);

Daniel Morgan Received on Thu Mar 13 2003 - 11:09:06 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US