Hi All.
I have a problem with Case-Sensitive search.The scenario is as below.
Let say i have a Customer with name = James
and when the user wants to search for that customer
he gives the name as james by ejb (CMP) dosen't find it.
Is there any way I can turn off case sensitive search.I am using EJB1.0.
I thought of using BMP but the problem there is have to use Oracle specific toUpper function to convert the names in the Database to all upper cases before I do a search.

Our application must work with both SQLServer and Oracle servers.Any one has done this case insensitive search in a
generic way?

Any suggestions?
Thanks In Advance

I am not sure about Oracle and SQL servers, but other SQL servers I worked with (mySQL, PostgreSQL) perform insensitive match when you use LIKE SQL directive for strings... For example:

select name from main where name like 'james'

will find all guys with name James without taking case into consideration.

Thanks Alex,
But Oracle dosent work that way.Even in Like statements it is case sensitive
Thanks

Even if you did use the BMP approach and use the db-specific functions, you may run into performance problems.

How large might the table in question be? In Oracle, a common approach is along the lines you mention, in which you convert both the database column value and the search value to uppercase. The problem is that this operation normally disables any indexes on the column, which will kill your performance.

I might recommend creating a view around your table-- create an all uppercase column in the view that uses the db-specific function. Both SQLServer and Oracle support views. Then uppercase your search parameter, and use the view instead of the table when you implment your finder method. Based on limits I know of, this will of course mean you'll need to use BMP.

As a side comment, if you are really worried about performance, a very common trick would be to create a table with a name and UPPER_name column, and then write a trigger that keeps UPPER_name equal to UPPER(name) on inserts and updates. Then you can create indexes on that column, etc, and you'll get great performance when you search on it. Both SQLServer and ORACLE support this approach too.

HTH
Dave