Home » Server Options » Text & interMedia » Need to get the one specific record with 's (oracle 11.2.0.4)
Need to get the one specific record with 's [message #667005] Tue, 05 December 2017 01:42 Go to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
On : 11.2.0.4 version, RDBMS

create table test1(id number,names varchar2(1000));
create index idx_names on test1(names) indextype is ctxsys.context;
insert into test1 values(1,'test record');
insert into test1 values(2,'raj''s record');
insert into test1 values(3,'raj record');
insert into test1 values(4,'raj ecord');
insert into test1 values(5,'raj');

commit;

after inserting record i am running the below block.
begin
ctx_ddl.sync_index('idx_names');
end;
/

select * from test1 where contains(names,'raj''s',1)>0

i am getting the all 2,3,4,5 records ..actually i need to get only 2 record.
How can i achive this one.?

Regards,
Rajesh

Report message to a moderator

Re: Need to get the one specific record with 's [message #667007 is a reply to message #667005] Tue, 05 December 2017 02:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
Senior Member
By default, the apostrophe is not indexed and separates tokens. If you want to include the apostrophe in the index, so that you can search for it, then you need to create a lexer and set the apostrophe as a printjoin, then use that lexer in the index creation. Please see the demonstration below.

SCOTT@orcl_12.1.0.2.0> create table test1(id number,names varchar2(1000))
  2  /

Table created.

-- create a lexer and set the apostrophe as a printjoin:
SCOTT@orcl_12.1.0.2.0> begin
  2    ctx_ddl.create_preference ('test_lex', 'basic_lexer');
  3    ctx_ddl.set_attribute ('test_lex', 'printjoins', '''');
  4  end;
  5  /

PL/SQL procedure successfully completed.

-- use the lexer in the index parameters:
SCOTT@orcl_12.1.0.2.0> create index idx_names on test1(names) indextype is ctxsys.context
  2    parameters ('lexer test_lex')
  3  /

Index created.


SCOTT@orcl_12.1.0.2.0> insert all
  2    into test1 values(1,'test record')
  3    into test1 values(2,'raj''s record')
  4    into test1 values(3,'raj record')
  5    into test1 values(4,'raj ecord')
  6    into test1 values(5,'raj')
  7  select * from dual
  8  /

5 rows created.

SCOTT@orcl_12.1.0.2.0> commit
  2  /

Commit complete.

SCOTT@orcl_12.1.0.2.0> begin
  2    ctx_ddl.sync_index('idx_names');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> column names format a30
SCOTT@orcl_12.1.0.2.0> select * from test1 where contains(names,'raj''s',1)>0
  2  /

        ID NAMES
---------- ------------------------------
         2 raj's record

1 row selected.

Report message to a moderator

Re: Need to get the one specific record with 's [message #667016 is a reply to message #667007] Tue, 05 December 2017 04:36 Go to previous messageGo to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
Thanks Barbara Boehmer ,

I need to add one more in list

insert into test1 values(6,'raj"s' record');
begin
ctx_ddl.sync_index('idx_names');
end;
/

when i queried
select * from test1 where contains(names,'raj"s',1)>0

I am getting 3,4,5,6 records ..i need to get only 6 record in output.
So how can i add two preferences in the same ctx_ddl.set_attribute.Please help in this scenario

Regards,
rajesh

Report message to a moderator

Re: Need to get the one specific record with 's [message #667038 is a reply to message #667016] Tue, 05 December 2017 10:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
Senior Member
List the printjoin characters one after the other without any separation.

ctx_ddl.set_attribute ('test_lex', 'printjoins', '''"');

SCOTT@orcl_12.1.0.2.0> create table test1(id number,names varchar2(1000))
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> -- create a lexer and set the apostrophe as  printjoin:
SCOTT@orcl_12.1.0.2.0> begin
  2    ctx_ddl.create_preference ('test_lex', 'basic_lexer');
  3    ctx_ddl.set_attribute ('test_lex', 'printjoins', '''"');
  4  end;
  5  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> -- use the lexer in the index parameters:
SCOTT@orcl_12.1.0.2.0> create index idx_names on test1(names) indextype is ctxsys.context
  2    parameters ('lexer test_lex')
  3  /

Index created.

SCOTT@orcl_12.1.0.2.0> insert all
  2    into test1 values(1,'test record')
  3    into test1 values(2,'raj''s record')
  4    into test1 values(3,'raj record')
  5    into test1 values(4,'raj ecord')
  6    into test1 values(5,'raj')
  7    into test1 values(6,'raj"s record')
  8  select * from dual
  9  /

6 rows created.

SCOTT@orcl_12.1.0.2.0> commit
  2  /

Commit complete.

SCOTT@orcl_12.1.0.2.0> begin
  2    ctx_ddl.sync_index('idx_names');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> column names format a30
SCOTT@orcl_12.1.0.2.0> select * from test1 where contains(names,'raj''s',1)>0
  2  /

        ID NAMES
---------- ------------------------------
         2 raj's record

1 row selected.

SCOTT@orcl_12.1.0.2.0> select * from test1 where contains(names,'raj"s',1)>0
  2  /

        ID NAMES
---------- ------------------------------
         6 raj"s record

1 row selected.

Report message to a moderator

Re: Need to get the one specific record with 's [message #667093 is a reply to message #667038] Thu, 07 December 2017 01:48 Go to previous message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
Thanks Barbara

Report message to a moderator

Previous Topic: where to check context index sync status
Next Topic: How to escape reserved words returned in column values as parameters to CONTAINS function
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Sep 08 10:18:26 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.