Sqlite + .NET 4.0. How quickly case-insensitive search not only latin characters

Question

0.00/5 (No votes)

See more:

Hello, how I can can organize insensitive search using SQLite ? It is necessary to set up non-fulltext search so I can search for parts of words. Now the program is based on System.Data.SQLite.dll. It use Like operator and operator override Lower (), because standard function can not work with non-latin characters, received a request "select * from myTable where Lower (myColumn) Like %text%". It works fine, but the search takes a long time and on the basis of a total of approximately 20-50 thousand strings can be searched 5 seconds. Tell me how you can still set up a case-insensitive search on SQLite to shorten the time of the request? Or maybe someone will share the collected working library with support for the ICU?

What I have tried:

I find an article that is written about the ICU, I need to use the SQLite library for on normal non_latin characters to search built-in ICU. On the official website I found the default Sqlite3.dll library but it also does not support the non-Latin characters. I found a bunch of SQLite3.dll libraries with support it with built the ICU, but no one could not connect when you try to access it hangs the application.

Posted 5-Nov-16 3:42am

Member 12561704

Updated 5-Nov-16 4:10am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Midi_Mick · Answer 1 · 2016-11-05T04:10:00

What I would suggest for your case would be to create a case-insensitive index on your search field, and then force the select to use that index in its search.

SQL

CREATE INDEX iCaseInsensitive ON myTable(myColumn COLLATE NOCASE);
...
SELECT * FROM myTable INDEXED BY iCaseInsensitive WHERE myColumn <expr>;

Note that with this system, if you are putting conditions on other columns, you will either have to add those columns to the index, or use a JOIN.
Another possibility I just thought of, if the COLLATE NOCASE does not work with your character set, create the index from the column already converted to lower case

SQL

CREATE INDEX iCaseInsensitive ON myTable(Lower(myColumn));