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: 117
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: 61
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: 2687
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: 8779
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: 117
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: 8779
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: 117
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: 117
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: 8779
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: 117
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: 2687
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: 8779
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: 117
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 message
Barbara Boehmer
Messages: 8779
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.
Previous Topic: i need to know hows this happening?
Next Topic: ORA-01502: index or partition of such index is in usable
Goto Forum:
  


Current Time: Thu Dec 14 04:54:04 CST 2017

Total time taken to generate the page: 0.02791 seconds