
Blank field not space in char and varchar field
Jeff,
With a CHAR(10) type column that does not allow nulls, it will always
contain 10 characters that are padded to the right with spaces if there are
less than 10 other characters. But with a VARCHAR column that doesn't allow
nulls, if you insert the following
INSERT INTO myTable VALUES ('')
When you select this you get an empty string(IE: no spaces and not null). If
you do this:
INSERT INTO myTable VALUES (' ')
you will get a single space. Just create a simple table with these data
types and experiment.
Andy
Quote:
> Thanks Andy
> I was led to believe that in SQL 7.0 it is possible to configure the
column
> or Server to allow nothing to be stored inside a char or varchar field
> without it being nullable. Are you saying that this is not the case? If
I
> store nothing in a varchar (10) for example and I select from it I
wouldn't
> expect it to return a space ' ' although I would expect this with a char
> (10) since it is fixed in it's length. Have I been misled or am I simply
> barking up the wrong tree?
> Jeff
> > Jeff,
> > If you define a column as CHAR(10) then you have 2 ways data will be
> > stored in it.
> > A. If you allow NULL's then it could be NULL
> > B. Otherwise it will always contain 10 characters, even if all
10
> > are spaces. It will pad spaces to the right of what ever you put in
there
> up
> > to a maximum of the column size. So 'AB' is really'AB '.
> > For VARCHAR(10) the choices are this:
> > A. If you allow NULL's then it could be NULL
> > B. A varchar will not add spaces, so if you have 'AB' then you
> have
> > 'AB'. If you set it to '' then its len(x) is 0.
> > Andy
> > > Hello
> > > I believe with SQL 7 we can stop it using single blank space when a
> field
> > is
> > > empty (ie inside a char or varchar). Can someone confirm this please
> and
> > if
> > > so can they explain how.
> > > Cheers
> > > Jeff Tulley