|
Home > Archive > MS SQL Server > May 2005 > Trailing spaces
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Dave S. 2005-05-17, 9:23 am |
| During an import from access to SQL, some text fields were imported into
CHAR fields and for some reason retained trailing spaces to make up for the
difference in field size. Did I miss a setting somewhere?
| |
| Jens Süßmeyer 2005-05-17, 9:23 am |
| That is by design, because Char is a fxed length data type, look in the BOL
missing bytes are filled up with spaces.
Perhaps you want to use varchar instead.
char and varchar
Fixed-length (char) or variable-length (varchar) character data types.
--
HTH, Jens Suessmeyer.
---
http://www.sqlserver2005.de
---
"Dave S." < davidstedman@collier
gov.net> schrieb im Newsbeitrag
news:%23atKMBuWFHA.2540@tk2msftngp13.phx.gbl...
> During an import from access to SQL, some text fields were imported into
> CHAR fields and for some reason retained trailing spaces to make up for
> the
> difference in field size. Did I miss a setting somewhere?
>
>
| |
| Scott Morris 2005-05-17, 9:23 am |
|
"Jens Süßmeyer" < Jens@Remove_this_For
_Contacting.sqlserver2005.de> wrote in
message news:OgM%23iJuWFHA.3184@TK2MSFTNGP15.phx.gbl...
> That is by design, because Char is a fxed length data type, look in the
BOL
> missing bytes are filled up with spaces.
>
> Perhaps you want to use varchar instead.
> char and varchar
> Fixed-length (char) or variable-length (varchar) character data types.
In general, this is true. However, the ansi_padding setting has an effect
based on the nullability of the column. The following script illustrates
this.
set ansi_padding off
set nocount on
go
create table test (testdesc char(10) not null, testdesc1 char(10) null)
go
insert test (testdesc, testdesc1) values ('test ', 'test ')
select '[' + testdesc + ']', len(testdesc), datalength(testdesc)
,
'[' + testdesc1 + ']', len(testdesc1), datalength(testdesc1
)
from test
go
drop table test
go
| |
|
|
|
|
|