I was trying to get my app to do a case insensitive search of records containing ‘’ in the “name” field, but I stumbled upon what looks like to be a bug in sqlite…
As built for use in the sqlite plugin its not Unicode savvy so case insensitive searches outside the ASCII range do not work
see http://sqlite.org/datatype3.html#collation
The was Xojo builds the sqlite plugin it is not not unicode savvy - so case sensitive comparisons outside the Ascii range wont work
I think Thomas Tempelmann posted some extensions for sqlite that let you make it use unicode which would alleviate this
see his blog at http://blog.tempel.org/2015/09/xojosqliteextensions.html
If you incorporate his code I think you should be able to do case sensitive comparisons as you expect
Hey Peter… It’s a search (select where)… So if. I’d want to do it within xojo I guess I should first load all the database table records and then apply the filter… I don’t think it’s a good idea… I mean… can it be done? What if there are thousands of records?
There just isn’t a good way as things sit with sqlite the way it is built
You need unicode in sqlite but its not there
The “best” you could do, and something I have done, is flatten everything into ascii and store that data in another column
Then select from the db using that additional column to narrow things down a bit and then search in Xojo in the subset as it IS unicode aware
And, as you found, that is how you find sqlite built a lot of the time
I was thinking about forcing the ‘name’ input field to be uppercase… but that means messing up the code too much and it wouldn’t look nice… I was hoping there would be a simpler / better solution.
How you STORE things is up to you
Thats why I suggested a second column (or maybe many alternates) that are some “normalized” version of the data you want to search
So when you insert a name you insert both a mixed case AND uppercase only version
Then for a “case insensitive name search” you use the uppercased column
No one needs to be the wiser that you’ve had to work around it like this except you
Not sure how many records this table might have. It could eventually grow more than expected and then the app would get too slow retrieving all this records…
I think. It’s either forcing lower / uppercase… Or as Norman suggest, creating another column for this purpose.