Home » SQL & PL/SQL » SQL & PL/SQL » need two token matching query (oracle 11.2.0.4)
need two token matching query [message #667094] Thu, 07 December 2017 01:50 Go to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
create table test_names(id number,firstname varchar2(100),secondname varchar2(100),thirdname varchar2(100),fourthname varchar2(100),fifthname varchar2(100));
insert into test_names values (1,'rajesh','kumar',null,null,null);
insert into test_names values (2,'rajesh',null,'rajesh',null,null);
insert into test_names values (3,'rajesh kumar',null,'rajesh',null,null);
insert into test_names values (4,'rajesh',null,'rajesh kumar',null,'reddy');
commit;


QUESTION:

My input name is 'rajesh kumar sachin'

Now i need to matchatleast two strings in the names columns

ouput :1,3,4 records i need to get
How can i achive this in sql query

Regards,
rajesh
Re: need two token matching query [message #667104 is a reply to message #667094] Thu, 07 December 2017 08:12 Go to previous messageGo to next message
quirks
Messages: 82
Registered: October 2014
Member
I deeply believe that there must be a better solution. But as long as no one comes around with a better one you could try this one:
WITH
    TEST_NAMES(ID, FIRSTNAME, SECONDNAME, THIRDNAME, FOURTHNAME
              ,FIFTHNAME)
    AS
        (SELECT 1, 'rajesh', 'kumar', NULL, NULL, NULL
           FROM DUAL
         UNION ALL
         SELECT 2, 'rajesh', NULL, 'rajesh', NULL, NULL
           FROM DUAL
         UNION ALL
         SELECT 3, 'rajesh kumar', NULL, 'rajesh', NULL, NULL
           FROM DUAL
         UNION ALL
         SELECT 4, 'rajesh', NULL, 'rajesh kumar', NULL, 'reddy'
           FROM DUAL),
    PREPARE_NAMES
    AS
        (SELECT     DISTINCT ID, TRIM(REGEXP_SUBSTR(FIRSTNAME || ' ' || SECONDNAME || ' ' || THIRDNAME || ' ' || FOURTHNAME || ' ' || FIFTHNAME, '[^ ]+', 1, LEVEL)) AS NAMES
               FROM TEST_NAMES
         CONNECT BY REGEXP_SUBSTR(FIRSTNAME || ' ' || SECONDNAME || ' ' || THIRDNAME || ' ' || FOURTHNAME || ' ' || FIFTHNAME
                   ,'[^ ]+', 1, LEVEL)
                        IS NOT NULL
                AND PRIOR ID = ID
                AND PRIOR SYS_GUID() IS NOT NULL)
SELECT   ID
    FROM PREPARE_NAMES
   WHERE 'rajesh kumar sachin' LIKE '%' || PREPARE_NAMES.NAMES || '%'
GROUP BY ID
  HAVING COUNT(*) > 1
ORDER BY ID;
Re: need two token matching query [message #667105 is a reply to message #667094] Thu, 07 December 2017 11:18 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Bad design. Normalize table layout and search string too. Anyway:

with t as (
           select  id,
                   (
                    select  count(distinct column_value)
                      from  table(sys.OdciVarchar2List(firstname,secondname,thirdname,fourthname,fifthname))
                      where instr(' ' || '&search_tring' || ' ',' ' || column_value || ' ') > 0
                   ) cnt
             from  test_names
          )
select  id
  from  t
  where cnt > 1
/
Enter value for search_tring: rajesh kumar sachin
old   6:                       where instr(' ' || '&search_tring' || ' ',' ' || column_value || ' ') > 0
new   6:                       where instr(' ' || 'rajesh kumar sachin' || ' ',' ' || column_value || ' ') > 0

        ID
----------
         1
         3
         4

SQL> 

SY.
Re: need two token matching query [message #667106 is a reply to message #667094] Thu, 07 December 2017 15:33 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
-- test table and data:
SCOTT@orcl_12.1.0.2.0> create table test_names(id number,firstname varchar2(100),secondname varchar2(100),thirdname varchar2(100),fourthname varchar2(100),fifthname varchar2(100));

Table created.

SCOTT@orcl_12.1.0.2.0> insert all
  2  into test_names values (1,'rajesh','kumar',null,null,null)
  3  into test_names values (2,'rajesh',null,'rajesh',null,null)
  4  into test_names values (3,'rajesh kumar',null,'rajesh',null,null)
  5  into test_names values (4,'rajesh',null,'rajesh kumar',null,'reddy')
  6  select * from dual
  7  /

4 rows created.

-- Oracle Text multi column datastore and context index:
SCOTT@orcl_12.1.0.2.0> begin
  2    ctx_ddl.create_preference ('test_mcds', 'multi_column_datastore');
  3    ctx_ddl.set_attribute ('test_mcds', 'columns', 'firstname, secondname, thirdname, fourthname, fifthname');
  4  end;
  5  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> alter table test_names add (allnames varchar2(1))
  2  /

Table altered.

SCOTT@orcl_12.1.0.2.0> create index test_names_idx on test_names (allnames)
  2    indextype is ctxsys.context
  3    parameters ('datastore  test_mcds')
  4  /

Index created.

-- inputname and query:
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh kumar sachin'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> select id
  2  from   test_names
  3  where  contains
  4  	      (allnames,
  5  	       'definemerge(((definescore('
  6  	       || replace (:inputname, ' ', ',discrete/100)),(definescore(')
  7  	       || ',discrete/100))),or,add)', 1) >= 2
  8  /

        ID
----------
         1
         3
         4

3 rows selected.
Re: need two token matching query [message #667110 is a reply to message #667106] Fri, 08 December 2017 00:21 Go to previous messageGo to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
Thanks Barbara.

With out Context Index I need to write the sql query.Could you please help in this scenario..

Regards,
Rajesh.

Re: need two token matching query [message #667115 is a reply to message #667110] Fri, 08 December 2017 01:55 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
mvrkr44 wrote on Thu, 07 December 2017 22:21
Thanks Barbara.

With out Context Index I need to write the sql query.Could you please help in this scenario..

Regards,
Rajesh.


Then I would suggest something like what Solomon suggested:

-- test table and data:
SCOTT@orcl_12.1.0.2.0> create table test_names(id number,firstname varchar2(100),secondname varchar2(100),thirdname varchar2(100),fourthname varchar2(100),fifthname varchar2(100));

Table created.

SCOTT@orcl_12.1.0.2.0> insert all
  2  into test_names values (1,'rajesh','kumar',null,null,null)
  3  into test_names values (2,'rajesh',null,'rajesh',null,null)
  4  into test_names values (3,'rajesh kumar',null,'rajesh',null,null)
  5  into test_names values (4,'rajesh',null,'rajesh kumar',null,'reddy')
  6  select * from dual
  7  /

4 rows created.

-- inputname and query:
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh kumar sachin'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> select id
  2  from   test_names,
  3  	    table (sys.odcivarchar2list (firstname, secondname, thirdname, fourthname, fifthname))
  4  where  instr (' ' || :inputname || ' ', ' ' || column_value || ' ') > 0
  5  group  by id
  6  having count (distinct column_value) >= 2
  7  /

        ID
----------
         1
         4
         3

3 rows selected.
Re: need two token matching query [message #667137 is a reply to message #667115] Sat, 09 December 2017 23:49 Go to previous messageGo to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
Thanks Barbara.

Small change in the table structure.

create table test_names(id number,id2 number,id3 number,firstname varchar2(100),secondname varchar2(100),thirdname varchar2(100),fourthname varchar2(100),fifthname varchar2(100));
ALTER TABLE test_names
ADD CONSTRAINT pk_test_names PRIMARY KEY (id, id2,id3);
insert into test_names values (1,1,1'rajesh','kumar',null,null,null);
insert into test_names values (1,1,2'rajesh','reddy',null,null,null);
insert into test_names values (1,1,3'rajesh','reddy',null,null,null);
insert into test_names values (1,2,1'rajesh',null,'rajesh',null,null);
insert into test_names values (1,2,2'rajesh kumar',null,'rajesh',null,null);
insert into test_names values (1,3,1'rajesh',null,'rajesh kumar',null,'reddy');
insert into test_names values (1,4,1'rajesh',null,'rajesh kumar',null,'reddy');
insert into test_names values (1,5,1'rajesh',null,'rajesh kumar',null,'reddy');
insert into test_names values (1,1,4'rajesh',null,'rajesh',null,null);
insert into test_names values (1,1,5'rajesh',null,'sachin',null,null);

Input :'rajesh kumar sachin'
Out put expecting:id id2 id3
1 1 1
1 2 2
1 3 1
1 4 1
1 5 1
1 1 5
Re: need two token matching query [message #667139 is a reply to message #667137] Sun, 10 December 2017 02:26 Go to previous messageGo to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
I need to get the same output with the different inputs

Input Parameters : 'rajesh kumar sachin'
'kumar sachin rajesh'
Out put expecting records :id id2 id3
1 1 1
1 2 2
1 3 1
1 4 1
1 5 1
1 1 5
Re: need two token matching query [message #667141 is a reply to message #667139] Sun, 10 December 2017 06:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Then I would suggest something more like what mvrkr44 suggested.

-- test table and data:
SCOTT@orcl_12.1.0.2.0> create table test_names
  2    (id	    number,
  3  	id2	    number,
  4  	id3	    number,
  5  	firstname   varchar2(100),
  6  	secondname  varchar2(100),
  7  	thirdname   varchar2(100),
  8  	fourthname  varchar2(100),
  9  	fifthname   varchar2(100))
 10  /

Table created.

SCOTT@orcl_12.1.0.2.0> ALTER TABLE test_names ADD CONSTRAINT pk_test_names PRIMARY KEY (id, id2,id3)
  2  /

Table altered.

SCOTT@orcl_12.1.0.2.0> insert all
  2  into test_names values (1,1,1,'rajesh','kumar',null,null,null)
  3  into test_names values (1,1,2,'rajesh','reddy',null,null,null)
  4  into test_names values (1,1,3,'rajesh','reddy',null,null,null)
  5  into test_names values (1,2,1,'rajesh',null,'rajesh',null,null)
  6  into test_names values (1,2,2,'rajesh kumar',null,'rajesh',null,null)
  7  into test_names values (1,3,1,'rajesh',null,'rajesh kumar',null,'reddy')
  8  into test_names values (1,4,1,'rajesh',null,'rajesh kumar',null,'reddy')
  9  into test_names values (1,5,1,'rajesh',null,'rajesh kumar',null,'reddy')
 10  into test_names values (1,1,4,'rajesh',null,'rajesh',null,null)
 11  into test_names values (1,1,5,'rajesh',null,'sachin',null,null)
 12  select * from dual
 13  /

10 rows created.

-- inputname and query:
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh kumar sachin'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> select id, id2, id3
  2  from   (select  distinct id, id2, id3,
  3  		     trim
  4  		       (regexp_substr
  5  			  (firstname || ' ' || secondname || ' ' || thirdname || ' ' || fourthname || ' ' || fifthname,
  6  			   '[^ ]+', 1, level)) as names
  7  	     from    test_names
  8  	     connect by regexp_substr (firstname || ' ' || secondname || ' ' || thirdname || ' ' || fourthname || ' ' || fifthname,
  9  				       '[^ ]+', 1, level) is not null
 10  			and prior id = id
 11  			and prior sys_guid() is not null)
 12  where   instr (' ' || :inputname || ' ', ' ' || names || ' ') > 0
 13  group   by id, id2, id3
 14  having  count(*) >= 2
 15  order   by id, id2, id3
 16  /

        ID        ID2        ID3
---------- ---------- ----------
         1          1          1
         1          1          5
         1          2          2
         1          3          1
         1          4          1
         1          5          1

6 rows selected.

SCOTT@orcl_12.1.0.2.0> exec :inputname := 'kumar sachin rajesh'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> /

        ID        ID2        ID3
---------- ---------- ----------
         1          1          1
         1          1          5
         1          2          2
         1          3          1
         1          4          1
         1          5          1

6 rows selected.
Re: need two token matching query [message #667142 is a reply to message #667141] Sun, 10 December 2017 06:37 Go to previous messageGo to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
Thanks Barbara Boehmer.
Re: need two token matching query [message #667143 is a reply to message #667139] Sun, 10 December 2017 10:34 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
This is no different from original task:

with t as (
           select  id,
                   id2,
                   id3,
                   (
                    select  count(distinct column_value)
                      from  table(sys.OdciVarchar2List(firstname,secondname,thirdname,fourthname,fifthname))
                      where instr(' ' || '&search_tring' || ' ',' ' || column_value || ' ') > 0
                   ) cnt
             from  test_names
          )
select  id,
        id2,
        id3
  from  t
  where cnt > 1
/
Enter value for search_tring: rajesh kumar sachin
old   8:                       where instr(' ' || '&search_tring' || ' ',' ' || column_value || ' ') > 0
new   8:                       where instr(' ' || 'rajesh kumar sachin' || ' ',' ' || column_value || ' ') > 0

        ID        ID2        ID3
---------- ---------- ----------
         1          1          1
         1          2          2
         1          3          1
         1          4          1
         1          5          1
         1          1          5

6 rows selected.

SQL>

SY.
Re: need two token matching query [message #667144 is a reply to message #667143] Sun, 10 December 2017 13:49 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Solomon Yakobson wrote on Sun, 10 December 2017 08:34
This is no different from original task:...
Yes, it is different. The original poster said that he wants the same results for input of 'rajesh kumar sachin' and input of 'kumar sachin rejesh'. You only ran your query for the first input. The following run of your query with the second input produces different results.

SCOTT@orcl_12.1.0.2.0> -- test table and data:
SCOTT@orcl_12.1.0.2.0> create table test_names
  2    (id	    number,
  3  	id2	    number,
  4  	id3	    number,
  5  	firstname   varchar2(100),
  6  	secondname  varchar2(100),
  7  	thirdname   varchar2(100),
  8  	fourthname  varchar2(100),
  9  	fifthname   varchar2(100))
 10  /

Table created.

SCOTT@orcl_12.1.0.2.0> ALTER TABLE test_names ADD CONSTRAINT pk_test_names PRIMARY KEY (id, id2,id3)
  2  /

Table altered.

SCOTT@orcl_12.1.0.2.0> insert all
  2  into test_names values (1,1,1,'rajesh','kumar',null,null,null)
  3  into test_names values (1,1,2,'rajesh','reddy',null,null,null)
  4  into test_names values (1,1,3,'rajesh','reddy',null,null,null)
  5  into test_names values (1,2,1,'rajesh',null,'rajesh',null,null)
  6  into test_names values (1,2,2,'rajesh kumar',null,'rajesh',null,null)
  7  into test_names values (1,3,1,'rajesh',null,'rajesh kumar',null,'reddy')
  8  into test_names values (1,4,1,'rajesh',null,'rajesh kumar',null,'reddy')
  9  into test_names values (1,5,1,'rajesh',null,'rajesh kumar',null,'reddy')
 10  into test_names values (1,1,4,'rajesh',null,'rajesh',null,null)
 11  into test_names values (1,1,5,'rajesh',null,'sachin',null,null)
 12  select * from dual
 13  /

10 rows created.

SCOTT@orcl_12.1.0.2.0> with t as (
  2  		select	id,
  3  			id2,
  4  			id3,
  5  			(
  6  			 select  count(distinct column_value)
  7  			   from  table(sys.OdciVarchar2List(firstname,secondname,thirdname,fourthname,fifthname))
  8  			   where instr(' ' || '&search_tring' || ' ',' ' || column_value || ' ') > 0
  9  			) cnt
 10  		  from	test_names
 11  	       )
 12  select  id,
 13  	     id2,
 14  	     id3
 15    from  t
 16    where cnt > 1
 17  /
Enter value for search_tring: kumar sachin rajesh
old   8:                       where instr(' ' || '&search_tring' || ' ',' ' || column_value || ' ') > 0
new   8:                       where instr(' ' || 'kumar sachin rajesh' || ' ',' ' || column_value || ' ') > 0

        ID        ID2        ID3
---------- ---------- ----------
         1          1          1
         1          1          5

2 rows selected.

Re: need two token matching query [message #667151 is a reply to message #667141] Mon, 11 December 2017 06:47 Go to previous messageGo to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
Thanks Barbara Boehmer ,

Your solution is working fine.But It is taking time for Millions of records.(we have almost 40 millions of records in the table)
is there any way to increase the performance of the query?

Regards,
rajesh.
Re: need two token matching query [message #667159 is a reply to message #667151] Mon, 11 December 2017 21:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Please try the following instead. Also, if you do not need the order by clause, then you can eliminate that.

SCOTT@orcl_12.1.0.2.0> select id, id2, id3
  2  from   test_names,
  3  	    (select  regexp_substr (:inputname, '[^ ]+', 1, rownum) names
  4  	     from    dual
  5  	     connect by level <= regexp_count (:inputname, ' +') + 1)
  6  where  instr
  7  	      (' '||firstname||' '||secondname||' '||thirdname||' '||fourthname||' '||fifthname||' ',
  8  	       ' ' || names || ' ') > 0
  9  group  by id, id2, id3
 10  having count (distinct names) >= 2
 11  order  by id, id2, id3
 12  /

        ID        ID2        ID3
---------- ---------- ----------
         1          1          1
         1          1          5
         1          2          2
         1          3          1
         1          4          1
         1          5          1

6 rows selected.
Re: need two token matching query [message #667293 is a reply to message #667159] Wed, 20 December 2017 00:27 Go to previous messageGo to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
Thanks Barbara Boehmer .
Now little bit requirement changed.

SCOTT@orcl_12.1.0.2.0> -- test table and data:
SCOTT@orcl_12.1.0.2.0> create table test_names
2 (id number,
3 id2 number,
4 id3 number,
5 firstname varchar2(100),
6 secondname varchar2(100),
7 thirdname varchar2(100),
8 fourthname varchar2(100),
9 fifthname varchar2(100))
10 /

Table created.

SCOTT@orcl_12.1.0.2.0> ALTER TABLE test_names ADD CONSTRAINT pk_test_names PRIMARY KEY (id, id2,id3)
2 /

Table altered.

SCOTT@orcl_12.1.0.2.0> insert all
2 into test_names values (1,1,1,'rajesh','kumar',null,null,null)
3 into test_names values (1,1,2,'rajesh','reddy',null,null,null)
4 into test_names values (1,1,3,'rajesh','reddy',null,null,null)
5 into test_names values (1,2,1,'rajesh',null,'rajesh',null,null)
6 into test_names values (1,2,2,'rajesh kumar',null,'rajesh',null,null)
7 into test_names values (1,3,1,'rajesh',null,'rajesh kumar',null,'reddy')
8 into test_names values (1,4,1,'rajesh',null,'rajesh kumar',null,'reddy')
9 into test_names values (1,5,1,'rajesh',null,'rajesh kumar',null,'reddy')
10 into test_names values (1,1,4,'rajesh',null,'rajesh',null,null)
11 into test_names values (1,1,5,'rajesh',null,'sachin',null,null)
12 select * from dual
13 /


My input query : rajesh rajesh ganguly

output i need : id id2 id3
1 2 1
1 2 2
1 3 1
1 4 1
1 5 1
1 1 4

Second input query : rajesh sachin
output i need : id id2 id3
1 1 5

I need single query to satisfy the two above conditions
Could you please help me in this scenario??

Regards,
Rajesh
Re: need two token matching query [message #667298 is a reply to message #667293] Wed, 20 December 2017 02:58 Go to previous messageGo to next message
quirks
Messages: 82
Registered: October 2014
Member
Please specify the matching criteria for "rajesh rajesh ganguly" (for "rajesh sachin" the query is still working).
Re: need two token matching query [message #667319 is a reply to message #667293] Wed, 20 December 2017 12:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_12.1.0.2.0> -- test table and data:
SCOTT@orcl_12.1.0.2.0> create table test_names
  2    (id	    number,
  3  	id2	    number,
  4  	id3	    number,
  5  	firstname   varchar2(100),
  6  	secondname  varchar2(100),
  7  	thirdname   varchar2(100),
  8  	fourthname  varchar2(100),
  9  	fifthname   varchar2(100))
 10  /

Table created.

SCOTT@orcl_12.1.0.2.0> ALTER TABLE test_names ADD CONSTRAINT pk_test_names PRIMARY KEY (id, id2,id3)
  2  /

Table altered.

SCOTT@orcl_12.1.0.2.0> insert all
  2  into test_names values (1,1,1,'rajesh','kumar',null,null,null)
  3  into test_names values (1,1,2,'rajesh','reddy',null,null,null)
  4  into test_names values (1,1,3,'rajesh','reddy',null,null,null)
  5  into test_names values (1,2,1,'rajesh',null,'rajesh',null,null)
  6  into test_names values (1,2,2,'rajesh kumar',null,'rajesh',null,null)
  7  into test_names values (1,3,1,'rajesh',null,'rajesh kumar',null,'reddy')
  8  into test_names values (1,4,1,'rajesh',null,'rajesh kumar',null,'reddy')
  9  into test_names values (1,5,1,'rajesh',null,'rajesh kumar',null,'reddy')
 10  into test_names values (1,1,4,'rajesh',null,'rajesh',null,null)
 11  into test_names values (1,1,5,'rajesh',null,'sachin',null,null)
 12  select * from dual
 13  /

10 rows created.

SCOTT@orcl_12.1.0.2.0> commit
  2  /

Commit complete.

SCOTT@orcl_12.1.0.2.0> -- inputname and query:
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh rajesh ganguly'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> select id, id2, id3
  2  from   test_names,
  3  	    (select  row_number () over (partition by names order by rownum) rn, names
  4  	     from    (select  regexp_substr (:inputname, '[^ ]+', 1, rownum) names
  5  		      from    dual
  6  		      connect by level <= regexp_count (:inputname, ' +') + 1))
  7  group  by id, id2, id3
  8  having sum
  9  	      (case when rn = 1 and
 10  			instr
 11  			  (' '||firstname||' '||secondname||' '||thirdname||' '||fourthname||' '||fifthname||' ',
 12  			   ' ' || names || ' ', 1, 1) > 0
 13  		    then 1 else 0 end
 14  	       +
 15  	       case when rn = 2 and
 16  			 instr
 17  			   (' '||firstname||' '||secondname||' '||thirdname||' '||fourthname||' '||fifthname||' ',
 18  			    ' ' || names || ' ', 1, 2) > 0
 19  		    then 1 else 0 end) >= 2
 20  order  by id, id2, id3
 21  /

        ID        ID2        ID3
---------- ---------- ----------
         1          1          4
         1          2          1
         1          2          2
         1          3          1
         1          4          1
         1          5          1

6 rows selected.

SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh sachin'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> /

        ID        ID2        ID3
---------- ---------- ----------
         1          1          5

1 row selected.
Re: need two token matching query [message #667330 is a reply to message #667319] Thu, 21 December 2017 02:37 Go to previous messageGo to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
Thanks Barbara Boehmer .
It is working fine..Now i need same logic with context index on full name column.

create table test_names
     (id	    number,
   	id2	    number,
  	id3	    number,
   	fullname  varchar2(500));


Table created.

create index test_names_idx on test_names (fullname)
      indextype is ctxsys.context;

Index created.

 insert all
  into test_names values (1,1,1,'rajesh kumar')
  into test_names values (1,1,2,'rajesh reddy')
  into test_names values (1,1,3,'rajesh reddy')
  into test_names values (1,2,1,'rajesh rajesh')
  into test_names values (1,2,2,'rajesh kumar rajesh')
  into test_names values (1,3,1,'rajesh rajesh kumar reddy')
    into test_names values (1,4,1,'rajesh rajesh kumar reddy')
  into test_names values (1,5,1,'rajesh rajesh kumar reddy')
   into test_names values (1,1,4,'rajesh rajesh')
 into test_names values (1,1,5,'rajesh sachin')
 select * from dual
   /


begin
ctx_ddl.sync_index('test_names_idx');
end;
/

My input query : rajesh rajesh ganguly

output i need : id id2 id3
1 2 1
1 2 2
1 3 1
1 4 1
1 5 1
1 1 4

Second input query : rajesh sachin
output i need : id id2 id3
1 1 5

I need single query to satisfy the two above conditions with CONTAINS function
Could you please help me in this scenario??

[mod-edit: code tags added by bb]

[Updated on: Thu, 21 December 2017 11:58] by Moderator

Report message to a moderator

Re: need two token matching query [message #667346 is a reply to message #667330] Thu, 21 December 2017 14:25 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I had to add some functions and a multi_column_datastore.

SCOTT@orcl_12.1.0.2.0> create table test_names
  2    (id	  number,
  3  	id2	  number,
  4  	id3	  number,
  5  	fullname  varchar2(500))
  6  /

Table created.

SCOTT@orcl_12.1.0.2.0> create or replace function format_fullname
  2    (p_fullname in varchar2)
  3    return	      varchar2
  4  as
  5    v_fullname     varchar2(100);
  6  begin
  7   for i in
  8  	(select  regexp_substr (p_fullname, '[^ ]+', 1, level) name
  9  	 from	 dual
 10  	 connect by level <= regexp_count (p_fullname, ' +') + 1
 11  	 order	 by name)
 12    loop
 13  	 v_fullname := v_fullname || i.name || ' ';
 14    end loop;
 15    return trim (v_fullname);
 16  end format_fullname;
 17  /

Function created.

SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> begin
  2    ctx_ddl.create_preference ('test_mcds', 'multi_column_datastore');
  3    ctx_ddl.set_attribute ('test_mcds', 'columns', 'format_fullname (fullname) names');
  4  end;
  5  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> create index test_names_idx on test_names (fullname)
  2    indextype is ctxsys.context
  3    parameters ('datastore  test_mcds')
  4  /

Index created.

SCOTT@orcl_12.1.0.2.0> insert all
  2    into test_names values (1,1,1,'rajesh kumar')
  3    into test_names values (1,1,2,'rajesh reddy')
  4    into test_names values (1,1,3,'rajesh reddy')
  5    into test_names values (1,2,1,'rajesh rajesh')
  6    into test_names values (1,2,2,'rajesh kumar rajesh')
  7    into test_names values (1,3,1,'rajesh rajesh kumar reddy')
  8    into test_names values (1,4,1,'rajesh rajesh kumar reddy')
  9    into test_names values (1,5,1,'rajesh rajesh kumar reddy')
 10    into test_names values (1,1,4,'rajesh rajesh')
 11    into test_names values (1,1,5,'rajesh sachin')
 12  select * from dual
 13  /

10 rows created.

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

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> create or replace function format_input
  2    (p_inputname in varchar2)
  3    return	       varchar2
  4  as
  5    v_searchstring  varchar2(4000);
  6  begin
  7    v_searchstring := 'definemerge((';
  8    for i in
  9  	 (select name, count(*) cnt
 10  	  from	 (select regexp_substr (p_inputname, '[^ ]+', 1, level) name
 11  		  from	 dual
 12  		  connect by level <= regexp_count (p_inputname, ' +') + 1)
 13  	  group  by name)
 14    loop
 15  	 v_searchstring := v_searchstring || '(definescore(' || i.name || ',discrete/100)),';
 16  	 if i.cnt > 1 then
 17  	   v_searchstring := v_searchstring || '(definescore(' || i.name || ' ' || i.name || ',discrete/100)),';
 18  	 end if;
 19    end loop;
 20    v_searchstring := rtrim (v_searchstring, ',') || '),or,add)';
 21    return v_searchstring;
 22  end format_input;
 23  /

Function created.

SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh rajesh ganguly'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> select id, id2, id3
  2  from   test_names
  3  where  contains (fullname, format_input (:inputname)) >= 2
  4  order  by id, id2, id3
  5  /

        ID        ID2        ID3
---------- ---------- ----------
         1          1          4
         1          2          1
         1          2          2
         1          3          1
         1          4          1
         1          5          1

6 rows selected.

SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh sachin'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> select id, id2, id3
  2  from   test_names
  3  where  contains (fullname, format_input (:inputname)) >= 2
  4  order  by id, id2, id3
  5  /

        ID        ID2        ID3
---------- ---------- ----------
         1          1          5

1 row selected.

Re: need two token matching query [message #667361 is a reply to message #667346] Sun, 24 December 2017 23:19 Go to previous messageGo to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
Thanks Barbara Boehmer

Regards,
Rajesh
Re: need two token matching query [message #667417 is a reply to message #667346] Wed, 27 December 2017 04:25 Go to previous messageGo to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
Hi Barbara Boehmer

I need to add the below set_attribute to the existing preference .How can I add it.
Please help.

new attribute :
ctx_ddl.set_attribute('test_mcds','printjoins',''' "');

Existing preference as below


SCOTT@orcl_12.1.0.2.0> begin
2 ctx_ddl.create_preference ('test_mcds', 'multi_column_datastore');
3 ctx_ddl.set_attribute ('test_mcds', 'columns', 'format_fullname (fullname) names');
4 end;
5 /

Regards,
Rajesh
Re: need two token matching query [message #667418 is a reply to message #667417] Wed, 27 December 2017 04:55 Go to previous messageGo to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
Got it.

Regards,
rajesh
Re: need two token matching query [message #667667 is a reply to message #667418] Wed, 10 January 2018 02:34 Go to previous messageGo to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
Hi Barbara,

SCOTT@orcl_12.1.0.2.0> create table test_names(id number,firstname varchar2(100),secondname varchar2(100),thirdname varchar2(100),fourthname varchar2(100),fifthname varchar2(100));

Table created.

SCOTT@orcl_12.1.0.2.0> insert all
2 into test_names values (1,'rajesh','kumar',null,null,null)
3 into test_names values (2,'rajesh',null,'rajesh',null,null)
4 into test_names values (3,'rajesh kumar',null,'rajesh',null,null)
5 into test_names values (4,'rajesh',null,'rajesh kumar',null,'reddy')
6 select * from dual
7 /

4 rows created.

SCOTT@orcl_12.1.0.2.0> begin
2 ctx_ddl.create_preference ('test_mcds', 'multi_column_datastore');
3 ctx_ddl.set_attribute ('test_mcds', 'columns', 'firstname, secondname, thirdname, fourthname, fifthname');
4 end;
5 /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> alter table test_names add (allnames varchar2(1))
2 /

Table altered.



SCOTT@orcl_12.1.0.2.0> create or replace function format_input
2 (p_inputname in varchar2)
3 return varchar2
4 as
5 v_searchstring varchar2(4000);
6 begin
7 v_searchstring := 'definemerge((';
8 for i in
9 (select name, count(*) cnt
10 from (select regexp_substr (p_inputname, '[^ ]+', 1, level) name
11 from dual
12 connect by level <= regexp_count (p_inputname, ' +') + 1)
13 group by name)
14 loop
15 v_searchstring := v_searchstring || '(definescore(' || i.name || ',discrete/100)),';
16 if i.cnt > 1 then
17 v_searchstring := v_searchstring || '(definescore(' || i.name || ' ' || i.name || ',discrete/100)),';
18 end if;
19 end loop;
20 v_searchstring := rtrim (v_searchstring, ',') || '),or,add)';
21 return v_searchstring;
22 end format_input;
23 /

Function created.

Scenario:
------------

How can i create the context index and use in the below query?Please help in this scenario.

select id, id2, id3
from test_names
where contains (allnames, format_input (:inputname)) >= 2


Regards,
Rajesh
Re: need two token matching query [message #667706 is a reply to message #667667] Thu, 11 January 2018 04:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
-- what you provided:
SCOTT@orcl_12.1.0.2.0> create table test_names
  2    (id	    number,
  3  	firstname   varchar2(100),
  4  	secondname  varchar2(100),
  5  	thirdname   varchar2(100),
  6  	fourthname  varchar2(100),
  7  	fifthname   varchar2(100))
  8  /

Table created.

SCOTT@orcl_12.1.0.2.0> insert all
  2    into test_names values (1,'rajesh','kumar',null,null,null)
  3    into test_names values (2,'rajesh',null,'rajesh',null,null)
  4    into test_names values (3,'rajesh kumar',null,'rajesh',null,null)
  5    into test_names values (4,'rajesh',null,'rajesh kumar',null,'reddy')
  6  select * from dual
  7  /

4 rows created.

SCOTT@orcl_12.1.0.2.0> begin
  2    ctx_ddl.create_preference ('test_mcds', 'multi_column_datastore');
  3    ctx_ddl.set_attribute ('test_mcds', 'columns', 'firstname, secondname, thirdname, fourthname, fifthname');
  4  end;
  5  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> alter table test_names add (allnames varchar2(1))
  2  /

Table altered.

SCOTT@orcl_12.1.0.2.0> create or replace function format_input
  2    (p_inputname in varchar2)
  3    return	       varchar2
  4  as
  5    v_searchstring  varchar2(4000);
  6  begin
  7    v_searchstring := 'definemerge((';
  8    for i in
  9  	 (select name, count(*) cnt
 10  	  from	 (select  regexp_substr (p_inputname, '[^ ]+', 1, level) name
 11  		  from	  dual
 12  		  connect by level <= regexp_count (p_inputname, ' +') + 1)
 13  	  group  by name)
 14    loop
 15  	 v_searchstring := v_searchstring || '(definescore(' || i.name || ',discrete/100)),';
 16  	 if i.cnt > 1 then
 17  	   v_searchstring := v_searchstring || '(definescore(' || i.name || ' ' || i.name || ',discrete/100)),';
 18  	 end if;
 19    end loop;
 20    v_searchstring := rtrim (v_searchstring, ',') || '),or,add)';
 21    return v_searchstring;
 22  end format_input;
 23  /

Function created.

-- what you asked for:
SCOTT@orcl_12.1.0.2.0> begin
  2    ctx_ddl.set_attribute ('test_mcds', 'delimiter', 'newline');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> create index test_names_idx on test_names (allnames)
  2    indextype is ctxsys.context
  3    parameters ('datastore  test_mcds')
  4  /

Index created.

SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh kumar sachin'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> select id
  2  from   test_names
  3  where  contains (allnames, format_input (:inputname)) >= 2
  4  /

        ID
----------
         1
         3
         4

3 rows selected.

Re: need two token matching query [message #667852 is a reply to message #667706] Sun, 21 January 2018 06:29 Go to previous messageGo to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
Thanks Barbara Boehmer ,

I need one more change to search the special characters,i succedd most of the characters but failing only with () characters to search. Please help in this scenario

SCOTT@orcl_12.1.0.2.0> create table test_names
2 (id number,
3 firstname varchar2(100),
4 secondname varchar2(100),
5 thirdname varchar2(100),
6 fourthname varchar2(100),
7 fifthname varchar2(100))
8 /

Table created.

SCOTT@orcl_12.1.0.2.0> insert all
2 into test_names values (1,'rajesh','kumar',null,null,null)
3 into test_names values (2,'rajesh',null,'rajesh',null,null)
4 into test_names values (3,'rajesh kumar',null,'rajesh',null,null)
5 into test_names values (4,'rajesh',null,'rajesh kumar',null,'reddy')
6 into test_names values (5,'rajesh'||chr(40),null,'rajeshkumar',null,'reddy')
7 select * from dual
7 /

I need to serach like the below query
select id
from test_names
where contains (allnames, format_input ('rajesh( reddy')) >= 2
/

Output should come as below --(i need to search special characters also lik (& * % ( ) { } % # $ ~ as it is in names)
ID
--
5

[Updated on: Sun, 21 January 2018 06:30]

Report message to a moderator

Re: need two token matching query [message #667855 is a reply to message #667852] Sun, 21 January 2018 16:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You can create a lexer and set the special characters as printjoins, then use that lexer in the index parameters. That will cause the special characters to be included as part of the indexed tokens, so that they are searchable. You can modify the format_input function to add a \ in front of each special character, so that they are treated as text characters in the search string, instead of special characters that would otherwise cause a text query parser error. Please see the demonstration below that does this.

This is just the option that you requested. Another option would be to not use a lexer so that the special characters would be ignored when indexing and modify the format_input function to remove the special characters. This would, however cause it to return results with and without the special characters. So, it all depends on whether you want to ignore the special characters or search for them.

SCOTT@orcl_12.1.0.2.0> create table test_names
  2    (id	    number,
  3  	firstname   varchar2(100),
  4  	secondname  varchar2(100),
  5  	thirdname   varchar2(100),
  6  	fourthname  varchar2(100),
  7  	fifthname   varchar2(100))
  8  /

Table created.

SCOTT@orcl_12.1.0.2.0> insert all
  2    into test_names values (1,'rajesh','kumar',null,null,null)
  3    into test_names values (2,'rajesh',null,'rajesh',null,null)
  4    into test_names values (3,'rajesh kumar',null,'rajesh',null,null)
  5    into test_names values (4,'rajesh',null,'rajesh kumar',null,'reddy')
  6    into test_names values (5,'rajesh'||chr(40),null,'rajeshkumar',null,'reddy')
  7  select * from dual
  8  /

5 rows created.

SCOTT@orcl_12.1.0.2.0> begin
  2    ctx_ddl.create_preference ('test_mcds', 'multi_column_datastore');
  3    ctx_ddl.set_attribute ('test_mcds', 'columns', 'firstname, secondname, thirdname, fourthname, fifthname');
  4    ctx_ddl.set_attribute ('test_mcds', 'delimiter', 'newline');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> alter table test_names add (allnames varchar2(1))
  2  /

Table altered.

SCOTT@orcl_12.1.0.2.0> -- add lexer and set special characters as printjoins:
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 lexer in index parameters:
SCOTT@orcl_12.1.0.2.0> create index test_names_idx on test_names (allnames)
  2    indextype is ctxsys.context
  3    parameters
  4  	 ('datastore  test_mcds
  5  	   lexer      test_lex')
  6  /

Index created.

SCOTT@orcl_12.1.0.2.0> -- modify function to add \ in front of each special character:
SCOTT@orcl_12.1.0.2.0> create or replace function format_input
  2    (p_inputname in varchar2)
  3    return	       varchar2
  4  as
  5    v_searchstring  varchar2(4000);
  6    v_name	       varchar2(100);
  7  begin
  8    v_searchstring := 'definemerge((';
  9    for i in
 10  	 (select name,
 11  		 count(*) cnt
 12  	  from	 (select  regexp_substr (p_inputname, '[^ ]+', 1, level) name
 13  		  from	  dual
 14  		  connect by level <= regexp_count (p_inputname, ' +') + 1)
 15  	  group  by name)
 16    loop
 17  	 v_name := i.name;
 18  	 v_name := replace (v_name, '(', '\(');
 19  	 v_name := replace (v_name, ')', '\)');
 20  	 v_name := replace (v_name, '&', '\&');
 21  	 v_name := replace (v_name, '*', '\*');
 22  	 v_name := replace (v_name, '%', '\%');
 23  	 v_name := replace (v_name, '{', '\{');
 24  	 v_name := replace (v_name, '}', '\}');
 25  	 v_name := replace (v_name, '#', '\#');
 26  	 v_name := replace (v_name, '$', '\$');
 27  	 v_name := replace (v_name, '~', '\~');
 28  	 v_searchstring := v_searchstring || '(definescore(' || v_name || ',discrete/100)),';
 29  	 if i.cnt > 1 then
 30  	   v_searchstring := v_searchstring || '(definescore(' || v_name || ' ' || v_name || ',discrete/100)),';
 31  	 end if;
 32    end loop;
 33    v_searchstring := rtrim (v_searchstring, ',') || '),or,add)';
 34    return v_searchstring;
 35  end format_input;
 36  /

Function created.

SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh( reddy'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> select id
  2  from   test_names
  3  where  contains (allnames, format_input (:inputname)) >= 2
  4  /

        ID
----------
         5

1 row selected.

Re: need two token matching query [message #667856 is a reply to message #667855] Mon, 22 January 2018 02:40 Go to previous messageGo to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
Barbara Boehmer ,

Its working fine..But when i add special character ? in the print joins lexer..
it is not working as expected.

insert all
into test_names values (6,'rajesh?','kumar',null,null,null)
select * from dual;


SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh kumar'
SCOTT@orcl_12.1.0.2.0> select id
2 from test_names
3 where contains (allnames, format_input (:inputname)) >= 2
4 /

ID
---------------
1
3
4
6

In the above query i should not get the ID-6.


And also when i execute

SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh? kumar'

SCOTT@orcl_12.1.0.2.0> select id
2 from test_names
3 where contains (allnames, format_input (:inputname)) >= 2
4 /

ID
---------------
1
3
4
6

here i should not get ID-1,3,4

Please help in this scenario

Regards,
Rajesh
Re: need two token matching query [message #667858 is a reply to message #667856] Mon, 22 January 2018 04:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
By default, the question mark is a punctuation, along with the period and exclamation point and that overrides setting it as a printjoin in some cases. You can modify this by specifying the punctuations as only period and exclamation point:

ctx_ddl.set_attribute ('test_lex', 'punctuations', '.!');

Please see the revised demonstration below, that includes the above as well as adding the question mark to the printjoins and modifying the format_input function to add a backslash in front of each question mark.

SCOTT@orcl_12.1.0.2.0> create table test_names
  2    (id	    number,
  3  	firstname   varchar2(100),
  4  	secondname  varchar2(100),
  5  	thirdname   varchar2(100),
  6  	fourthname  varchar2(100),
  7  	fifthname   varchar2(100))
  8  /

Table created.

SCOTT@orcl_12.1.0.2.0> insert all
  2    into test_names values (1,'rajesh','kumar',null,null,null)
  3    into test_names values (2,'rajesh',null,'rajesh',null,null)
  4    into test_names values (3,'rajesh kumar',null,'rajesh',null,null)
  5    into test_names values (4,'rajesh',null,'rajesh kumar',null,'reddy')
  6    into test_names values (5,'rajesh'||chr(40),null,'rajeshkumar',null,'reddy')
  7    into test_names values (6,'rajesh?','kumar',null,null,null)
  8  select * from dual
  9  /

6 rows created.

SCOTT@orcl_12.1.0.2.0> alter table test_names add (allnames varchar2(1))
  2  /

Table altered.

SCOTT@orcl_12.1.0.2.0> begin
  2    ctx_ddl.create_preference ('test_mcds', 'multi_column_datastore');
  3    ctx_ddl.set_attribute ('test_mcds', 'columns', 'firstname, secondname, thirdname, fourthname, fifthname');
  4    ctx_ddl.set_attribute ('test_mcds', 'delimiter', 'newline');
  5    ctx_ddl.create_preference ('test_lex', 'basic_lexer');
  6    ctx_ddl.set_attribute ('test_lex', 'printjoins', '&*%(){}%#$~?');
  7    ctx_ddl.set_attribute ('test_lex', 'punctuations', '.!');
  8  end;
  9  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> create index test_names_idx on test_names (allnames)
  2    indextype is ctxsys.context
  3    parameters
  4  	 ('datastore  test_mcds
  5  	   lexer      test_lex')
  6  /

Index created.

SCOTT@orcl_12.1.0.2.0> create or replace function format_input
  2    (p_inputname in varchar2)
  3    return	       varchar2
  4  as
  5    v_searchstring  varchar2(4000);
  6    v_name	       varchar2(100);
  7  begin
  8    v_searchstring := 'definemerge((';
  9    for i in
 10  	 (select name,
 11  		 count(*) cnt
 12  	  from	 (select  regexp_substr (p_inputname, '[^ ]+', 1, level) name
 13  		  from	  dual
 14  		  connect by level <= regexp_count (p_inputname, ' +') + 1)
 15  	  group  by name)
 16    loop
 17  	 v_name := i.name;
 18  	 v_name := replace (v_name, '(', '\(');
 19  	 v_name := replace (v_name, ')', '\)');
 20  	 v_name := replace (v_name, '&', '\&');
 21  	 v_name := replace (v_name, '*', '\*');
 22  	 v_name := replace (v_name, '%', '\%');
 23  	 v_name := replace (v_name, '{', '\{');
 24  	 v_name := replace (v_name, '}', '\}');
 25  	 v_name := replace (v_name, '#', '\#');
 26  	 v_name := replace (v_name, '$', '\$');
 27  	 v_name := replace (v_name, '~', '\~');
 28  	 v_name := replace (v_name, '?', '\?');
 29  	 v_searchstring := v_searchstring || '(definescore(' || v_name || ',discrete/100)),';
 30  	 if i.cnt > 1 then
 31  	   v_searchstring := v_searchstring || '(definescore(' || v_name || ' ' || v_name || ',discrete/100)),';
 32  	 end if;
 33    end loop;
 34    v_searchstring := rtrim (v_searchstring, ',') || '),or,add)';
 35    return v_searchstring;
 36  end format_input;
 37  /

Function created.

SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh kumar'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> select id
  2  from   test_names
  3  where  contains (allnames, format_input (:inputname)) >= 2
  4  /

        ID
----------
         1
         3
         4

3 rows selected.

SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh? kumar'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> select id
  2  from   test_names
  3  where  contains (allnames, format_input (:inputname)) >= 2
  4  /

        ID
----------
         6

1 row selected.
Re: need two token matching query [message #667860 is a reply to message #667858] Mon, 22 January 2018 05:21 Go to previous messageGo to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
Thanks Barbara Boehmer ,
I have used the punctuations with the given solution and i inserted the below records,but i am not getting the expected result

insert all
into test_names values (7,'rajesh.','kumar',null,null,null)
into test_names values (8,'rajesh|',null,'rajesh',null,null)
into test_names values (9,'rajesh! kumar',null,'rajesh',null,null)
select * from dual;

I need to get the only ouput
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh. kumar'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> select id
2 from test_names
3 where contains (allnames, format_input (:inputname)) >= 2
4 /

expected output ID
------------------
7


SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh! kumar'

PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> select id
2 from test_names
3 where contains (allnames, format_input (:inputname)) >= 2
4 /

expected output ID
------------------
9


SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh| kumar'

PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> select id
2 from test_names
3 where contains (allnames, format_input (:inputname)) >= 2
4 /

expected output ID
------------------
8

I tried different ways but i didnt succeeded for these confitions .!|

Regards,
Rajesh.
Re: need two token matching query [message #667861 is a reply to message #667860] Mon, 22 January 2018 06:08 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You need to post a copy and paste of exactly what you have done, not just describe it. For each special character, you will need to add it to the printjoins. If it is a punctuation, then you will also need to remove it from the punctuations. If you remove all punctuations, you cannot use a null value or empty string as a parameter, so you must use a space or something. For each special character, you will also need to add a line to the format_input function to add a \ in front of it. Please see the following demonstration, noting the comments prefaced by -- along the left margin. Also, note that I added the name kumar to the row with id 8; Otherwise, that row does not belong in your result set, given the search string provided.

SCOTT@orcl_12.1.0.2.0> create table test_names
  2    (id	    number,
  3  	firstname   varchar2(100),
  4  	secondname  varchar2(100),
  5  	thirdname   varchar2(100),
  6  	fourthname  varchar2(100),
  7  	fifthname   varchar2(100))
  8  /

Table created.

SCOTT@orcl_12.1.0.2.0> insert all
  2    into test_names values (1,'rajesh','kumar',null,null,null)
  3    into test_names values (2,'rajesh',null,'rajesh',null,null)
  4    into test_names values (3,'rajesh kumar',null,'rajesh',null,null)
  5    into test_names values (4,'rajesh',null,'rajesh kumar',null,'reddy')
  6    into test_names values (5,'rajesh'||chr(40),null,'rajeshkumar',null,'reddy')
  7    into test_names values (6,'rajesh?','kumar',null,null,null)
  8    into test_names values (7,'rajesh.','kumar',null,null,null)
  9    into test_names values (8,'rajesh|',null,'kumar',null,null)
 10    into test_names values (9,'rajesh! kumar',null,'rajesh',null,null)
 11  select * from dual
 12  /

9 rows created.

SCOTT@orcl_12.1.0.2.0> alter table test_names add (allnames varchar2(1))
  2  /

Table altered.

SCOTT@orcl_12.1.0.2.0> begin
  2    ctx_ddl.create_preference ('test_mcds', 'multi_column_datastore');
  3    ctx_ddl.set_attribute ('test_mcds', 'columns', 'firstname, secondname, thirdname, fourthname, fifthname');
  4    ctx_ddl.set_attribute ('test_mcds', 'delimiter', 'newline');
  5    ctx_ddl.create_preference ('test_lex', 'basic_lexer');
  6  -- add characters to printjoins:
  7    ctx_ddl.set_attribute ('test_lex', 'printjoins', '&*%(){}%#$~?.|!');
  8  -- remove characters from punctuations:
  9    ctx_ddl.set_attribute ('test_lex', 'punctuations', ' ');
 10  end;
 11  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> create index test_names_idx on test_names (allnames)
  2    indextype is ctxsys.context
  3    parameters
  4  	 ('datastore  test_mcds
  5  	   lexer      test_lex')
  6  /

Index created.

SCOTT@orcl_12.1.0.2.0> create or replace function format_input
  2    (p_inputname in varchar2)
  3    return	       varchar2
  4  as
  5    v_searchstring  varchar2(4000);
  6    v_name	       varchar2(100);
  7  begin
  8    v_searchstring := 'definemerge((';
  9    for i in
 10  	 (select name,
 11  		 count(*) cnt
 12  	  from	 (select  regexp_substr (p_inputname, '[^ ]+', 1, level) name
 13  		  from	  dual
 14  		  connect by level <= regexp_count (p_inputname, ' +') + 1)
 15  	  group  by name)
 16    loop
 17  	 v_name := i.name;
 18  	 v_name := replace (v_name, '(', '\(');
 19  	 v_name := replace (v_name, ')', '\)');
 20  	 v_name := replace (v_name, '&', '\&');
 21  	 v_name := replace (v_name, '*', '\*');
 22  	 v_name := replace (v_name, '%', '\%');
 23  	 v_name := replace (v_name, '{', '\{');
 24  	 v_name := replace (v_name, '}', '\}');
 25  	 v_name := replace (v_name, '#', '\#');
 26  	 v_name := replace (v_name, '$', '\$');
 27  	 v_name := replace (v_name, '~', '\~');
 28  	 v_name := replace (v_name, '?', '\?');
 29  -- add \ in front of each character:
 30  	 v_name := replace (v_name, '.', '\.');
 31  	 v_name := replace (v_name, '|', '\|');
 32  	 v_name := replace (v_name, '!', '\!');
 33  	 v_searchstring := v_searchstring || '(definescore(' || v_name || ',discrete/100)),';
 34  	 if i.cnt > 1 then
 35  	   v_searchstring := v_searchstring || '(definescore(' || v_name || ' ' || v_name || ',discrete/100)),';
 36  	 end if;
 37    end loop;
 38    v_searchstring := rtrim (v_searchstring, ',') || '),or,add)';
 39    return v_searchstring;
 40  end format_input;
 41  /

Function created.

SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh. kumar'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> select id
  2  from   test_names
  3  where  contains (allnames, format_input (:inputname)) >= 2
  4  /

        ID
----------
         7

1 row selected.

SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh! kumar'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> select id
  2  from   test_names
  3  where  contains (allnames, format_input (:inputname)) >= 2
  4  /

        ID
----------
         9

1 row selected.

SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh| kumar'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> select id
  2  from   test_names
  3  where  contains (allnames, format_input (:inputname)) >= 2
  4  /

        ID
----------
         8

1 row selected.
Re: need two token matching query [message #667933 is a reply to message #667861] Wed, 24 January 2018 11:11 Go to previous messageGo to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
Thanks Barbara Boehmer ,

Small fix needed may be this is my final requirement.I am not getting the few records in the below scenario.Please help me

SCOTT@orcl_12.1.0.2.0> create table test_names
2 (id number,
3 id2 number,
4 id3 number,
5 fullname varchar2(500))
6 /

SCOTT@orcl_12.1.0.2.0> create or replace function format_input
2 (p_inputname in varchar2)
3 return varchar2
4 as
5 v_searchstring varchar2(4000);
6 v_name varchar2(100);
7 begin
8 v_searchstring := 'definemerge((';
9 for i in
10 (select name,
11 count(*) cnt
12 from (select regexp_substr (p_inputname, '[^ ]+', 1, level) name
13 from dual
14 connect by level <= regexp_count (p_inputname, ' +') + 1)
15 group by name)
16 loop
17 v_name := i.name;
18 v_name := replace (v_name, '(', '\(');
19 v_name := replace (v_name, ')', '\)');
20 v_name := replace (v_name, '&', '\&');
21 v_name := replace (v_name, '*', '\*');
22 v_name := replace (v_name, '%', '\%');
23 v_name := replace (v_name, '{', '\{');
24 v_name := replace (v_name, '}', '\}');
25 v_name := replace (v_name, '#', '\#');
26 v_name := replace (v_name, '$', '\$');
27 v_name := replace (v_name, '~', '\~');
28 v_name := replace (v_name, '?', '\?');
29 -- add \ in front of each character:
30 v_name := replace (v_name, '.', '\.');
31 v_name := replace (v_name, '|', '\|');
32 v_name := replace (v_name, '!', '\!');
33 v_searchstring := v_searchstring || '(definescore(' || v_name || ',discrete/100)),';
34 if i.cnt > 1 then
35 v_searchstring := v_searchstring || '(definescore(' || v_name || ' ' || v_name || ',discrete/100)),';
36 end if;
37 end loop;
38 v_searchstring := rtrim (v_searchstring, ',') || '),or,add)';
39 return v_searchstring;
40 end format_input;
41 /

Function created.



SCOTT@orcl_12.1.0.2.0> insert all
2 into test_names values (1,1,1,'rajesh kumar')
3 into test_names values (1,1,2,'rajesh reddy')
4 into test_names values (1,1,3,'rajesh reddy')
5 into test_names values (1,2,1,'rajesh rajesh')
6 into test_names values (1,2,2,'rajesh kumar rajesh')
7 into test_names values (1,3,1,'rajesh rajesh kumar reddy')
8 into test_names values (1,4,1,'rajesh dravid')
9 into test_names values (1,5,1,'rajesh rajesh kumar reddy')
10 into test_names values (1,1,4,'rajesh rajesh_ kumar sachin')
11 into test_names values (1,1,5,'rajesh sachin? ganguly')
12 into test_names values (1,1,7,'rajesh sachin')
13 into test_names values (1,1,8,'rajesh sachin')
14 into test_names values (1,1,9,'rajesh sachin')
15 select * from dual
16 /



SCOTT@orcl_12.1.0.2.0> begin
2 ctx_ddl.create_preference ('test_mcds', 'multi_column_datastore');
3 ctx_ddl.set_attribute ('test_mcds', 'columns', 'fullname');
4 ctx_ddl.set_attribute ('test_mcds', 'delimiter', 'newline');
5 ctx_ddl.create_preference ('test_lex', 'basic_lexer');
6 -- add characters to printjoins:
7 ctx_ddl.set_attribute ('test_lex', 'printjoins', '&*%(){}%#$~?.|!');
8 -- remove characters from punctuations:
9 ctx_ddl.set_attribute ('test_lex', 'punctuations', ' ');
10 end;
11 /


SCOTT@orcl_12.1.0.2.0> create index test_names_idx on test_names (fullname)
2 indextype is ctxsys.context
3 parameters
4 ('datastore test_mcds
5 lexer test_lex')
6 /

Index created.


SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh rajesh'

/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> select id,id2,id3
2 from test_names
3 where contains (fullname, format_input (:inputname)) >= 2
4 /

id id2 id3
--------------
1 2 1
1 3 1
1 5 1

i am not getting the expected output, i need to get the output as below including

id id2 id3
--------------
1 2 1
1 3 1
1 5 1
1 2 2
Re: need two token matching query [message #667937 is a reply to message #667933] Wed, 24 January 2018 16:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_12.1.0.2.0> create table test_names
  2    (id	  number,
  3  	id2	  number,
  4  	id3	  number,
  5  	fullname  varchar2(500))
  6  /

Table created.

SCOTT@orcl_12.1.0.2.0> create or replace function format_input
  2    (p_inputname in varchar2)
  3    return	       varchar2
  4  as
  5    v_searchstring  varchar2(4000);
  6    v_name	       varchar2(100);
  7  begin
  8    v_searchstring := 'definemerge((';
  9    for i in
 10  	(select name,
 11  		count(*) cnt
 12  	 from	(select  regexp_substr (p_inputname, '[^ ]+', 1, level) name
 13  		 from	 dual
 14  		 connect by level <= regexp_count (p_inputname, ' +') + 1)
 15  	 group	by name)
 16    loop
 17  	 v_name := i.name;
 18  	 v_name := replace (v_name, '(', '\(');
 19  	 v_name := replace (v_name, ')', '\)');
 20  	 v_name := replace (v_name, '&', '\&');
 21  	 v_name := replace (v_name, '*', '\*');
 22  	 v_name := replace (v_name, '%', '\%');
 23  	 v_name := replace (v_name, '{', '\{');
 24  	 v_name := replace (v_name, '}', '\}');
 25  	 v_name := replace (v_name, '#', '\#');
 26  	 v_name := replace (v_name, '$', '\$');
 27  	 v_name := replace (v_name, '~', '\~');
 28  	 v_name := replace (v_name, '?', '\?');
 29  	 v_name := replace (v_name, '.', '\.');
 30  	 v_name := replace (v_name, '|', '\|');
 31  	 v_name := replace (v_name, '!', '\!');
 32  -- added \ in front of _:
 33  	 v_name := replace (v_name, '_', '\_');
 34  	 v_searchstring := v_searchstring || '(definescore(' || v_name || ',discrete/100)),';
 35  	 if i.cnt > 1 then
 36  	   v_searchstring := v_searchstring || '(definescore(' || v_name || ' ' || v_name || ',discrete/100)),';
 37  	 end if;
 38    end loop;
 39    v_searchstring := rtrim (v_searchstring, ',') || '),or,add)';
 40    return v_searchstring;
 41  end format_input;
 42  /

Function created.

SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> insert all
  2    into test_names values (1,1,1,'rajesh kumar')
  3    into test_names values (1,1,2,'rajesh reddy')
  4    into test_names values (1,1,3,'rajesh reddy')
  5    into test_names values (1,2,1,'rajesh rajesh')
  6    into test_names values (1,2,2,'rajesh kumar rajesh')
  7    into test_names values (1,3,1,'rajesh rajesh kumar reddy')
  8    into test_names values (1,4,1,'rajesh dravid')
  9    into test_names values (1,5,1,'rajesh rajesh kumar reddy')
 10    into test_names values (1,1,4,'rajesh rajesh_ kumar sachin')
 11    into test_names values (1,1,5,'rajesh sachin? ganguly')
 12    into test_names values (1,1,7,'rajesh sachin')
 13    into test_names values (1,1,8,'rajesh sachin')
 14    into test_names values (1,1,9,'rajesh sachin')
 15  select * from dual
 16  /

13 rows created.

SCOTT@orcl_12.1.0.2.0> begin
  2    ctx_ddl.create_preference ('test_mcds', 'multi_column_datastore');
  3    ctx_ddl.set_attribute ('test_mcds', 'columns', 'fullname');
  4    ctx_ddl.set_attribute ('test_mcds', 'delimiter', 'newline');
  5    ctx_ddl.create_preference ('test_lex', 'basic_lexer');
  6  -- added underscore to printjoins:
  7    ctx_ddl.set_attribute ('test_lex', 'printjoins', '&*%(){}%#$~?.|!_');
  8    ctx_ddl.set_attribute ('test_lex', 'punctuations', ' ');
  9  end;
 10  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> create index test_names_idx on test_names (fullname)
  2    indextype is ctxsys.context
  3    parameters
  4  	 ('datastore  test_mcds
  5  	   lexer      test_lex')
  6  /

Index created.

SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh rajesh'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> select format_input (:inputname) from dual
  2  /

FORMAT_INPUT(:INPUTNAME)
--------------------------------------------------------------------------------
definemerge(((definescore(rajesh,discrete/100)),(definescore(rajesh rajesh,discr
ete/100))),or,add)


1 row selected.

SCOTT@orcl_12.1.0.2.0> select id, id2, id3
  2  from   test_names
  3  where  contains (fullname, format_input (:inputname)) >= 2
  4  -- added 3 lines below:
  5  or     (contains (fullname, format_input (:inputname)) >= 1 and
  6  	     regexp_count (' ' || fullname|| ' ', ' ' || regexp_substr (:inputname, '[^ ]+') || ' ') >= 2 and
  7  	     regexp_count (' ' || :inputname || ' ', regexp_substr (' ' || :inputname, '[^ ]+') || ' ') >= 2)
  8  /

        ID        ID2        ID3
---------- ---------- ----------
         1          2          1
         1          2          2
         1          3          1
         1          5          1

4 rows selected.
Re: need two token matching query [message #667963 is a reply to message #667937] Sun, 28 January 2018 00:23 Go to previous messageGo to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
Thanks Barbara Boehmer ,

The suggested solution is failing in the below scenario.
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh( rajesh'

SCOTT@orcl_12.1.0.2.0> select id, id2, id3
2 from test_names
3 where contains (fullname, format_input (:inputname)) >= 2
4 -- added 3 lines below:
5 or (contains (fullname, format_input (:inputname)) >= 1 and
6 regexp_count (' ' || fullname|| ' ', ' ' || regexp_substr (:inputname, '[^ ]+') || ' ') >= 2 and
7 regexp_count (' ' || :inputname || ' ', regexp_substr (' ' || :inputname, '[^ ]+') || ' ') >= 2)
8 /

Ora-12725:unmatched parentheses in regular expression.

Regards,
Rajesh.
Re: need two token matching query [message #667964 is a reply to message #667963] Sun, 28 January 2018 01:54 Go to previous messageGo to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
Thanks Barbara Boehmer ,

The suggested solution is failing in the below scenarios.

SCOTT@orcl_12.1.0.2.0> create table test_names
2 (id number,
3 id2 number,
4 id3 number,
5 fullname varchar2(500))
6 /

SCOTT@orcl_12.1.0.2.0> create or replace function format_input
2 (p_inputname in varchar2)
3 return varchar2
4 as
5 v_searchstring varchar2(4000);
6 v_name varchar2(100);
7 begin
8 v_searchstring := 'definemerge((';
9 for i in
10 (select name,
11 count(*) cnt
12 from (select regexp_substr (p_inputname, '[^ ]+', 1, level) name
13 from dual
14 connect by level <= regexp_count (p_inputname, ' +') + 1)
15 group by name)
16 loop
17 v_name := i.name;
18 v_name := replace (v_name, '(', '\(');
19 v_name := replace (v_name, ')', '\)');
20 v_name := replace (v_name, '&', '\&');
21 v_name := replace (v_name, '*', '\*');
22 v_name := replace (v_name, '%', '\%');
23 v_name := replace (v_name, '{', '\{');
24 v_name := replace (v_name, '}', '\}');
25 v_name := replace (v_name, '#', '\#');
26 v_name := replace (v_name, '$', '\$');
27 v_name := replace (v_name, '~', '\~');
28 v_name := replace (v_name, '?', '\?');
29 -- add \ in front of each character:
30 v_name := replace (v_name, '.', '\.');
31 v_name := replace (v_name, '|', '\|');
32 v_name := replace (v_name, '!', '\!');
32 v_name := replace (v_name, '>', '\>');
32 v_name := replace (v_name, '<', '\<');
32 v_name := replace (v_name, '_', '\_');
33 v_searchstring := v_searchstring || '(definescore(' || v_name || ',discrete/100)),';
34 if i.cnt > 1 then
35 v_searchstring := v_searchstring || '(definescore(' || v_name || ' ' || v_name || ',discrete/100)),';
36 end if;
37 end loop;
38 v_searchstring := rtrim (v_searchstring, ',') || '),or,add)';
39 return v_searchstring;
40 end format_input;
41 /

Function created.



SCOTT@orcl_12.1.0.2.0> insert all
2 into test_names values (1,1,1,'rajesh kumar')
3 into test_names values (1,1,2,'rajesh reddy')
4 into test_names values (1,1,3,'rajesh reddy')
5 into test_names values (1,2,1,'rajesh rajesh')
6 into test_names values (1,2,2,'rajesh kumar rajesh')
7 into test_names values (1,3,1,'rajesh rajesh kumar reddy')
8 into test_names values (1,4,1,'rajesh dravid')
9 into test_names values (1,5,1,'rajesh rajesh kumar reddy')
10 into test_names values (1,1,4,'rajesh rajesh_ kumar sachin')
11 into test_names values (1,1,5,'rajesh sachin? ganguly')
12 into test_names values (1,1,7,'rajesh sachin')
13 into test_names values (1,1,8,'rajesh sachin')
14 into test_names values (1,1,9,'rajesh sachin')
15 into test_names values (1,1,10,'sachin rajesh kumar rajesh')
16 select * from dual
17 /



SCOTT@orcl_12.1.0.2.0> begin
2 ctx_ddl.create_preference ('test_mcds', 'multi_column_datastore');
3 ctx_ddl.set_attribute ('test_mcds', 'columns', 'fullname');
4 ctx_ddl.set_attribute ('test_mcds', 'delimiter', 'newline');
5 ctx_ddl.create_preference ('test_lex', 'basic_lexer');
6 -- add characters to printjoins:
7 ctx_ddl.set_attribute ('test_lex', 'printjoins', '&*%(){}%#$~?.|!><_');
8 -- remove characters from punctuations:
9 ctx_ddl.set_attribute ('test_lex', 'punctuations', ' ');
10 end;
11 /


SCOTT@orcl_12.1.0.2.0> create index test_names_idx on test_names (fullname)
2 indextype is ctxsys.context
3 parameters
4 ('datastore test_mcds
5 lexer test_lex')
6 /

Index created.



SCOTT@orcl_12.1.0.2.0> insert all
2 into test_names values (1,1,1,'rajesh kumar')
3 into test_names values (1,1,2,'rajesh reddy')
4 into test_names values (1,1,3,'rajesh reddy')
5 into test_names values (1,2,1,'rajesh rajesh')
6 into test_names values (1,2,2,'rajesh kumar rajesh')
7 into test_names values (1,3,1,'rajesh rajesh kumar reddy')
8 into test_names values (1,4,1,'rajesh dravid')
9 into test_names values (1,5,1,'rajesh rajesh kumar reddy')
10 into test_names values (1,1,4,'rajesh rajesh_ kumar sachin')
11 into test_names values (1,1,5,'rajesh sachin? ganguly')
12 into test_names values (1,1,7,'rajesh sachin')
13 into test_names values (1,1,8,'rajesh sachin')
14 into test_names values (1,1,9,'rajesh sachin')
15 into test_names values (1,1,10,'sachin rajesh kumar rajesh')
16 into test_names values (1,1,11,'sachin kumar rajesh reddy dravid rajesh')
16 select * from dual
17 /


SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh( rajesh'

SCOTT@orcl_12.1.0.2.0> select id, id2, id3
2 from test_names
3 where contains (fullname, format_input (:inputname)) >= 2
4 -- added 3 lines below:
5 or (contains (fullname, format_input (:inputname)) >= 1 and
6 regexp_count (' ' || fullname|| ' ', ' ' || regexp_substr (:inputname, '[^ ]+') || ' ') >= 2 and
7 regexp_count (' ' || :inputname || ' ', regexp_substr (' ' || :inputname, '[^ ]+') || ' ') >= 2)
8 /

Ora-12725:unmatched parentheses in regular expression.

SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh rajesh'
SCOTT@orcl_12.1.0.2.0> select id, id2, id3
2 from test_names
3 where contains (fullname, format_input (:inputname)) >= 2
4 -- added 3 lines below:
5 or (contains (fullname, format_input (:inputname)) >= 1 and
6 regexp_count (' ' || fullname|| ' ', ' ' || regexp_substr (:inputname, '[^ ]+') || ' ') >= 2 and
7 regexp_count (' ' || :inputname || ' ', regexp_substr (' ' || :inputname, '[^ ]+') || ' ') >= 2)
8 /

id id2 id3
--------------
1 2 1
1 3 1
1 5 1
1 2 2

i am not getting the expected output, i need to get the output including
id id2 id3
--------------
1 2 1
1 3 1
1 5 1
1 2 2
1 1 10
1 1 11

Regards,
Rajesh
Re: need two token matching query [message #667985 is a reply to message #667964] Mon, 29 January 2018 17:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Part of the purpose of asking you to post a copy and paste of a run of the code from SQL*Plus on your system is to be certain of what you have actually run. In your previous post, what you posted did not produce the results you claim to have gotten, so it was obviously faked. In this post you have posted two sets on insert statements, one of them after index creation without any synchronization. It is obvious that you are just copying and pasting mismatched pieces in random order, much of it just taken from what I have posted with some modifications. This defeats the whole purpose and makes it very time-consuming to sort out the problem. If you continue to do this, I may choose not to waste my time responding in the future. You need to stop doing this and post an actual copy and paste of a complete test run from your system, as I have done below.

In the below, I have added a function to escape the special characters in the variable for usage with regular expressions. I have also made modifications to the query.

SCOTT@orcl_12.1.0.2.0> create table test_names
  2    (id	  number,
  3  	id2	  number,
  4  	id3	  number,
  5  	fullname  varchar2(500))
  6  /

Table created.

SCOTT@orcl_12.1.0.2.0> create or replace function format_input
  2    (p_inputname in varchar2)
  3    return	       varchar2
  4  as
  5    v_searchstring  varchar2(4000);
  6    v_name	       varchar2(100);
  7  begin
  8    v_searchstring := 'definemerge((';
  9    for i in
 10  	 (select name, count(*) cnt
 11  	  from	 (select  regexp_substr (p_inputname, '[^ ]+', 1, level) name
 12  		  from	  dual
 13  		  connect by level <= regexp_count (p_inputname, ' +') + 1)
 14  	  group  by name)
 15    loop
 16  	 v_name := i.name;
 17  	 v_name := replace (v_name, '(', '\(');
 18  	 v_name := replace (v_name, ')', '\)');
 19  	 v_name := replace (v_name, '&', '\&');
 20  	 v_name := replace (v_name, '*', '\*');
 21  	 v_name := replace (v_name, '%', '\%');
 22  	 v_name := replace (v_name, '{', '\{');
 23  	 v_name := replace (v_name, '}', '\}');
 24  	 v_name := replace (v_name, '#', '\#');
 25  	 v_name := replace (v_name, '$', '\$');
 26  	 v_name := replace (v_name, '~', '\~');
 27  	 v_name := replace (v_name, '?', '\?');
 28  	 v_name := replace (v_name, '.', '\.');
 29  	 v_name := replace (v_name, '|', '\|');
 30  	 v_name := replace (v_name, '!', '\!');
 31  	 v_name := replace (v_name, '>', '\>');
 32  	 v_name := replace (v_name, '<', '\<');
 33  	 v_name := replace (v_name, '_', '\_');
 34  	 v_searchstring := v_searchstring || '(definescore(' || v_name || ',discrete/100)),';
 35  	 if i.cnt > 1 then
 36  	   v_searchstring := v_searchstring || '(definescore(' || v_name || ' ' || v_name || ',discrete/100)),';
 37  	 end if;
 38    end loop;
 39    v_searchstring := rtrim (v_searchstring, ',') || '),or,add)';
 40    return v_searchstring;
 41  end format_input;
 42  /

Function created.

SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> insert all
  2    into test_names values (1,1,1,'rajesh kumar')
  3    into test_names values (1,1,2,'rajesh reddy')
  4    into test_names values (1,1,3,'rajesh reddy')
  5    into test_names values (1,2,1,'rajesh rajesh')
  6    into test_names values (1,2,2,'rajesh kumar rajesh')
  7    into test_names values (1,3,1,'rajesh rajesh kumar reddy')
  8    into test_names values (1,4,1,'rajesh dravid')
  9    into test_names values (1,5,1,'rajesh rajesh kumar reddy')
 10    into test_names values (1,1,4,'rajesh rajesh_ kumar sachin')
 11    into test_names values (1,1,5,'rajesh sachin? ganguly')
 12    into test_names values (1,1,7,'rajesh sachin')
 13    into test_names values (1,1,8,'rajesh sachin')
 14    into test_names values (1,1,9,'rajesh sachin')
 15    into test_names values (1,1,10,'sachin rajesh kumar rajesh')
 16    into test_names values (1,1,11,'sachin kumar rajesh reddy dravid rajesh')
 17  select * from dual
 18  /

15 rows created.

SCOTT@orcl_12.1.0.2.0> begin
  2    ctx_ddl.create_preference ('test_mcds', 'multi_column_datastore');
  3    ctx_ddl.set_attribute ('test_mcds', 'columns', 'fullname');
  4    ctx_ddl.set_attribute ('test_mcds', 'delimiter', 'newline');
  5    ctx_ddl.create_preference ('test_lex', 'basic_lexer');
  6    ctx_ddl.set_attribute ('test_lex', 'printjoins', '&*%(){}%#$~?.|!><_');
  7    ctx_ddl.set_attribute ('test_lex', 'punctuations', ' ');
  8  end;
  9  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> create index test_names_idx on test_names (fullname)
  2    indextype is ctxsys.context
  3    parameters
  4  	 ('datastore  test_mcds
  5  	   lexer      test_lex')
  6  /

Index created.

SCOTT@orcl_12.1.0.2.0> -- added function to escape the special characters for usage with regular expressions:
SCOTT@orcl_12.1.0.2.0> create or replace function esc_spec_chars
  2    (p_inputname in varchar2)
  3    return	       varchar2
  4  as
  5    v_name	       varchar2(100);
  6  begin
  7    v_name := p_inputname;
  8    v_name := replace (v_name, '(', '\(');
  9    v_name := replace (v_name, ')', '\)');
 10    v_name := replace (v_name, '&', '\&');
 11    v_name := replace (v_name, '*', '\*');
 12    v_name := replace (v_name, '%', '\%');
 13    v_name := replace (v_name, '{', '\{');
 14    v_name := replace (v_name, '}', '\}');
 15    v_name := replace (v_name, '#', '\#');
 16    v_name := replace (v_name, '$', '\$');
 17    v_name := replace (v_name, '~', '\~');
 18    v_name := replace (v_name, '?', '\?');
 19    v_name := replace (v_name, '.', '\.');
 20    v_name := replace (v_name, '|', '\|');
 21    v_name := replace (v_name, '!', '\!');
 22    v_name := replace (v_name, '_', '\_');
 23    return v_name;
 24  end esc_spec_chars;
 25  /

Function created.

SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh( rajesh'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> -- modified query:
SCOTT@orcl_12.1.0.2.0> select id, id2, id3
  2  from   test_names
  3  where  contains (fullname, format_input (:inputname)) >= 2
  4  or     (contains (fullname, format_input (:inputname)) >= 1 and
  5  	     regexp_count
  6  	       (' ' || esc_spec_chars(fullname)|| ' ',
  7  		' ' || regexp_substr (esc_spec_chars(:inputname), '[^ ]+') || ' ') >= 2 and
  8  	     regexp_count
  9  	       (' ' || esc_spec_chars(:inputname) || ' ',
 10  		regexp_substr (esc_spec_chars(:inputname), '[^ ]+') || ' ') >= 2)
 11  /

no rows selected

SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh rajesh'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> select id, id2, id3
  2  from   test_names
  3  where  contains (fullname, format_input (:inputname)) >= 2
  4  or     (contains (fullname, format_input (:inputname)) >= 1 and
  5  	     regexp_count
  6  	       (' ' || esc_spec_chars(fullname)|| ' ',
  7  		' ' || regexp_substr (esc_spec_chars(:inputname), '[^ ]+') || ' ') >= 2 and
  8  	     regexp_count
  9  	       (' ' || esc_spec_chars(:inputname) || ' ',
 10  		regexp_substr (esc_spec_chars(:inputname), '[^ ]+') || ' ') >= 2)
 11  /

        ID        ID2        ID3
---------- ---------- ----------
         1          2          1
         1          2          2
         1          3          1
         1          5          1
         1          1         10
         1          1         11

6 rows selected.
Re: need two token matching query [message #667987 is a reply to message #667985] Tue, 30 January 2018 01:34 Go to previous messageGo to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
Thanks Barbara Boehmer ,

I am extremely sorry for copy and paste.I am implementing the suggested solution in another system,from that system i cant send the same.So i am re typing every thing here.And also in last message I typed two sets of insert statements,which is my mistake.I tried to edit that ,but I am unable to edit the same after submitting the reply.

Once again thanks for your reply,It helped me lot to implement the solution.And also I learned the new concepts how to use context index and all.

Regards,
Rajesh.
Re: need two token matching query [message #668015 is a reply to message #667987] Wed, 31 January 2018 01:09 Go to previous messageGo to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
Thanks Barbara Boehmer ,

Thanks for your support,this may be the last post on this topic .
.Please suggest any changes need for the below.

SCOTT@orcl_12.1.0.2.0> create table test_names
2 (id number,
3 id2 number,
4 id3 number,
5 fullname varchar2(500))
6 /

Table created.

SCOTT@orcl_12.1.0.2.0> create or replace function format_input
2 (p_inputname in varchar2)
3 return varchar2
4 as
5 v_searchstring varchar2(4000);
6 v_name varchar2(100);
7 begin
8 v_searchstring := 'definemerge((';
9 for i in
10 (select name, count(*) cnt
11 from (select regexp_substr (p_inputname, '[^ ]+', 1, level) name
12 from dual
13 connect by level <= regexp_count (p_inputname, ' +') + 1)
14 group by name)
15 loop
16 v_name := i.name;
17 v_name := replace (v_name, '(', '\(');
18 v_name := replace (v_name, ')', '\)');
19 v_name := replace (v_name, '&', '\&');
20 v_name := replace (v_name, '*', '\*');
21 v_name := replace (v_name, '%', '\%');
22 v_name := replace (v_name, '{', '\{');
23 v_name := replace (v_name, '}', '\}');
24 v_name := replace (v_name, '#', '\#');
25 v_name := replace (v_name, '$', '\$');
26 v_name := replace (v_name, '~', '\~');
27 v_name := replace (v_name, '?', '\?');
28 v_name := replace (v_name, '.', '\.');
29 v_name := replace (v_name, '|', '\|');
30 v_name := replace (v_name, '!', '\!');
31 v_name := replace (v_name, '>', '\>');
32 v_name := replace (v_name, '<', '\<');
33 v_name := replace (v_name, '_', '\_');
34 v_searchstring := v_searchstring || '(definescore(' || v_name || ',discrete/100)),';
35 if i.cnt > 1 then
36 v_searchstring := v_searchstring || '(definescore(' || v_name || ' ' || v_name || ',discrete/100)),';
37 end if;
38 end loop;
39 v_searchstring := rtrim (v_searchstring, ',') || '),or,add)';
40 return v_searchstring;
41 end format_input;
42 /

Function created.

SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> insert all
2 into test_names values (1,1,1,'rajesh kumar')
3 into test_names values (1,1,2,'rajesh reddy')
4 into test_names values (1,1,3,'rajesh reddy')
5 into test_names values (1,2,1,'rajesh rajesh')
6 into test_names values (1,2,2,'rajesh kumar rajesh')
7 into test_names values (1,3,1,'rajesh rajesh kumar reddy')
8 into test_names values (1,4,1,'rajesh dravid')
9 into test_names values (1,5,1,'rajesh rajesh kumar reddy')
10 into test_names values (1,1,4,'rajesh rajesh_ kumar sachin')
11 into test_names values (1,1,5,'rajesh sachin? ganguly')
12 into test_names values (1,1,7,'rajesh sachin')
13 into test_names values (1,1,8,'rajesh sachin')
14 into test_names values (1,1,9,'rajesh sachin')
15 into test_names values (1,1,10,'sachin rajesh kumar rajesh')
16 into test_names values (1,1,11,'sachin kumar rajesh reddy dravid rajesh')
17 into test_names values (1,1,12,'khan rajesh( kumar rajesh(')
18 into test_names values (1,1,13,'zaheer rajesh( rajesh(')
19 select * from dual
20 /

17 rows created.

SCOTT@orcl_12.1.0.2.0> begin
2 ctx_ddl.create_preference ('test_mcds', 'multi_column_datastore');
3 ctx_ddl.set_attribute ('test_mcds', 'columns', 'fullname');
4 ctx_ddl.set_attribute ('test_mcds', 'delimiter', 'newline');
5 ctx_ddl.create_preference ('test_lex', 'basic_lexer');
6 ctx_ddl.set_attribute ('test_lex', 'printjoins', '&*%(){}%#$~?.|!><_');
7 ctx_ddl.set_attribute ('test_lex', 'punctuations', ' ');
8 end;
9 /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> create index test_names_idx on test_names (fullname)
2 indextype is ctxsys.context
3 parameters
4 ('datastore test_mcds
5 lexer test_lex')
6 /

Index created.

SCOTT@orcl_12.1.0.2.0> -- added function to escape the special characters for usage with regular expressions:
SCOTT@orcl_12.1.0.2.0> create or replace function esc_spec_chars
2 (p_inputname in varchar2)
3 return varchar2
4 as
5 v_name varchar2(100);
6 begin
7 v_name := p_inputname;
8 v_name := replace (v_name, '(', '\(');
9 v_name := replace (v_name, ')', '\)');
10 v_name := replace (v_name, '&', '\&');
11 v_name := replace (v_name, '*', '\*');
12 v_name := replace (v_name, '%', '\%');
13 v_name := replace (v_name, '{', '\{');
14 v_name := replace (v_name, '}', '\}');
15 v_name := replace (v_name, '#', '\#');
16 v_name := replace (v_name, '$', '\$');
17 v_name := replace (v_name, '~', '\~');
18 v_name := replace (v_name, '?', '\?');
19 v_name := replace (v_name, '.', '\.');
20 v_name := replace (v_name, '|', '\|');
21 v_name := replace (v_name, '!', '\!');
22 v_name := replace (v_name, '_', '\_');
23 return v_name;
24 end esc_spec_chars;
25 /

Function created.

SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh( rajesh'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> -- modified query:
SCOTT@orcl_12.1.0.2.0> select id, id2, id3
2 from test_names
3 where contains (fullname, format_input (:inputname)) >= 2
4 or (contains (fullname, format_input (:inputname)) >= 1 and
5 regexp_count
6 (' ' || esc_spec_chars(fullname)|| ' ',
7 ' ' || regexp_substr (esc_spec_chars(:inputname), '[^ ]+') || ' ') >= 2 and
8 regexp_count
9 (' ' || esc_spec_chars(:inputname) || ' ',
10 regexp_substr (esc_spec_chars(:inputname), '[^ ]+') || ' ') >= 2)
11 /

no rows selected

SCOTT@orcl_12.1.0.2.0> exec :inputname := 'dhoni rajesh rajesh'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> select id, id2, id3
2 from test_names
3 where contains (fullname, format_input (:inputname)) >= 2
4 or (contains (fullname, format_input (:inputname)) >= 1 and
5 regexp_count
6 (' ' || esc_spec_chars(fullname)|| ' ',
7 ' ' || regexp_substr (esc_spec_chars(:inputname), '[^ ]+') || ' ') >= 2 and
8 regexp_count
9 (' ' || esc_spec_chars(:inputname) || ' ',
10 regexp_substr (esc_spec_chars(:inputname), '[^ ]+') || ' ') >= 2)
11 /

ID ID2 ID3
---------- ---------- ----------
1 2 1
1 3 1
1 5 1


3 rows selected.

But i need to get the below records also .(if any two tokens matching with the given name we need to get the output)
expected output:
ID ID2 ID3
---------- ---------- ----------
1 2 1
1 3 1
1 5 1
1 2 2
1 1 10
1 1 11


SCOTT@orcl_12.1.0.2.0> exec :inputname := 'dhoni rajesh( rajesh('

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> select id, id2, id3
2 from test_names
3 where contains (fullname, format_input (:inputname)) >= 2
4 or (contains (fullname, format_input (:inputname)) >= 1 and
5 regexp_count
6 (' ' || esc_spec_chars(fullname)|| ' ',
7 ' ' || regexp_substr (esc_spec_chars(:inputname), '[^ ]+') || ' ') >= 2 and
8 regexp_count
9 (' ' || esc_spec_chars(:inputname) || ' ',
10 regexp_substr (esc_spec_chars(:inputname), '[^ ]+') || ' ') >= 2)
11 /

no rows selected

But i need to get the below records also .(if any two tokens matching with the given name we need to get the output)
expected output:
ID ID2 ID3
---------- ---------- ----------
1 1 12
1 1 13


Regards,
Rajesh.
Re: need two token matching query [message #668028 is a reply to message #668015] Wed, 31 January 2018 18:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The requirements seem to be getting more complex in terms of either matching 2 different tokens or matching 2 tokens if there are 2 duplicates in the input string and 2 duplicates of the same token in the fullname and including special characters. It keeps getting more convoluted in terms of escaping the special characters for the context index and nested regular expressions. You don't seem to need any of the features of Oracle Text. So, at this point, I think it is appropriate to re-examine and re-think the whole process. I suggest the following without a context index or nested regular expressions or functions. Instead it uses one standard index concatenating spaces to fullname and LIKE which uses the index to produce a small result set, then uses INSTR and such on that small result set to narrow that result set further. I have added some additional data to create a larger data set and used timing and autotrace to show that the results are returned quickly using the index.

-- your table and your latest data set that you provided:
SCOTT@orcl_12.1.0.2.0> create table test_names
  2    (id	  number,
  3  	id2	  number,
  4  	id3	  number,
  5  	fullname  varchar2(500))
  6  /

Table created.

SCOTT@orcl_12.1.0.2.0> insert all
  2    into test_names values (1,1,1,'rajesh kumar')
  3    into test_names values (1,1,2,'rajesh reddy')
  4    into test_names values (1,1,3,'rajesh reddy')
  5    into test_names values (1,2,1,'rajesh rajesh')
  6    into test_names values (1,2,2,'rajesh kumar rajesh')
  7    into test_names values (1,3,1,'rajesh rajesh kumar reddy')
  8    into test_names values (1,4,1,'rajesh dravid')
  9    into test_names values (1,5,1,'rajesh rajesh kumar reddy')
 10    into test_names values (1,1,4,'rajesh rajesh_ kumar sachin')
 11    into test_names values (1,1,5,'rajesh sachin? ganguly')
 12    into test_names values (1,1,7,'rajesh sachin')
 13    into test_names values (1,1,8,'rajesh sachin')
 14    into test_names values (1,1,9,'rajesh sachin')
 15    into test_names values (1,1,10,'sachin rajesh kumar rajesh')
 16    into test_names values (1,1,11,'sachin kumar rajesh reddy dravid rajesh')
 17    into test_names values (1,1,12,'khan rajesh( kumar rajesh(')
 18    into test_names values (1,1,13,'zaheer rajesh( rajesh(')
 19  select * from dual
 20  /

17 rows created.

SCOTT@orcl_12.1.0.2.0> commit
  2  /

Commit complete.

-- extra data for testing:
SCOTT@orcl_12.1.0.2.0> insert into test_names (id, id2, id3, fullname)
  2  select object_id, data_object_id, namespace, 'rajesh ' || object_name
  3  from   all_objects
  4  /

92177 rows created.

SCOTT@orcl_12.1.0.2.0> commit
  2  /

Commit complete.

-- standard index concatenating spaces to fullname (not a text index):
SCOTT@orcl_12.1.0.2.0> -- standard index:
SCOTT@orcl_12.1.0.2.0> create index test_idx on test_names (' ' || fullname || ' ', id, id2, id3)
  2  /

Index created.

-- gather statistics on the index to help the optimizer choose the best execution plan
-- (you may be able to skip this step if your database is set to gather statistics automatically or use sampling):
SCOTT@orcl_12.1.0.2.0> exec dbms_stats.gather_table_stats (user, 'TEST_NAMES')

PL/SQL procedure successfully completed.

-- queries with different variables, showing timing and showing the execution plan on the first run:
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh kumar sachin'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> set timing on
SCOTT@orcl_12.1.0.2.0> set autotrace on explain
SCOTT@orcl_12.1.0.2.0> select  distinct fullname, id, id2, id3
  2  from    (select  fullname, id, id2, id3, name, ncnt,
  3  		      count (*) over (partition by fullname, id, id2, id3) fcnt
  4  	      from    test_names,
  5  		      (select  name, count (*) ncnt
  6  		       from    (select	regexp_substr (:inputname, '[^ ]+', 1, level) name
  7  				from	dual
  8  				connect by level <= regexp_count (:inputname, ' +') + 1)
  9  		       group   by name)
 10  	      where   ' ' || fullname || ' ' like '% ' || name || ' %')
 11  where   fcnt >= 2
 12  or      (ncnt >= 2 and instr (' ' || fullname || ' ', ' ' || name || ' ', 2) > 0)
 13  /

FULLNAME                                         ID        ID2        ID3
---------------------------------------- ---------- ---------- ----------
rajesh kumar                                      1          1          1
rajesh kumar rajesh                               1          2          2
rajesh rajesh kumar reddy                         1          3          1
rajesh rajesh kumar reddy                         1          5          1
rajesh rajesh_ kumar sachin                       1          1          4
rajesh sachin                                     1          1          7
rajesh sachin                                     1          1          8
rajesh sachin                                     1          1          9
sachin kumar rajesh reddy dravid rajesh           1          1         11
sachin rajesh kumar rajesh                        1          1         10

10 rows selected.

Elapsed: 00:00:00.48

Execution Plan
----------------------------------------------------------
Plan hash value: 1624550815

----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |  4610 |   603K|       |   472   (1)| 00:00:01 |
|   1 |  SORT UNIQUE NOSORT                 |            |  4610 |   603K|       |   472   (1)| 00:00:01 |
|*  2 |   VIEW                              |            |  4610 |   603K|       |   331   (1)| 00:00:01 |
|   3 |    WINDOW SORT                      |            |  4610 |   693K|   784K|   331   (1)| 00:00:01 |
|   4 |     NESTED LOOPS                    |            |  4610 |   693K|       |   172   (1)| 00:00:01 |
|   5 |      VIEW                           |            |     1 |    79 |       |     3  (34)| 00:00:01 |
|   6 |       HASH GROUP BY                 |            |     1 |    66 |       |     3  (34)| 00:00:01 |
|   7 |        VIEW                         |            |     1 |    66 |       |     2   (0)| 00:00:01 |
|*  8 |         CONNECT BY WITHOUT FILTERING|            |       |       |       |            |          |
|   9 |          FAST DUAL                  |            |     1 |       |       |     2   (0)| 00:00:01 |
|  10 |      TABLE ACCESS BY INDEX ROWID    | TEST_NAMES |  4610 |   337K|       |   169   (0)| 00:00:01 |
|* 11 |       INDEX RANGE SCAN              | TEST_IDX   |   830 |       |       |     8   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FCNT">=2 OR "NCNT">=2 AND INSTR(' '||"FULLNAME"||' ',' '||"NAME"||' ',2)>0)
   8 - filter(LEVEL<= REGEXP_COUNT (:INPUTNAME,' +')+1)
  11 - access(' '||"FULLNAME"||' ' LIKE '% '||"NAME"||' %')
       filter(' '||"FULLNAME"||' ' LIKE '% '||"NAME"||' %')

SCOTT@orcl_12.1.0.2.0> set autotrace off
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'kumar sachin rajesh'

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SCOTT@orcl_12.1.0.2.0> /

FULLNAME                                         ID        ID2        ID3
---------------------------------------- ---------- ---------- ----------
rajesh kumar                                      1          1          1
rajesh kumar rajesh                               1          2          2
rajesh rajesh kumar reddy                         1          3          1
rajesh rajesh kumar reddy                         1          5          1
rajesh rajesh_ kumar sachin                       1          1          4
rajesh sachin                                     1          1          7
rajesh sachin                                     1          1          8
rajesh sachin                                     1          1          9
sachin kumar rajesh reddy dravid rajesh           1          1         11
sachin rajesh kumar rajesh                        1          1         10

10 rows selected.

Elapsed: 00:00:00.45
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh rajesh ganguly'

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SCOTT@orcl_12.1.0.2.0> /

FULLNAME                                         ID        ID2        ID3
---------------------------------------- ---------- ---------- ----------
rajesh kumar rajesh                               1          2          2
rajesh rajesh                                     1          2          1
rajesh rajesh kumar reddy                         1          3          1
rajesh rajesh kumar reddy                         1          5          1
rajesh sachin? ganguly                            1          1          5
sachin kumar rajesh reddy dravid rajesh           1          1         11
sachin rajesh kumar rajesh                        1          1         10

7 rows selected.

Elapsed: 00:00:00.57
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh sachin'

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SCOTT@orcl_12.1.0.2.0> /

FULLNAME                                         ID        ID2        ID3
---------------------------------------- ---------- ---------- ----------
rajesh rajesh_ kumar sachin                       1          1          4
rajesh sachin                                     1          1          7
rajesh sachin                                     1          1          8
rajesh sachin                                     1          1          9
sachin kumar rajesh reddy dravid rajesh           1          1         11
sachin rajesh kumar rajesh                        1          1         10

6 rows selected.

Elapsed: 00:00:00.50
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh kumar'

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SCOTT@orcl_12.1.0.2.0> /

FULLNAME                                         ID        ID2        ID3
---------------------------------------- ---------- ---------- ----------
rajesh kumar                                      1          1          1
rajesh kumar rajesh                               1          2          2
rajesh rajesh kumar reddy                         1          3          1
rajesh rajesh kumar reddy                         1          5          1
rajesh rajesh_ kumar sachin                       1          1          4
sachin kumar rajesh reddy dravid rajesh           1          1         11
sachin rajesh kumar rajesh                        1          1         10

7 rows selected.

Elapsed: 00:00:00.42
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh rajesh'

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SCOTT@orcl_12.1.0.2.0> /

FULLNAME                                         ID        ID2        ID3
---------------------------------------- ---------- ---------- ----------
rajesh kumar rajesh                               1          2          2
rajesh rajesh                                     1          2          1
rajesh rajesh kumar reddy                         1          3          1
rajesh rajesh kumar reddy                         1          5          1
sachin kumar rajesh reddy dravid rajesh           1          1         11
sachin rajesh kumar rajesh                        1          1         10

6 rows selected.

Elapsed: 00:00:00.40
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'dhoni rajesh rajesh'

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SCOTT@orcl_12.1.0.2.0> /

FULLNAME                                         ID        ID2        ID3
---------------------------------------- ---------- ---------- ----------
rajesh kumar rajesh                               1          2          2
rajesh rajesh                                     1          2          1
rajesh rajesh kumar reddy                         1          3          1
rajesh rajesh kumar reddy                         1          5          1
sachin kumar rajesh reddy dravid rajesh           1          1         11
sachin rajesh kumar rajesh                        1          1         10

6 rows selected.

Elapsed: 00:00:00.46
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'dhoni rajesh( rajesh('

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SCOTT@orcl_12.1.0.2.0> /

FULLNAME                                         ID        ID2        ID3
---------------------------------------- ---------- ---------- ----------
khan rajesh( kumar rajesh(                        1          1         12
zaheer rajesh( rajesh(                            1          1         13

2 rows selected.

Elapsed: 00:00:00.12

[Updated on: Wed, 31 January 2018 18:34]

Report message to a moderator

Re: need two token matching query [message #668029 is a reply to message #668028] Wed, 31 January 2018 18:50 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The following query with the additional INSTR condition seems to have similar times, but a better execution plan, narrowing the results to a smaller data set sooner, so it may scale better on larger data sets. I ran it on the same data and index as above.

-- queries with different variables, showing timing and execution plan on first run:
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh kumar sachin'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> set timing on
SCOTT@orcl_12.1.0.2.0> set autotrace on explain
SCOTT@orcl_12.1.0.2.0> select  distinct fullname, id, id2, id3
  2  from    (select  fullname, id, id2, id3, name, ncnt,
  3  		      count (*) over (partition by fullname, id, id2, id3) fcnt
  4  	      from    test_names,
  5  		      (select  name, count (*) ncnt
  6  		       from    (select	regexp_substr (:inputname, '[^ ]+', 1, level) name
  7  				from	dual
  8  				connect by level <= regexp_count (:inputname, ' +') + 1)
  9  		       group   by name)
 10  	      where   ' ' || fullname || ' ' like '% ' || name || ' %'
 11  	      and     instr (' ' || fullname || ' ', ' ' || name || ' ', 1) > 0)
 12  where   fcnt >= 2
 13  or      (ncnt >= 2 and instr (' ' || fullname || ' ', ' ' || name || ' ', 2) > 0)
 14  /

FULLNAME                                         ID        ID2        ID3
---------------------------------------- ---------- ---------- ----------
rajesh kumar                                      1          1          1
rajesh kumar rajesh                               1          2          2
rajesh rajesh kumar reddy                         1          3          1
rajesh rajesh kumar reddy                         1          5          1
rajesh rajesh_ kumar sachin                       1          1          4
rajesh sachin                                     1          1          7
rajesh sachin                                     1          1          8
rajesh sachin                                     1          1          9
sachin kumar rajesh reddy dravid rajesh           1          1         11
sachin rajesh kumar rajesh                        1          1         10

10 rows selected.

Elapsed: 00:00:00.68

Execution Plan
----------------------------------------------------------
Plan hash value: 1624550815

--------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |   230 | 30820 |    34   (9)| 00:00:01 |
|   1 |  SORT UNIQUE NOSORT                 |            |   230 | 30820 |    34   (9)| 00:00:01 |
|*  2 |   VIEW                              |            |   230 | 30820 |    33   (7)| 00:00:01 |
|   3 |    WINDOW SORT                      |            |   230 | 35420 |    33   (7)| 00:00:01 |
|   4 |     NESTED LOOPS                    |            |   230 | 35420 |    32   (4)| 00:00:01 |
|   5 |      VIEW                           |            |     1 |    79 |     3  (34)| 00:00:01 |
|   6 |       HASH GROUP BY                 |            |     1 |    66 |     3  (34)| 00:00:01 |
|   7 |        VIEW                         |            |     1 |    66 |     2   (0)| 00:00:01 |
|*  8 |         CONNECT BY WITHOUT FILTERING|            |       |       |            |          |
|   9 |          FAST DUAL                  |            |     1 |       |     2   (0)| 00:00:01 |
|  10 |      TABLE ACCESS BY INDEX ROWID    | TEST_NAMES |   230 | 17250 |    29   (0)| 00:00:01 |
|* 11 |       INDEX RANGE SCAN              | TEST_IDX   |    41 |       |     8   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FCNT">=2 OR "NCNT">=2 AND INSTR(' '||"FULLNAME"||' ',' '||"NAME"||' ',2)>0)
   8 - filter(LEVEL<= REGEXP_COUNT (:INPUTNAME,' +')+1)
  11 - access(' '||"FULLNAME"||' ' LIKE '% '||"NAME"||' %')
       filter(' '||"FULLNAME"||' ' LIKE '% '||"NAME"||' %' AND INSTR(' '||"FULLNAME"||'
              ',' '||"NAME"||' ',1)>0)

SCOTT@orcl_12.1.0.2.0> set autotrace off
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'kumar sachin rajesh'

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SCOTT@orcl_12.1.0.2.0> /

FULLNAME                                         ID        ID2        ID3
---------------------------------------- ---------- ---------- ----------
rajesh kumar                                      1          1          1
rajesh kumar rajesh                               1          2          2
rajesh rajesh kumar reddy                         1          3          1
rajesh rajesh kumar reddy                         1          5          1
rajesh rajesh_ kumar sachin                       1          1          4
rajesh sachin                                     1          1          7
rajesh sachin                                     1          1          8
rajesh sachin                                     1          1          9
sachin kumar rajesh reddy dravid rajesh           1          1         11
sachin rajesh kumar rajesh                        1          1         10

10 rows selected.

Elapsed: 00:00:00.51
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh rajesh ganguly'

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SCOTT@orcl_12.1.0.2.0> /

FULLNAME                                         ID        ID2        ID3
---------------------------------------- ---------- ---------- ----------
rajesh kumar rajesh                               1          2          2
rajesh rajesh                                     1          2          1
rajesh rajesh kumar reddy                         1          3          1
rajesh rajesh kumar reddy                         1          5          1
rajesh sachin? ganguly                            1          1          5
sachin kumar rajesh reddy dravid rajesh           1          1         11
sachin rajesh kumar rajesh                        1          1         10

7 rows selected.

Elapsed: 00:00:00.51
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh sachin'

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SCOTT@orcl_12.1.0.2.0> /

FULLNAME                                         ID        ID2        ID3
---------------------------------------- ---------- ---------- ----------
rajesh rajesh_ kumar sachin                       1          1          4
rajesh sachin                                     1          1          7
rajesh sachin                                     1          1          8
rajesh sachin                                     1          1          9
sachin kumar rajesh reddy dravid rajesh           1          1         11
sachin rajesh kumar rajesh                        1          1         10

6 rows selected.

Elapsed: 00:00:00.54
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh kumar'

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SCOTT@orcl_12.1.0.2.0> /

FULLNAME                                         ID        ID2        ID3
---------------------------------------- ---------- ---------- ----------
rajesh kumar                                      1          1          1
rajesh kumar rajesh                               1          2          2
rajesh rajesh kumar reddy                         1          3          1
rajesh rajesh kumar reddy                         1          5          1
rajesh rajesh_ kumar sachin                       1          1          4
sachin kumar rajesh reddy dravid rajesh           1          1         11
sachin rajesh kumar rajesh                        1          1         10

7 rows selected.

Elapsed: 00:00:00.53
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh rajesh'

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SCOTT@orcl_12.1.0.2.0> /

FULLNAME                                         ID        ID2        ID3
---------------------------------------- ---------- ---------- ----------
rajesh kumar rajesh                               1          2          2
rajesh rajesh                                     1          2          1
rajesh rajesh kumar reddy                         1          3          1
rajesh rajesh kumar reddy                         1          5          1
sachin kumar rajesh reddy dravid rajesh           1          1         11
sachin rajesh kumar rajesh                        1          1         10

6 rows selected.

Elapsed: 00:00:00.43
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'dhoni rajesh rajesh'

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SCOTT@orcl_12.1.0.2.0> /

FULLNAME                                         ID        ID2        ID3
---------------------------------------- ---------- ---------- ----------
rajesh kumar rajesh                               1          2          2
rajesh rajesh                                     1          2          1
rajesh rajesh kumar reddy                         1          3          1
rajesh rajesh kumar reddy                         1          5          1
sachin kumar rajesh reddy dravid rajesh           1          1         11
sachin rajesh kumar rajesh                        1          1         10

6 rows selected.

Elapsed: 00:00:00.51
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'dhoni rajesh( rajesh('

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SCOTT@orcl_12.1.0.2.0> /

FULLNAME                                         ID        ID2        ID3
---------------------------------------- ---------- ---------- ----------
khan rajesh( kumar rajesh(                        1          1         12
zaheer rajesh( rajesh(                            1          1         13

2 rows selected.

Elapsed: 00:00:00.12
Previous Topic: Is there a way to do a rollup on only one dimension
Next Topic: Websocket connection through Oracle PL SQL
Goto Forum:
  


Current Time: Thu Mar 28 09:50:56 CDT 2024