Hi, Is it possible to filter a table case - insensitive for a given (entered by user) search name (text type)?
Yes, you can. Put an “@” in front of the filter text to make the search cas-insensitive. John
Thank you, John. It works great! I made some silly solution by checking: lowercase(searchName) or uppercase(searchName) or sentenceCase(searchName). But it does not cover every case-insensitive situation. Thanks again.
When performing a case-insensitive search on a case sensitive column, you are likely to experience performance problems as the database server might have to scan quite a large number of records to find the record(-s) matching your filter. If you intend to perform a lot of searches like that, particularly in large tables, you might want to create a “Search Name” column of type Code, index the column, and then search for the up-cased string in that column without using the @-operator. - Jens
Thank you again, John, Actually, I was filtering (not searching) a table of items by their description, where the first few letters will be written by the user. After pressing Enter, the table will appear, showing only items beggining with “Ser”, for example. So, there should be no performance problems. Thank you.
The @ operator also introduces accent-insensitivity, not just case-insensitivity. So all characters that are grouped by equal accents will be considered equal and match in the filter. Doesn’t matter if you only store ASCII range characters. In Navision Server this is determined by the chartable entry in the (server) stx file; in SQL Server it is determined by the collation.
Thank you all, John, Jens and Robert. You are very kind.