And this is in BO so I can change the object in universe to be upper(column_name) but for search term…user can enter upper(column_name) MATCHES PATTERN LIKE %Nestle% or %nestle% or %NESTLE% or %nesTLE% but want it to be insensitive
There are 1000’s of records in database. Regardless of how I do wildcard search e.g.: …where ‘Name’ matches pattern LIKE %Nestle% and it should return NESTLE, nestle, Nestle, nesTLE…
The easiest way to do this is use a case-insensitive database. For example SQL Server has an option to make character fields case insensitive, as does Teradata. Have you checked to see if that works for you?
The next trick is to do something that works with enough of an index to perform well, without trying to cover every single possible scenario. It turns out that in most cases, it’s enough to check only the first two characters for wildcard matches and then use upper case to do the exact match.
Something like this:
(table.col like 'Ne%'
or table.col like 'nE%'
or table.col like 'NE%'
or table.col like 'ne%')
and upper(table.col) = 'NESTLE'
To be clear, however, the user doesn’t enter a wildcard, they have to enter a discrete value like “NESTLE” for this to work. They can enter it in any combination of upper and lower case letters, but the user does not enter a wildcard character. And to try to do it with leading % as well as trailing % would result in a very long-running query because no index would be possible at all.
The final trick, if this is something that needs to be done regularly but on a limited subset of fields, is to maintain to different columns in the database. One is called NAME and the other is called NAME_UPPER. Every time a row is inserted, the name field is stored as entered and then converted to all upper case and stored in the secondary field NAME_UPPER. Build your index on that column, and build your prompts on that column, and you have a third option.
Thanks Dave for the reply.
The issue is the original data in the database should not be modified(system of record). And there are more than 20000 records and I’ve given a sample of Nestle but the complete names could be: Nestle foods, Nestle water, Nestle baby food,… in other words in not possible to know combinations of the names.
And you said: ‘The easiest way to do this is use a case-insensitive database. For example SQL Server has an option to make character fields case insensitive, as does Teradata.’ We have Oracle database, so not sure if that feature exists.
That forces everything to be uppercase on both sides. However, that will often invalidate an index and it requires the prompt to be a single select, as you can’t apply a function to a multi-select prompt.
Thanks Venkat.
It’s for ad-hoc reports and there are abt 200 objects. User would like to see the original case when using InList but when using matches pattern, wants to search by the keyword(insensitive).
Situation (for me at least): Have a non-optional @prompt with a multi LOV (in other words, they can select multiple values). These values are all upper case, however my customers tend to manually enter values in mixed or lower case.
Lower case has been resolved, however I cannot resolved mixed case. Bottom line - as described by @DaveRathbun and others above - is that wrapping the SQL prompt itself lower or upper will result in an error if you are returned more than one value.
As discussed above, this appears to be an Oracle limitation when you are using InList. I tried all the above solutions - including venkatdss’s one - however I always got the error message when trying with multiple values selected InList.
(BOB member since 2002-06-06)
(BOB member since 2003-11-27)