"Better" depends on who you ask - I'd say it's worse, and I bet most DBA's would agree. The ANSI standard is to ignore trailing spaces when comparing character strings in a WHERE clause, a HAVING clause, or a join. So I can take the exact same data, run the exact same query, yet get a different answer from SQLite vs. Oracle or SQL Server. In fact, we found this issue because we DID get a different answer.
Regarding whether by extension it should be impossible to create strings with trailing spaces; I side with the SQLite developers who say it isn't the engine's job to trim blanks in data. Most other engines I've used do not trim spaces either, even if the field is a varchar. But - whether ANSI compliance is considered "bloat" is not really my place to comment upon. I guess it's really is up to the SQLite team. Purely from a business usability standpoint (not a programming one), I would say there's no question that it's far more useful to do comparisons the ANSI way. If for some reason I truly want to compare & respect trailing spaces, I can still do that using a function such as HEX(A) = HEX(B) or something better. - Jeff Fowler -----Original Message----- From: Darren Duncan [mailto:[EMAIL PROTECTED] Sent: Friday, January 18, 2008 3:33 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] SQLite character comparisons At 10:43 AM -0500 1/17/08, Fowler, Jeff wrote: >Hello All, >I've used SQL Server for over 15 years, Oracle off & on when I have no >choice, but SQLite for a couple weeks. I've just learned (today) that >SQLite respects trailing spaces when comparing two character fields. >I.e. 'SQLITE' <> 'SQLITE ' >Is this behavior intentional? Neither SQL Server nor Oracle do this. >Just curious as to why it works this way. Because respecting the actual contents of the string is the better way to do things. The strings 'SQLITE' and 'SQLITE ' are not the same string. Just as the strings 'SQLITE' and 'sqlite' are not the same string. A computer language is more logical, predictable, and easy to use when a test for equality or inequality actually treats every distinct value as distinct. If trailing spaces were supposed to be insignificant for an equality test, then it should not be possible to define a string value containing trailing spaces at all. Moreover, treating 'SQLITE' and 'SQLITE ' as not being the same string also is consistent with the SQLite philosophy, because it means SQLite has fewer exceptions to be concerned with in a simpler set of rules, and also not having to check lengths and space pad before each compare also makes the code simpler, and less buggy, and it saves CPU cycles. A value equality test is a very common and fundamental thing to do in a DBMS, and bloating that will have a big performance impact. -- Darren Duncan ------------------------------------------------------------------------ ----- To unsubscribe, send email to [EMAIL PROTECTED] ------------------------------------------------------------------------ ----- ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
