Null Sting in Varchar Field

[Kevan Ball]

During our development and testing, we've encountered a problem with the

way Sybase handles empty strings. We're using Java/TopLink/JConnect for
our development environment. However, the problem can be simply
duplicated by using ISQL. We've duplicated the problem under WindowsNT
using both WISQL and ISQL. We've also duplicated the problem using ISQL
on an HP box (just to prove it's not a OpenClient issue related to
platform).

The problem:

We've defined a column as varchar (doesn't matter if it's nullable or
not, the same problem occurs).

Insert into the column an empty string (i.e. "").

When you select from the column, you always get a single space back.

Sybase explains on their web site that trailing spaces are always
removed when inserting into varchar columns. And indeed, no matter how
many spaces you put in a string, Sybase will return a string containing
one space. The question is, why does Sybase insist on keeping that last
space? When we insert an empty string, we'd like Sybase to return an
empty string.

Example:

Create table test (

a1 int not null,

a2 varchar(10) null,

a3 varchar(10) not null

)

insert into test (a1, a2, a3) values (1, "", "")

insert into test (a1, a2, a3) values (2, " ", " ")

insert into test (a1, a2, a3) values (3, " ", " ")

select a1, "."+a2+".", "."+a3+"." from test

Result:

a1

---------- ----------- ------------

1 . . . .

2 . . . .

3 . . . .

What we'd like to see is an empty string when we insert an empty string.

Is there a way to configure Sybase to do this for us? None of the can
remember encountering this with Sybase in the past. Is it something the
Adaptive Server has added? Is it possible we're using the wrong version
of OpenClient?

Here's our Sybase version (@@version):

Adaptive Server Enterprise/11.5.1/P/HP9000-800/HP-UX 10.0/SWR 8071
Rollup /opt/Thu Aug 20 19:41:45 PDT 1998

[joe]

It's been that way as long as I can remember. What your Sybase support says
is true, but not the root of the problem. Sybase implements both nullable
chars and all varchars as varing length strings. It tries to save a byte by
doubling up the use of the length byte. When the length is zero, the string
is null. Non-null strings are at least one character long.

I don't have access to Sybase at this moment to test the following, but I am
sure it is true. Because of the above implementation, another oddity is that
the string concatenation operator, "+", does not obey the normal rule about
operations on null values. For example:

declare @aChar1 varchar(1) null
declare @aChar2 varchar(1) null
declare @aChar3 varchar(1) null

select @aChar1 = "", @aChar2 = "", @aChar3 = ""

select @aChar1 + "." + @aChar2 + "." + @aChar3 /* Like you said, the
result is . . . */

select @aChar1 = null, @aChar2 = null, @aChar3 = null

select @aChar1 + "." + @aChar2 + "." + @aChar3 /* Surprize, the result
is ... */

Kevan Ball <Kevan...@born.com> wrote in message
news:37264313...@born.com...

[lburn...@my-dejanews.com]

Kevan,

To store an empty string in a char or varchar column, you must use the 'null'
(or 'NULL') keyword. If you use single or double quotes, Sybase will store a
single blank (see Transact-SQL manual, Chapter 8). So instead of:

insert into test (a1, a2, a3) values (1, "", "")

Do this:

insert into test (a1, a2, a3) values (1, null, null)

Hope this helps,

-- Larry

In article <37264313...@born.com>,

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own