In a message dated 98-09-02 23:16:52 EDT, you write:
We need to search text fields containing mixed upper and lower case long
descriptive information. The database entries are also inconsistent so there is no guarantee that upper case letters appear only at the beginning of a word.
We would like to be able to do a non case-sensitive search effectively BUT at the same time retain how the descriptions were originally keyed in for our reports.
Apart from specifying all possible combinations of upper and lower case for the search, is there anything that will work like an anycase ?
For this example I will assume that the field being searched is a customer name.
There is an interesting trick to this. You could, for example, create an object that uses your database UPPER() command (if available) to force all of the data in the table to be upper case. That would allow your users to search with confidence, knowing that all of the search conditions should be in upper case. This object could be called Search Name. It would be valid as a condition, but not as a result object. You can control these settings when the object is set up in the Designer application.
Next, create an object called Customer Name that can be a result object but cannot be a search object. This object would not use the UPPER() function, and would be used for display purposes on the report.
This should work, but will have a side effect of ruining any chance of using a database index on the customer name column. (If the field in question is not indexed, then this is not a concern.) If the field is indexed,however, then you will lose that performance benefit. To solve that problem, you should go another way.
Instead of using just a search based on the UPPER version of the customer name, it turns out that the following set of conditions are often enough to use the index and search for matches based on the upper case version. If you are searching for a customer named “Brown” then it goes something like this:
(customer_name like 'Br%' OR
customer_name like 'br%' OR
customer_name like 'bR%' OR
customer_name like 'BR%') AND
upper(customer_name) = 'BROWN'
It turns out that specifying all possible case options for the first two letters is enough to narrow down the search using an index, while the last item checks for the exact match. This is, however, not very user friendly! You would not want to train your users to build conditions in this way!
So, the final solution: build an object that picks apart the user’s criteria and builds the condition for them. You did not specify which database you are using, so I’ll provide an example using Oracle. Because it’s the easiest.
The predefined condition would be called ‘Customer Name Search’ and the where clause could be something like:
(
customers.customer_name like
upper(substr(@prompt('Enter customer name','A',,mono,free),1,1) || lower(substr(@prompt('Enter customer name','A',,mono,free),2,1) || '%' OR customers.customer_name like
lower(substr(@prompt('Enter customer name','A',,mono,free),1,1) || lower(substr(@prompt('Enter customer name','A',,mono,free),2,1) || '%' OR customers.customer_name like
lower(substr(@prompt('Enter customer name','A',,mono,free),1,1) || upper(substr(@prompt('Enter customer name','A',,mono,free),2,1) || '%' OR customers.customer_name like
upper(substr(@prompt('Enter customer name','A',,mono,free),1,1) || upper(substr(@prompt('Enter customer name','A',,mono,free),2,1) || '%' ) AND upper(customers.customer_name) =
upper(@prompt('Enter customer name','A',,mono,free))
In this example, I am not using a list of values for customer names. It probably is not necessary, as the user is simply expected to type in the name. If you are going to use the list of values feature, then this trick is not required as you will have an exact match. Note: be very careful with the parenthesis! You have to have the four “OR” conditions inside parenthesis, and the AND condition outside.
(test 1 OR test 2 OR test 3 OR test 4) AND test 5
Recall that if you use the same prompt over and over, then BusObj will only prompt the user one time, ensuring that the same value is used throughout the logic for the entire condition. As I said, this object should work for Oracle; as long as your database has an equivalend Sub String, Upper, and Lower function then you should be able to convert this idea to work.
Hope this helps!
Regards,
Dave Rathbun
Integra Solutions
www.islink.com
PS - As a side note… thanks to everyone that attended the user conference that came up and introduced themselves. It is always great to put a face with the names on the list!
Listserv Archives (BOB member since 2002-06-25)
(BOB member since 2002-06-20)