Home » Server Options » Text & interMedia » how to ignore particular string or character in contains function (oracle ,12c)
how to ignore particular string or character in contains function [message #677322] Thu, 12 September 2019 02:50 Go to next message
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

Hi team, could you please tell how to ignore particular string or character using "contains" function.
If possible please provide one working example as well.
Re: how to ignore particular string or character in contains function [message #677337 is a reply to message #677322] Thu, 12 September 2019 08:10 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: how to ignore particular string or character in contains function [message #677344 is a reply to message #677322] Thu, 12 September 2019 11:12 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You can do this by creating a stoplist, then adding what you want to be ignored to the stoplist, then using that stoplist in the parameters of your context index, as demonstrated below.

-- test environment:
SCOTT@orcl_12.1.0.2.0> SELECT banner FROM v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

5 rows selected.

SCOTT@orcl_12.1.0.2.0> CREATE TABLE customer_details (complete_address	VARCHAR2(30))
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> INSERT INTO customer_details (complete_address) VALUES ('560 TRUMPELL LANE æøå')
  2  /

1 row created.

SCOTT@orcl_12.1.0.2.0> INSERT INTO customer_details (complete_address) VALUES ('314 SOMEWHERE ST æøå')
  2  /

1 row created.

-- create a stoplist:
SCOTT@orcl_12.1.0.2.0> EXEC CTX_DDL.CREATE_STOPLIST ('ignore_list')

PL/SQL procedure successfully completed.

-- add a stopword to the stoplist:
SCOTT@orcl_12.1.0.2.0> EXEC CTX_DDL.ADD_STOPWORD ('ignore_list', '314')

PL/SQL procedure successfully completed.

-- create a context index using the stoplist in the paramters:
SCOTT@orcl_12.1.0.2.0> CREATE INDEX customer_details_idx ON customer_details (complete_address) INDEXTYPE IS CTXSYS.CONTEXT
  2    PARAMETERS ('STOPLIST ignore_list')
  3  /

Index created.

-- what is tokenized and indexed:
SCOTT@orcl_12.1.0.2.0> SELECT token_text FROM dr$customer_details_idx$i
  2  /

TOKEN_TEXT
----------------------------------------------------------------
560
LANE
SOMEWHERE
ST
TRUMPELL
Ã

6 rows selected.

-- query test:
SCOTT@orcl_12.1.0.2.0> VARIABLE search_criteria VARCHAR2(30)
SCOTT@orcl_12.1.0.2.0> EXEC :search_criteria := '314'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> Select * from customer_details where contains (complete_address,:search_criteria)>0
  2  /

no rows selected

Previous Topic: Uncertainty while searching string which contains æøå characters
Next Topic: pixel (rgb) values extraction function
Goto Forum:
  


Current Time: Tue Mar 19 04:02:48 CDT 2024