SybaseBlog

The other day I was asked what happens when you provide a string that is too long to a table or stored procedure parameter. Rather than guess, that sounds like an easy thing to test.

create table atable (stringvar varchar(5))
go

insert into atable values ("too long")
go

select * from atable
go

stringvar 
--------- 
too l 

So it would appear as if the string is silently truncated. Step two is to double-check the behaviour in the documentation. This time we can verify this behaviour by looking at the Reference Manual Volume 1: Building Blocks, Chapter 1: System and User-Defined Datatypes (Character datatypes), which states:

"Entries shorter than the assigned length are blank-padded; entires longer than the assigned length are truncated without warning, unless the string_rtruncation option to the set command is set to on."

http://download.sybase.com/pdfdocs/asg1250e/refman1.pdf

Bear in mind that standard ANSI/ISO SQL/92 states that these values should NOT be silently truncated, it should reject the value! Once again this is a default setting that is for your convenience, but actually makes your database less ANSI-compliant. We've seen this once before with regards to the treatment of NULLs.

http://sybasease.blogspot.com/2005/05/nulls-in-sybase-ase.html

So let's try it with the setting where it should be. Remember this will only affect the current session.

set string_rtruncation on

insert into atable values ("too long")
go

Server Message:  Number  9502, Severity  16
Server 'TEST_SYBASE', Line 1:
Data exception - string data right truncated 

select * from atable
go

stringvar 
--------- 
too l

By the way, reading a little further in the documentation reveals this interesting note:
"Fixed-length columns tend to take more storage space than variable-length columns, but are access somewhat faster."