Re: Another angle on this....

"Brian Dick" <bdick_at_cox.net> wrote in message news:QdUc8.35343$nI1.177701_at_news1.wwck1.ri.home.com...
> "Heinz Kiosk" <no.spam_at_ntlworld.com> wrote in message
> news:3ZTc8.125540$H37.16031960_at_news2-win.server.ntlworld.com...
> > THING EXCEPT IN ORACLE (how rude to shout). Also not just SS but DB2,
> Sybase
> > et al.
>
> Actually, SS and Sybase do their own weird thing to an empty string. They
> convert it to a single blank character. But since trailing blanks are
> ignored in character comparisons, it usually doesn't matter.
Similar to the solution that I've hidden inside my lowest level drivers for Oracle, strip the space on read, and add it on write. Still biting me in various areas though, like when other apps write to my data I've had to let them know about this too.

There is an amusing (to me, maddening to others I guess) SS bug where although trailing spaces are ignored on searches, they aren't ignored on Group By which can give rise to very weird behaviour, particularly if you try to push the result of a "group by" into another table using a selected insert where the grouped by field is becoming a primary key you get a constraint violation! It took me a while to work out what was going on. Some users had keyed in spaces on the end of some codes which weren't picked up as foreign key violations but didn't group with the non-space-terminated ones. So on SS I trim strings before writing.....and on Oracle I add a space if it's blank. Such is life.

> Also, database
> drivers often trim strings before returning them to the client. Again this
> makes the single blank innocuous.
>
> I'm with you. An empty string is a discrete value. It is not a null. It is
> analogous to the numeric value of 0.

Yep, a point I think I made to Daniel.
>
> But I'm sure this Oracle implementation anomaly is buried deep within the
> database engine. We just have to live with it.
They can't change it now with existing types, too many currently working apps would break, but did they miss a trick when they brought in varchar2?
>
> Later,
> BEDick
>
>
>
Received on Wed Feb 20 2002 - 16:51:52 CST