Home » Server Options » Text & interMedia » Wildcard Seraches Using CONTAINS (Oracle 9.2)
Wildcard Seraches Using CONTAINS [message #442827] Wed, 10 February 2010 20:26
Messages: 8
Registered: December 2009
Location: BOSTON
Junior Member
i have an application in which the users give the search terms in a user interface and the application has to search those against the oracle table using the text index

the user can enter any number of search terms separated by semi-colon (Wink

so if the user enters:

bernie and phyl;jordans

the application converts that to a where clause :

contains ( concat,'{bernie and phyl} OR {jordans}',1 ) > 0 

the reason i put the user eneted search terms in { } brackets is - the users search terms can have some terms that are 'reserved words' in context syntax (such as 'and' above) and we are not expecting them to mean anything other than plain serach terms ...

question i have is - to allow wild-carding in search queries we want the user to be able to specify the % operator, that is they should be able to eneter something like :

bernie and phyl%;jordans

as per my usual logic this search term got converted to :

contains ( concat,'{bernie and phyl%} OR {jordans}',1 ) > 0 

that does not do the job - i kinda understand taht a % inside the { } is probably just ignored

so i tried this :

contains ( concat,'{bernie and phyl}% OR {jordans}',1 ) > 0 

that does not work also - it give me too may terms error

what does work is

contains ( concat,'{bernie and} phyl% OR {jordans}',1 ) > 0 

but that makes my life much harder as to convert

bernie and phyl%


{bernie and} phyl%

i'll have to write a bit of messy code to look for words that have a % and take them out of the brackets ...

i am wondering if i am missing something basic and can get away without writing that code

any help would be greatly appreciated ...

Previous Topic: Oracle text concatenated datastore
Next Topic: Text Index Privileges
Goto Forum:

Current Time: Sat Mar 25 14:49:36 CDT 2023