Case insensitive search on Sybase

Question

I have been sick and tired Googling the solution for doing case-insensitive search on Sybase ASE (Sybase data/column names are case sensitive). The Sybase documentation proudly says that there is only one way to do such search which is using the Upper and Lower functions, but the adage goes, it has performance problems. And believe me they are right, if your table has huge data the performance is so awkward you are never gonna use Upper and Lower again. My question to fellow developers is: how do you guys tackle this?

P.S. Don't advise to change the sort-order or move to any other Database please, in real world developers don't control the databases.

Answer 1

If you cannot change the sort-order on the database(best option), then the indexes on unknown case fields will not help. There is a way to do this and keep performance if the number of fields is manageable. You make an extra column MyFieldLower. You use a trigger to keep the field filled with a lower case of MyField.

Then the query is: WHERE MyFieldLower = LOWER(@MySearch)

This will use indexing.

Answer 2

Create an functional index.

Like Create Index INDX_MY_SEARCH on TABLE_NAME(LOWER(@MySearch)