CODEWITHSUNDEEP
sqloracle-database
Jap Evans
Jap Evans

Reputation: 1127

How to search for a whole word with special char using oracle contains?

I have a column short_desc in my table that is context indexed.

When i search like below

select * from table1 where CONTAINS (short_desc,'{product\_id}') > 0

I get results like

abc product_id

abc product id

The escaped character behaves like an OR and the query searches for 'product_id' or 'product id'

How can i search for the whole word 'product_id' only?

Thanks in advance

Upvotes: 2

Views: 952

Answers (2)

ThinkJet
ThinkJet

Reputation: 6735

According to Oracle*Text documentation non-alphabetic characters are treated as whitespace (so product$id and product_id both treated as 'product id').

This behavior demonstrated at this SQLFiddle. Don't matter which non-aplhanumeric symbol placed between 'product' and 'id'.

To alter that you must define underscore as printjoin in lexer used by CONTEXT index.

I can't demonstrate this in SQLFiddle because access to ctx_ddl package restricted here, but this code must do the job:

create table table1(short_desc varchar2(200))
/

-- Add lexer definition and set '_' as "printjoin"
begin
  ctx_ddl.create_preference('mylex', 'BASIC_LEXER');
  ctx_ddl.set_attribute('mylex', 'printjoins', '_');
end;
/

-- Specify new lexer definition while creating index
create index table1_ctx_index on table12(short_desc) 
  indextype is ctxsys.context 
  parameters ( 'LEXER mylex' )
/

insert into table1(short_desc) values('1 product id 2')
/
insert into table1(short_desc) values('3 product_id 4')
/
insert into table1(short_desc) values('5 product#id 6')
/
insert into table1(short_desc) values('7 productXid 8')
/
insert into table1(short_desc) values('9 product-id 10')
/

alter index table1_ctx_index rebuild
/

Upvotes: 2

Harshit
Harshit

Reputation: 560

can try this

 select * from table1 where CONTAINS (short_desc,'{product'||'\_id}') > 0

Upvotes: 0

Related Questions