Home » Server Options » Text & interMedia » Matching query (oracle 11g)
| Matching query [message #664240] |
Mon, 10 July 2017 00:31  |
 |
mvrkr44
Messages: 132
Registered: December 2012
|
Senior Member |
|
|
Table:
-----
Create table test(id number,name varchar2(250));
insert into test(1,'rajesh kumar reddy');
insert into test(2,'rajesh kumar mudimela');
question:I need a query which will give both records if i pass the string as "rajesh mudimela reddy".
if any minimum two exact words is matching then needs to give the result
my input parameter is "rajesh mudimela reddy"
output should be: 1,rajesh kumar reddy
2,rajesh kumar mudimela
[Updated on: Mon, 10 July 2017 00:32] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Matching query [message #664244 is a reply to message #664243] |
Mon, 10 July 2017 01:27  |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
This does not change the fact it is wrong and a source of problems like your current one.
If you normalize your data, there is no more problem and performances will be far better.
Wait for Barbara to help you with this problem as she did with your previous one (she is our expert in this domain).
I notice you did not feedback and thank her for the time and help she gave you.
[Updated on: Mon, 10 July 2017 01:29] Report message to a moderator |
|
|
|
|
|
| Re: Matching query [message #664249 is a reply to message #664240] |
Mon, 10 July 2017 06:01 |
 |
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl_12.1.0.2.0> Create table test(id number,name varchar2(250))
2 /
Table created.
SCOTT@orcl_12.1.0.2.0> insert all
2 into test values(1,'rajesh kumar reddy')
3 into test values(2,'rajesh kumar mudimela')
4 into test values(3,'rajesh')
5 into test values(4,'mudimela')
6 into test values(5,'reddy')
7 into test values(6,'rajesh mudimela reddy')
8 into test values(7,'somebody else')
9 select * from dual
10 /
7 rows created.
SCOTT@orcl_12.1.0.2.0> create index test_name_idx on test (name) indextype is ctxsys.context
2 /
Index created.
SCOTT@orcl_12.1.0.2.0> variable the_string varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :the_string := 'rajesh mudimela reddy'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> column name format a45
SCOTT@orcl_12.1.0.2.0> select matching_names, id, name
2 from (select sum (score(1)/100) as matching_names, test.id, test.name
3 from test,
4 (select regexp_substr (:the_string, '[^ ]+', 1, rownum) single_name
5 from dual
6 connect by level <= regexp_count (:the_string || ' ', ' ')) names
7 where contains (test.name, 'definescore(' || names.single_name || ', discrete)', 1) > 0
8 group by test.id, test.name)
9 where matching_names >= 2
10 order by matching_names desc
11 /
MATCHING_NAMES ID NAME
-------------- ---------- ---------------------------------------------
3 6 rajesh mudimela reddy
2 1 rajesh kumar reddy
2 2 rajesh kumar mudimela
3 rows selected.
|
|
|
|
|
|
| Re: Matching query [message #664251 is a reply to message #664250] |
Mon, 10 July 2017 06:33 |
|
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following is a bit simpler.
SCOTT@orcl_12.1.0.2.0> Create table test(id number,name varchar2(250))
2 /
Table created.
SCOTT@orcl_12.1.0.2.0> insert all
2 into test values(1,'rajesh kumar reddy')
3 into test values(2,'rajesh kumar mudimela')
4 into test values(3,'rajesh')
5 into test values(4,'mudimela')
6 into test values(5,'reddy')
7 into test values(6,'rajesh mudimela reddy')
8 into test values(7,'somebody else')
9 select * from dual
10 /
7 rows created.
SCOTT@orcl_12.1.0.2.0> create index test_name_idx on test (name) indextype is ctxsys.context
2 /
Index created.
SCOTT@orcl_12.1.0.2.0> variable the_string varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :the_string := 'rajesh mudimela reddy'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> column name format a45
SCOTT@orcl_12.1.0.2.0> select count(*) as matching_names, test.id, test.name
2 from test,
3 (select regexp_substr (:the_string, '[^ ]+', 1, rownum) single_name
4 from dual
5 connect by level <= regexp_count (:the_string || ' ', ' ')) names
6 where contains (test.name, names.single_name, 1) > 0
7 group by test.id, test.name
8 having count(*) >= 2
9 order by matching_names desc
10 /
MATCHING_NAMES ID NAME
-------------- ---------- ---------------------------------------------
3 6 rajesh mudimela reddy
2 1 rajesh kumar reddy
2 2 rajesh kumar mudimela
3 rows selected.
|
|
|
|
| Re: Matching query [message #664252 is a reply to message #664251] |
Mon, 10 July 2017 06:41 |
|
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
This could also be done without Oracle Text and contains, using instr instead. This would be fine for small amounts of data, but the text index and contains should be faster on large tables.
SCOTT@orcl_12.1.0.2.0> Create table test(id number,name varchar2(250))
2 /
Table created.
SCOTT@orcl_12.1.0.2.0> insert all
2 into test values(1,'rajesh kumar reddy')
3 into test values(2,'rajesh kumar mudimela')
4 into test values(3,'rajesh')
5 into test values(4,'mudimela')
6 into test values(5,'reddy')
7 into test values(6,'rajesh mudimela reddy')
8 into test values(7,'somebody else')
9 select * from dual
10 /
7 rows created.
SCOTT@orcl_12.1.0.2.0> variable the_string varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :the_string := 'rajesh mudimela reddy'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> column name format a45
SCOTT@orcl_12.1.0.2.0> select count(*) as matching_names, test.id, test.name
2 from test,
3 (select ' ' || regexp_substr (:the_string, '[^ ]+', 1, rownum) || ' ' single_name
4 from dual
5 connect by level <= regexp_count (:the_string || ' ', ' ')) names
6 where instr (' ' || test.name || ' ', names.single_name, 1) > 0
7 group by test.id, test.name
8 having count(*) >= 2
9 order by matching_names desc
10 /
MATCHING_NAMES ID NAME
-------------- ---------- ---------------------------------------------
3 6 rajesh mudimela reddy
2 1 rajesh kumar reddy
2 2 rajesh kumar mudimela
3 rows selected.
|
|
|
|
|
|
|
|
|
|
| Re: Matching query [message #664280 is a reply to message #664265] |
Mon, 10 July 2017 23:41 |
|
mvrkr44
Messages: 132
Registered: December 2012
|
Senior Member |
|
|
Thanks Barbara,
I used the below query,I will check today with huge sample data and let you know the performance
select count(*) as matching_names, test.id, test.name
from test,
(select ' ' || regexp_substr (:the_string, '[^ ]+', 1, rownum) || ' ' single_name
from dual
connect by level <= regexp_count (:the_string || ' ', ' ')) names
where instr (' ' || test.name || ' ', names.single_name, 1) > 0
group by test.id, test.name
having count(*) >= 2
order by matching_names desc
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Jan 08 19:01:45 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
