On 08/05/2011 05:44 PM, Martin Gill wrote: > On 5 August 2011 10:55, Dan Kennedy<danielk1...@gmail.com> wrote: >> >> On 08/05/2011 02:58 PM, Martin Gill wrote: >>> >>> CREATE VIRTUAL TABLE full_text USING FTS3 (id,text) >>> INSERT INTO full_text VALUES ('1', 'I have a foréign character.') >> >> SQLite does not recognize upper/lower-case mappings for non-ASCII >> characters. That is why querying for "HAVE" works but "FORÉIGN" >> does not. If you need upper/lower case mapping for non-ASCII characters, >> you will need to use the ICU extension. >> > > That may be part of the problem. System.Data.SQlite isn't compiled with ICU, > so > I cannot check that quickly. I do though get exactly the same odd > behaviour using > the porter tokenizer. > >>> SELECT id, text FROM full_text WHERE NOT(text = @p1) AND text MATCH @p2 >>> >>> p1 = "foréign" >>> p2 = "FORÉIGN" >>> >>> Not using parametrized SQL also returns a correct result. >> >> I don't see how either the query above or a version that uses SQL >> literals could be returning any rows.. Not with the "CREATE VIRTUAL >> TABLE" as it stands above. Unless you specify the ICU tokenizer (or >> some other tokenizer that understands non-ASCII upper/lower case), >> "FORÉIGN" should not match the row in table "full_text". >>
> SQLiteParameter param1 = command.CreateParameter(); > param1.Value = "FORÉIGN"; > param1.ParameterName = "p1"; > param1.DbType = System.Data.DbType.String; > command.Parameters.Add(param1); > > SQLiteParameter param2 = command.CreateParameter(); > param2.Value = "foréign"; > param2.ParameterName = "p2"; > param2.DbType = System.Data.DbType.String; > command.Parameters.Add(param2); p2 is lower-case here, so it matches. If the upper-case value were bound to p2, it would not. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users