Home » Server Options » Text & interMedia » Matching query (oracle 11g)
Matching query [message #664240] |
Mon, 10 July 2017 00:31 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/fc0c4f1effffe68370d04746da979732?s=64&d=mm&r=g) |
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68653 Registered: March 2007 Location: Nanterre, France, http://...
|
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9096 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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9096 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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9096 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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/fc0c4f1effffe68370d04746da979732?s=64&d=mm&r=g) |
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: Sun May 19 19:29:23 CDT 2024
|