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

Add additional upper or lower case column in your select statement. Example:

select col1, upper(col1) upp_col1 from table1 order by upp_col1

Answer 2

Try creating a functional index, like

Create Index INDX_MY_SEARCH on TABLE_NAME(LOWER(@MySearch)

Answer 3

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.