Subject RE: Case insensitive search on a memo field
Author Maya Opperman

Thank you for all the suggestions


>hopefully, we’re not talking about more than a few million rows in this table?…


I’d say definitely not more than a million, probably around 15000 for the products tables, 5000 for the customer tables, and 100 000 for the jobs and orders tables.


But, each memo could be 5 pages long (or more, theoretically).


So, while the insensitive search is quite quick, even though it doesn’t use an index, if I have to convert each and every line of all the memos to lower or upper case, it could make the existing search slow.


PS. I’m using LIKE ‘find me%’ or like ‘%find me%’ inside a stored procedure, depending on whether the end user ticks “starting with” or “containing text” in my software, so it’s not easy to use containing and changing the middle of the SQL, hence I just use LIKE, even though it can’t use an index.