? Sybase truncating trailing whitespace? 
Author Message
 ? Sybase truncating trailing whitespace?

Hi folks --
        I'm having this problem (4.9.2 server running under Solaris 2.3)
where if I insert some varchar text into a table, Sybase seems to lose
any trailing whitespace I have at the end of it:

For example, the following SQL code:

insert data_info (data_type, table_type, srdb_id, datetime_val, data) values
(0,0,10299,getdate(),'abcde  ')

Yields:

1> select char_length(data) from data_info where srdb_id = 10299 and data_type = 0
2> go

 -----------
           5

(1 row affected)
1>

But, it ought to be 7 characters. Is this an option somewhere? Can I shut it
off? Or a known bug? Or is my syntax just incorrect?

Any hints appreciated!
Thanks,

---

# Opinions above aren't Sun's, and facts are not necessarily official.



Sun, 02 Feb 1997 05:35:42 GMT
 ? Sybase truncating trailing whitespace?


: Hi folks --
:       I'm having this problem (4.9.2 server running under Solaris 2.3)
: where if I insert some varchar text into a table, Sybase seems to lose
: any trailing whitespace I have at the end of it:
[...]
: But, it ought to be 7 characters. Is this an option somewhere? Can I shut it
: off? Or a known bug? Or is my syntax just incorrect?
It's a known "feature".  Drove us nuts.  Another one: "", " ", and "\0"
are identical!

Only solution is to either put a dummy character (e.g. a dot ".")
at the end, and have the client strip it, or have a length column
that specifies the real lenght, and the client pads the string,
if necessary.  Double ugly, since Sybase already has a virtual
length column for varchar's :-(

--

--  George Mason University   --  SITE PhD Program           --
--  The Telephone Connection  --  Director of Engineering    --

The views expressed above do not necessarily reflect the views
of George Mason University or The Telephone Connection.



Sun, 02 Feb 1997 10:17:21 GMT
 ? Sybase truncating trailing whitespace?

| Hi folks --
|       I'm having this problem (4.9.2 server running under Solaris 2.3)
| where if I insert some varchar text into a table, Sybase seems to lose
| any trailing whitespace I have at the end of it:

It is designed to be that way.  It's well documented (in system 10 manual
at least.)  "varchar(*) null/not null" and "char(*) null" columns will
always automatically truncate trailing blanks.  "char(*) not null" column
will always append trailing blanks to make the exact length.  It's not an
user-configurable option.  So we are pretty much stuck with it.  

P.S.
   I've heard that Informix's way of handling trailing blanks is different
from Sybase.  I guess ANSI standard doesn't include this?

--

Qing Vincent Yin                |       Repeat

                                |       Until 0 = 1;



Sun, 02 Feb 1997 11:00:08 GMT
 ? Sybase truncating trailing whitespace?

Quote:

>Hi folks --
>    I'm having this problem (4.9.2 server running under Solaris 2.3)
>where if I insert some varchar text into a table, Sybase seems to lose
>any trailing whitespace I have at the end of it

<...>

Quote:
>Is this an option somewhere? Can I shut it
>off? Or a known bug? Or is my syntax just incorrect?

>Any hints appreciated!
>Thanks,

>---

># Opinions above aren't Sun's, and facts are not necessarily official.

The behaviour you described is perfectly correct, varchar will always remove
trailing spaces.  Refer to the Commands Reference, under 'Datatypes' is a
section which describes the 'Treatment of Spaces in char, nchar, varchar,
and nvarchar Data'.  If you want to keep trailing spaces you must define
the column as 'char not null'.

Cheers,

Frank



Sun, 02 Feb 1997 23:00:10 GMT
 ? Sybase truncating trailing whitespace?

What type of interface are you using to query the table.
ISQL , DataWorkBench, ? This might have something to do with it.


Quote:

> Path:

ngc!ngcgate!uunet!promus.com!informix.com!barrnet.net!nntp.crl.com!pcnet.com!pa
gesat.net!pagesat.net!news.cerf.net!nntp-server.caltech.edu!netline-fddi.jpl.na
sa.gov!elroy.jpl.nasa.gov!swrinde!cs.utexas.edu!koriel!news2me.EBay.Sun.COM!jet
hro.Corp.Sun.COM!usenet
Quote:

> Newsgroups: comp.databases.sybase
> Subject: ? Sybase truncating trailing whitespace?
> Date: 16 Aug 1994 21:35:42 GMT
> Organization: The Unconfigured xvnews people
> Lines: 32


