Hi,
I'm trying to add full text search (FTS3) to a small project I'm working on
with SQLite v3.6.11. When I search for matches, I'd like the results to be
alphabetical (case insensitive), but the FTS table is doing case-sensitive
sorting. For all other tables, I defined the columns as TEXT COLLATE NOCASE,
but I read that the data type, collation, and other column options aren't used
for FTS virtual tables. For example:
CREATE VIRTUAL TABLE ftsa using fts3(col1 text COLLATE NOCASE);
insert into ftsa (col1) values ('z');
insert into ftsa (col1) values ('Z');
insert into ftsa (col1) values ('a');
insert into ftsa (col1) values ('A');
select * from ftsa order by col1;
Returns:
A
Z
a
z
I found I could get it to work with:
select * from ftsa order by Lower(col1);
Returns:
a
A
z
Z
Is that the best way to do it? It seems inefficient to have to change the
capitalization on every item every time I query them.
I thought about keeping the text in a normal table, and just putting a copy in
the FTS virtual table (maybe also removing a, an, the, of, etc). Is that what
people normally do? ...maybe with triggers on the normal table to keep the
virtual table updated?
I'm using this for a code snippet database. The table needs to record ID,
Title, SourceCode, FileName, FileBLOB, LastUpdateDate, and I'd like to be able
to search on Title, SourceCode, and FileName. If it matters, I also need to
join to other tables based on the ID field--which seems to be challenging when
using MATCHES, but I found a comment showing how to do it by prefixing the join
on FTS field condition with a + sign.
Thanks,
Eric
--
Be Yourself @ mail.com!
Choose From 200+ Email Addresses
Get a Free Account at www.mail.com
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
