Blank field not space in char and varchar field 
Author Message
 Blank field not space in char and varchar field
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



Sat, 16 Nov 2002 03:00:00 GMT
 Blank field not space in char and varchar field

I assume you are asking if you can store an empty string ('') in SQL Server
7. The answer is most definitely.

Tom Stearns


Quote:
> 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



Mon, 18 Nov 2002 03:00:00 GMT
 Blank field not space in char and varchar field

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



Quote:
> 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



Sun, 17 Nov 2002 03:00:00 GMT
 Blank field not space in char and varchar field
Correct. But you must not be in 60 or 65 compatibility mode. Try below:

EXEC sp_dbcmptlevel 'pubs', 70
GO
SELECT '"' + '' + '"'
GO
EXEC sp_dbcmptlevel 'pubs', 65
GO
SELECT '"' + '' + '"'

--
Tibor Karaszi, Cornerstone Sweden AB
MCDBA, MCSE, MCSD, MCT, SQL Server MVP
Please reply to the newsgroup only, not by email.


Quote:
> 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



Sun, 17 Nov 2002 03:00:00 GMT
 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



Sun, 17 Nov 2002 03:00:00 GMT
 Blank field not space in char and varchar field
Many thanks !


Quote:
> Correct. But you must not be in 60 or 65 compatibility mode. Try below:

> EXEC sp_dbcmptlevel 'pubs', 70
> GO
> SELECT '"' + '' + '"'
> GO
> EXEC sp_dbcmptlevel 'pubs', 65
> GO
> SELECT '"' + '' + '"'

> --
> Tibor Karaszi, Cornerstone Sweden AB
> MCDBA, MCSE, MCSD, MCT, SQL Server MVP
> Please reply to the newsgroup only, not by email.



> > 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



Sun, 17 Nov 2002 03:00:00 GMT
 Blank field not space in char and varchar field
I assume you are asking if you can store an empty string ('') in SQL Server
7. The answer is most definitely.

Tom Stearns


Quote:
> 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



Mon, 18 Nov 2002 03:00:00 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. varchar fields should be empty, but contain 1 space char

2. Changed from Char to VarChar and now I have lots of trailing blank spaces

3. Limit on VARCHAR and CHAR fields?

4. DTS Export truncates varchar fields to 256 char

5. Primary Key Field - char or varchar?

6. Performance difference on Varchar vs Char field

7. need varchar/char field size

8. char and varchar fields

9. Appending special char-s to VARCHAR field/var

10. Stripping misc. chars from varchar fields, how to

11. Char & Varchar fields

12. How to save spaces for BLANK fields?


 
Powered by phpBB® Forum Software