EMPTY STRING IS NULL... HOW ???

Sagheer Ahmed

unread,

May 18, 1998, 3:00:00 AM5/18/98

to

Does any one know how I can by default treat empty strings as nulls. I
dont wanna do this in trigger or proc. I am using SQLANYWHERE 5.5.03 Build
#1666. Data comes from diferent sources into the database. Is there any
database option I can use.

SA

Jim Egan

unread,

May 19, 1998, 3:00:00 AM5/19/98

to

Why don't you want to do this in a trigger? And why do you want to treat
empty strings as NULL?
--
Jim Egan [TeamPS]
Dealer Solutions, LLC
Houston, TX

Sagheer Ahmed

unread,

May 19, 1998, 3:00:00 AM5/19/98

to

Hi
Thanks for your response.

The reason I want to treat empty strings as null for incoming data is that
querying data with where column contains an emty string OR is null is
slower then when you only look for nulls.

Sagheer

Jim Egan <ega...@compuserve.com> wrote in article
<01bd8321$c31785a0$a301a8c0@jegan>...

Darrell Guard

unread,

May 19, 1998, 3:00:00 AM5/19/98

to

Hello,

Why not create a function that will perform this for you:

CREATE FUNCTION MakeNullString(@STRING char(<max length anticipated>))
RETURNS char(<max length anticipated>)
BEGIN
declare @RETURN char(<max length anticipated>);
if @STRING is null or length(@STRING) = 0 then
SET @RETURN = null
else
set @RETURN = @STRING
end if;
return(@RETURN);
END

and perform:

UPDATE TABLE SET COL1 = MakeNullString( '' ); -- Set COL = NULL
UPDATE TABLE SET COL1 = MakeNullString( null ); -- Set COL = NULL
UPDATE TABLE SET COL1 = MakeNullString( 'test' ); -- Set COL = 'test'

The above is from memory (i.e. syntax errors may exist). Hope this helps.

Jim Egan

unread,

May 19, 1998, 3:00:00 AM5/19/98

to

Well, you are correct to say that having to check for a blank string OR a
NULL is time consuming. I usually go the other way and force a non NULL
value. Columns that allow nulls are a bit less efficient (in storage for
sure, perhaps in performance also) than columns that do not allow nulls.

Jason Hinsperger

unread,

May 20, 1998, 3:00:00 AM5/20/98

to

Jim,
You negate any gain in storage space by forcing a non-null value into a
column.
Allowing NULLs is actually MORE efficient, not less.
Let me explain. With each row there is enough space left in the header to
mark nullable columns as nullable. At one bit per column, that is pretty
efficient use of storage (8 columns per byte). Whenever a column is NULL,
the bit is set and all other columns are shifted over. No space is left in
the row for that column. If it does receive data in the future, the row is
grown as necessary.
On the other hand, by forcing a value that signifies 'NULL', you waste at
least 2 bytes for each column (4 bytes for integers and date/time values)
I am not saying forcing non-NULL values is wrong, just that your reasoning is
flawed.
Just my 2 cents,

Jason Hinsperger
Product Quality Engineering
Sybase SQL Anywhere

Jim Egan

unread,

May 20, 1998, 3:00:00 AM5/20/98

to

Okay, Okay, I wasn't thinking. I was aware of how things work, I just
didn't write down very well what I was thinking. Either that or I had a
temporary lapse of sanity. <g>

I'll change my argument somewhat. It takes more to query against a column
that allows NULL (since you have to check for the absence of a value or if
it is NULL -> col1 <> '1' or col1 IS NULL) than one that doesn't allow

Sam Maxwell

unread,

May 21, 1998, 3:00:00 AM5/21/98

to

In a non-trivial database or in one with a high transaction volume I believe
that approach would be unacceptably inefficient.

Darrell Guard wrote in message <3561C0...@netcom.ca>...