> NNTP-Posting-Host: medicated.corp.sun.com

> Hi folks --
>    I'm having this problem (4.9.2 server running under Solaris 2.3)
> where if I insert some varchar text into a table, Sybase seems to lose
> any trailing whitespace I have at the end of it:

> For example, the following SQL code:

> insert data_info (data_type, table_type, srdb_id, datetime_val, data) values
> (0,0,10299,getdate(),'abcde  ')

> Yields:

> 1> select char_length(data) from data_info where srdb_id = 10299 and
data_type = 0
> 2> go

>  -----------
>            5

> (1 row affected)
> 1>

> But, it ought to be 7 characters. Is this an option somewhere? Can I shut it
> off? Or a known bug? Or is my syntax just incorrect?

> Any hints appreciated!
> Thanks,

> ---

> # Opinions above aren't Sun's, and facts are not necessarily official.



Wed, 05 Feb 1997 05:45:58 GMT
 ? Sybase truncating trailing whitespace?

Quote:
Frank Tiemann writes:

> Daniel M. Rosenberg writes:
> > Hi folks --
> >       I'm having this problem (4.9.2 server running under Solaris 2.3)
> > where if I insert some varchar text into a table, Sybase seems to lose
> > any trailing whitespace I have at the end of it:

> The behaviour you described is perfectly correct, varchar will always
> remove trailing spaces.  Refer to the Commands Reference, under
> 'Datatypes' is a section which describes the 'Treatment of Spaces in
> char, nchar, varchar, and nvarchar Data'.  If you want to keep trailing
> spaces you must define the column as 'char not null'.

and Qing Yin writes:

> It is designed to be that way.  It's well documented (in system 10
> manual at least.)  "varchar(*) null/not null" and "char(*) null" columns
> will always automatically truncate trailing blanks.  "char(*) not null"
> column will always append trailing blanks to make the exact length.  
> It's not an user-configurable option.  So we are pretty much stuck with
> it.  
> P.S.
> I've heard that Informix's way of handling trailing blanks is
> different from Sybase.  I guess ANSI standard doesn't include this?

Typical Sybase (ugggh!).  This is indeed the behavior documented in Sybase  
manuals, and it completely contradicts ANSI SQL'92.  Since ANSI SQL'89  
didn't include VARCHAR as a supported datatype, Sybase was okay.  However,  
they will have to modify their varchar functionality if they expect to  
conform to ANSI SQL'92 (as will Oracle which has similar if different  
problems with VARCHARs and NULLs).  

As noted, Informix is completely ANSI SQL'92 compliant in this area.  
VARCHARs do NOT automatically truncate anything (although you can use  
string functions to perform that task if so desired) so 'abc  ' in a  
VARCHAR is different from 'abc' in a VARCHAR.  (On the other hand, all  
CHARs are blank-padded so these would be the same in a CHAR).  In  
addition, if you compare a VARCHAR to a CHAR, the comparison uses  
blank-padded protocol to the length of the CHAR (VARCHARs that are longer  
than the CHAR field will compare unequal even if the extra characters are  
blanks).  

Finally, '' is NOT a null (as it appears to be in Sybase and Oracle).  The  
only way to set a field to NULL is to assign it the value NULL or to not  
assign it any value (or to create it with a NULL value from a query).  ''  
is a zero-length character string in a VARCHAR and is the same as ' ' in a  
CHAR field (since all CHARs are blank-padded).  

Jon

--
Jon Rosen
"So remember when you're feeling very small and insecure, how amazingly unlikely is your birth, and pray that there's intelligent life somewhere up in space, because there's {*filter*} all down here on Earth." - Monty Python



Fri, 07 Feb 1997 10:35:11 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Distinct/varchar with trailing whitespace

2. bcp does not truncate trailing blanks

3. BCP importing: trailing blanks not truncated ???

4. Converting data from REAL to CHAR does not truncate trailing spaces

5. Trailing White Space truncated during inserts

6. Outputting table to text file with truncated data - no trailing spaces

7. Truncating trailing spaces on Oracle8

8. Truncate Truncate and More Truncate

9. Can't truncate transaction log (Sybase ASE 11.0.3.3 Linux)

10. Can't truncate transaction log (Sybase ASE 11.0.3.3 Linux)

11. Sybase/RDO Truncates Data

12. truncated numeric sybase field


 
Powered by phpBB® Forum Software