We can't search on a field using a "contains" search

Question

I want to search for some text inside a field. This works great on other tables but it seems to work inconsistently.

I can search a 160,000 record table using the following SQL and it returns 35 records.

select * from MyTable where comments like '%doc%'

But in dbFront, it only returns 1 record. It looks like it can only find the text at the beginning of a field.

NOTE: I am not using the Full-Text search functionality.

Answer 1

The reason is that the table has more then 100,000 records. Regular LIKE searches that include '%' before and after the search term can't use database indexes and so they can cause significant performance issues.

To guard against that, dbFront will automatically revert to only a single following '%', which will allow the database server to use indexes.

• Users can control this by manually surrounding the text with '%' themselves.
• As an administrator you can also change the breakpoint from 100,000 to something else.
• Or you can implement Full Text Search at the server level.

For more details please see: Using Default Wildcard Searches