Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle Text Index - Trailing Search when the String has spaces

user13735210Aug 26 2014 — edited Sep 4 2014

Hi Am trying to use CONTAINS to tune a trailing LIKE search.  But it's not working when the string has spaces in between them

For e.g in say if table person has column name which has following values

JOHN

FRED JOHNSON

JOHN ROBERTS

select * from person where name like 'JOHN%'

Above query will give JOHN and JOHN ROBERTS. If i use CONTAINS As below

select * from person where CONTAINS(name,'JOHN%',1) > 0

It brings back all three records. If i remove spaces in column itself, its working bit if i try to remove spaces in Query, i am getting a Text error as i have Text index only on the name column.  I would like to know is it possible to use CONTAINS but get same result set on trailing search as how a normal LIKE will do.

Below is how i did Index creation

create index txt_idx1 on person (name)

indextype is ctxsys.context

parameters('DATASTORE ctxsys.default_datastore');

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 2 2014
Added on Aug 26 2014
3 comments
1,747 views