Databases and case sensitive searches

tkilshaw

Could the Aware IM support team please clarify the issues of case sensitive searches and databases.

Other posts seem to suggest that if you use Derby all searches will be case sensitive by default but if you use MySQL, searches will be case insensitive by default.

This is an important issue, I think.

Could you please indicate what will be the case if I use Derby, MySQL, MSDE, or MS SQL server?

thanks,

Terry

aware_support

Terry,

The following applies to database searches:

1) Derby searches are case sensitive. This is buit into Derby and unfortunately cannot be changed for now.
2) MySQL is case insensitive by default. There are ways to force MySQL to be case sensitive at the database level but Aware IM does not support these. So in Aware IM MySQL is always case insensitive.
3) MS SQL Server (and MSDE as far as I know) is case insensitive by default. You can also install MS SQL Server to be case sensitive. Aware IM will support whatever current installation setting is.

Best Regards,

autonomy2

Hello Aware Support.

Since this issue was last replied to in February regarding Derby being case sensitive for searches, has there been any changes to this?

If not, would the following be a possible work around: The Aware IM configurator would need to add an addtional rule or expand the query to account for case sentitivity.

For example; If a user searches for 'autonomy2', Aware IM could also include 'Autonomy2' in the query.

This issue needs to be addressed before our host sets up our dedicated server and if we should go with Mysql.

I like the fact that Derby is built in and apparently requires no administration.

aware_support

Hello autonomy2,

no there has been no changes to this since February. Derby is case sensitive and this cannot be changed at the database level, unfortunately.

You are right, the work around is to modify your queries - for example, use TO_LOWER_CASE function on both sides of the expression:

FIND Account WHERE (TO_LOWER_CASE (Account.Name) = 'autonomy2')

This query will ignore the case of the Account.Name.

Best Regards

autonomy2

Hello Support.

How would one configure a query rule to account for and include title case searches?

For example;

AUTONOMY2 (TO_UPPER_CASE)
autonomy2(TO_LOWER_CASE)
Autonomy2 ?

😃

aware_support2

If you want to ignore case in the search parameter, as well as the database values, you can do it like this:

FIND Account WHERE TO_LOWER_CASE (Account.Name) = TO_LOWER_CASE ('Autonomy2')

or a more useful version where the user can enter the desired search parameter at run time:

FIND Account WHERE TO_LOWER_CASE (Account.Name) = TO_LOWER_CASE (?'Account Name')

This will ensure that both the search parameter and the database value are converted to lower case before comparison is done.

greg

I tried to use this approach with the REPLACE_PATTERN function:

FIND Product WHERE Product.CatNoShort CONTAINS REPLACE_PATTERN(?'Catalog No.','[ .-]','')

Unfortunately it did not work. REPLACE_PATTERN did not modify an entered search string but transferred it directly to FIND. Am I doing something wrong or is it a limitation of the system? Thank you.