"Fowler, Jeff" <[EMAIL PROTECTED]> wrote:
> I'm curious to know how many of you agree with Darren's
> sentiments on this issue....
Changing the behavior of SQLite to ignore trailing
spaces is not an option for SQLite version 3, since
to do so would result in a incompatible file format
All indices created before the change would be invalid
since they would use a different collation. There
are multiple thousands of SQLite applications and
hundreds of millions of existing SQLite database files
that depend on this backwards compatibility. To make
this change would therefore require bumping the version
number up to SQLite 4.0.
>
> Our app creates SQLite tables dynamically based on the output
> from user-defined queries that run against data warehouses
> (of practically any "flavor") we have no control over, and
> we insert the results into SQLite. Sure - we can handle this
> situation by writing more code looking for spaces everywhere
> they might occur. But to me (and maybe only to me?), it makes
> sense for SQLite -- where reasonably possible -- to attempt
> to follow clear ANSI guidelines....
>
Check-in [4732] implements a built-in RTRIM collating sequence
that provides the ignore-spaces comparison semantics that you
desire. All you have to do is add "COLLATE RTRIM" to the declarations
of text columns in your SQLite schema and SQLite will thereafter
ignore trailing spaces on comparisons involving those columns.
If you do SQL comparisons that do not involve columns, you can
put "COLLATE RTRIM" after the comparison operator itself to get
this behavior. Example (an actual screen capture):
[EMAIL PROTECTED]:~/sqlite/bld> ./sqlite3
SQLite version 3.5.4
Enter ".help" for instructions
sqlite> SELECT 'abc'='abc ';
0
sqlite> SELECT 'abc'='abc ' COLLATE RTRIM;
1
--
D. Richard Hipp <[EMAIL PROTECTED]>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
