ASE Database forum - Sybase 12.5 ASE automatically fill spaces in varchar

Author

Sybase 12.5 ASE automatically fill spaces in varchar

David Hsia

2005-04-18, 3:24 am

I create a table with a column as varchar (1024). But it was filled with
spaces after I insert a short string. see below:
--------------- start of cut -----------------------
create table test1
(
field varchar(1024)
)
go

insert into test1 values ('this is a test')
go

select rtrim(field)
from test1
go
---------------- end of cut -----------------------
It prints 'this is a test ...... ' ~ 1024
bytes.

The rtrim() doesn't work, because there is a '\n' at the end of string.
Is this server configuration problem or I miss something? Please help.
Thanks in advance,
David

Mark K

2005-04-18, 7:24 am

Are you running 12.5 GA, or possibly 12.5.2? If 12.5.2, I believe you are
hitting CR 358007, which is fixed in 12.5.2 ESD2. An alternative is to boot
with -T7709.

Mark Kusma
http://www.sybase.com/support/about...t/otherservices

"David Hsia" <davidhsia@patmedia.net> wrote in message
news:42632a47$1@foru
ms-2-dub...
> I create a table with a column as varchar (1024). But it was filled with
> spaces after I insert a short string. see below:
> --------------- start of cut -----------------------
> create table test1
> (
> field varchar(1024)
> )
> go
>
> insert into test1 values ('this is a test')
> go
>
> select rtrim(field)
> from test1
> go
> ---------------- end of cut -----------------------
> It prints 'this is a test ...... ' ~
1024
> bytes.
>
> The rtrim() doesn't work, because there is a '\n' at the end of string.
> Is this server configuration problem or I miss something? Please help.
> Thanks in advance,
> David
>
>

2005-04-18, 9:24 am

Were you using isql to update and display the data. If so
you got what I would have expected. isql still thinks the
column is 1024 bytes in size and attempts to display that.
Use a convert(char(..), field)
> I create a table with a column as varchar (1024). But it
> was filled with spaces after I insert a short string. see
> below: --------------- start of cut
> ----------------------- create table test1
> (
> field varchar(1024)
> )
> go
>
> insert into test1 values ('this is a test')
> go
>
> select rtrim(field)
> from test1
> go
> ---------------- end of cut -----------------------
> It prints 'this is a test
> ..... ' ~ 1024 bytes.
>
> The rtrim() doesn't work, because there is a '\n' at the
> end of string. Is this server configuration problem or I
> miss something? Please help. Thanks in advance,
> David
>
>

Muralidharan

2005-04-19, 3:24 am

I think this should be a client issue since ASE stores the
data without spaces:

1> create table tes( a varchar(1024) )
2> go
1> insert tes values('this is a test')
2> go
(1 row affected)
1> select first,root from sysindexes where id =
object_id('tes')
2> go
first root
----------- -----------
617 617

(1 row affected)
1> dbcc traceon(3604)
2> go
DBCC execution completed. If DBCC printed error messages,
contact a user with System Administrator (SA) role.
1> dbcc page(tempdb,617,1)
2> go
...............................
DATA:
Row-Offset table at the end of the row: (<varcol number>,
<offset from start of the row>, <varcol length> )

Offset 32 - row ID=0 row length=21 # varlen cols=1
0xb64c1820 ( 0): 01001500 74686973 20697320 61207465
....this is a te
0xb64c1830 ( 16): 73740212 04
st...
Row-Offset table for variable-length columns:
[1, 4, 14]

OFFSET TABLE:
Row - Offset
0 (0x00) - 32 (0x0020),

As you can see, the data's length is 14 characters.

HTH

> I create a table with a column as varchar (1024). But it
> was filled with spaces after I insert a short string. see
> below: --------------- start of cut
> ----------------------- create table test1
> (
> field varchar(1024)
> )
> go
>
> insert into test1 values ('this is a test')
> go
>
> select rtrim(field)
> from test1
> go
> ---------------- end of cut -----------------------
> It prints 'this is a test
> ..... ' ~ 1024 bytes.
>
> The rtrim() doesn't work, because there is a '\n' at the
> end of string. Is this server configuration problem or I
> miss something? Please help. Thanks in advance,
> David
>
>

Sponsored Links