Oracle Text Search

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Oracle Text Search

Postby ponic » Thu Oct 10, 2013 3:36 pm

Hi Tim

I am trying to follow your article about Text search
http://www.oracle-base.com/articles/9i/ ... CATIndexes

I have tried the following and it did work.

Code: Select all
SELECT id, price, name
FROM   my_items
WHERE  CATSEARCH(description, 'Bike', 'price BETWEEN 1 AND 5')> 0;


Now my test case

With the following plain sql where condition I could get results

Code: Select all
    SELECT id, price, name,description
FROM   my_items where lower(description) like lower('%bi%')



How can I achieve the same results when Oracle Text search is used? The following does not return any rows.

Code: Select all
    SELECT id, price, name,description
FROM   my_items
WHERE  CATSEARCH(description, 'bi', 'price BETWEEN 1 AND 1000')> 0;
ponic
Senior Member
 
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Oracle Text Search

Postby Tim... » Thu Oct 10, 2013 7:55 pm

Hi.

You mean this tight?

Code: Select all
SELECT id, price, name,description
FROM   my_items
WHERE  CATSEARCH(description, '*bi*', 'price BETWEEN 1 AND 1000')> 0;


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17940
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Oracle Text Search

Postby ponic » Fri Oct 11, 2013 2:49 am

Oh Yes, thanks Tim
ponic
Senior Member
 
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Oracle Text Search

Postby Tim... » Fri Oct 11, 2013 7:42 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17940
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 1 guest

cron