Home » Developer & Programmer » Forms » Asking for search algorithm (Oracle Forms 6i, PL/SQL)
|
Re: Asking for search algorithm [message #667811 is a reply to message #667810] |
Thu, 18 January 2018 00:13 ![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/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Those "OK" options sound like "The string", so you could try with SOUNDEX function. It
Oracle
returns a character string containing the phonetic representation of char . This function lets you compare words that are spelled differently, but sound alike in English.
Or, somewhat more advanced, UTL_MATCH.JARO_WINKLER_SIMILARITY which
Oracle
calculates the measure of agreement between string-1 and string-2, returning a value between 0 (no match) and 100 (perfect match)
Maybe Oracle Text offers such a capabilities, but I didn't use it so I can't comment on that.
Also, there might be some other options. Hopefully, someone else will suggest them.
|
|
|
Re: Asking for search algorithm [message #667813 is a reply to message #667811] |
Thu, 18 January 2018 00:58 ![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) |
![](/forum/images/custom_avatars/193058.png) |
NDKA739125
Messages: 33 Registered: January 2018
|
Member |
|
|
@Littlefoot
SOUNDEX look great, but I think It could not solve my problem, and UTL_MATCH neither.
I type from my keyboard a string, the forms will look into the column TNAME in EMP table, and pick up the list which contains the similar name. I could not use LIKE, because it's slow. Beside, the input would be very complicated.
Here is, the problem.
When I type :
ALEXENDER ONORE (wrong fisrt name ALEXENDER) or
ONORE ALEXANDER (Reverse name) or
ONOR ALAXANDEZ (wrong and reverse name) the forms will give me a suggestion list, include ALEXANDER ONORE, which is the right one.
P/s: I'm thinking about using 'fuzzy', but it requires an index, how can i create an index in forms ?
[Updated on: Thu, 18 January 2018 01:01] Report message to a moderator
|
|
|
|
Re: Asking for search algorithm [message #667816 is a reply to message #667814] |
Thu, 18 January 2018 03:59 ![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: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
-- table and data for demonstration:
SCOTT@orcl_12.1.0.2.0> create table emp (tname varchar2(60))
2 /
Table created.
SCOTT@orcl_12.1.0.2.0> insert all
2 into emp values ('ALEXANDER ONORE')
3 into emp values ('ALEXANDRA ONORE')
4 into emp values ('ALEXANDER ONOFRE')
5 into emp values ('ALEXANDRA ONOFRE')
6 into emp values ('ALEX DOE')
7 into emp values ('JOHN ONORE')
8 into emp values ('JOHN DOE')
9 select * from dual
10 /
7 rows created.
-- Oracle Text Context index:
SCOTT@orcl_12.1.0.2.0> create index emp_tname_idx on emp (tname)
2 indextype is ctxsys.context
3 parameters ('sync(on commit)')
4 /
Index created.
-- searches using Oracle Text CONTAINS and FUZZY and SOUNDEX (!) and JARO_WINKLER (for odering)
-- that return results in the order of the closest match:
SCOTT@orcl_12.1.0.2.0> variable string varchar2(60)
SCOTT@orcl_12.1.0.2.0> exec :string := 'ALEXENDER ONORE'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> select tname
2 from emp
3 where contains
4 (tname,
5 '(fuzzy(' || replace (:string, ' ', ',1,5000,W) and fuzzy(') || ',1,5000,W)) or
6 (!(' || replace (:string, ' ', ') and !(') || '))', 1) > 0
7 order by score(1) desc, utl_match.jaro_winkler_similarity (tname, :string) desc
8 /
TNAME
------------------------------------------------------------
ALEXANDER ONORE
ALEXANDER ONOFRE
ALEXANDRA ONORE
ALEXANDRA ONOFRE
4 rows selected.
SCOTT@orcl_12.1.0.2.0> exec :string := 'ONORE ALEXANDER'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> /
TNAME
------------------------------------------------------------
ALEXANDER ONORE
ALEXANDRA ONORE
ALEXANDER ONOFRE
ALEXANDRA ONOFRE
4 rows selected.
SCOTT@orcl_12.1.0.2.0> exec :string := 'ONOR ALAXANDEZ'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> /
TNAME
------------------------------------------------------------
ALEXANDRA ONORE
ALEXANDER ONORE
ALEXANDRA ONOFRE
ALEXANDER ONOFRE
4 rows selected.
|
|
|
|
Re: Asking for search algorithm [message #667857 is a reply to message #667821] |
Mon, 22 January 2018 03:51 ![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) |
![](/forum/images/custom_avatars/193058.png) |
NDKA739125
Messages: 33 Registered: January 2018
|
Member |
|
|
@Barbara Boehmer.
@Littlefoot
Tks a lot, the fuzzy search worked well, but I wanna improve my search box to be more robust and could catch up as much cases as it could.
For example, when Im using Fuzzy:
Input: MARIA AP ANTONOVA -> no result found, but the correct string is just : MARIA EP ANTONOVA.
So I wanna add MATCHES into the query so It could catch 'MARIA' and also 'ANTONOVA' for the better result ('AP' for another name is fine). Regarding to this problem, I found this link: https://docs.oracle.com/cd/B28359_01/text.111/b28303/quicktour.htm#i1008390
Quote:2.5.1 Steps for Creating a Classification Application
The following example steps you through defining simple categories, creating a CTXRULE index, and using MATCHES to classify documents.
But I cannot create index. There was an error, indextype does not exist .
Thanks in advance
P/s: Another question, the input is a string inclue 'space', how can I split this string by space ? .
I'm thinking about using REGEXP_SUBSTR .
[Updated on: Mon, 22 January 2018 04:16] Report message to a moderator
|
|
|
Re: Asking for search algorithm [message #667859 is a reply to message #667857] |
Mon, 22 January 2018 04:46 ![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: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
The ctxrule index and matches are for document classification, which is something entirely different and not what you need here.
You need to leave a space between each term/word/name in the input string, so you don't need to use regexp_substr or anything else to remove it.
Soundex requires that the first letter match and fuzzy does not apply to words or names less than three characters.
What you can do is change the AND's to ACCUM's in the query, so that more results will be returned, but still with the closer matches first. Please see the revised demonstration below.
SCOTT@orcl_12.1.0.2.0> create table emp (tname varchar2(60))
2 /
Table created.
SCOTT@orcl_12.1.0.2.0> insert all
2 into emp values ('ALEXANDER ONORE')
3 into emp values ('ALEXANDRA ONORE')
4 into emp values ('ALEXANDER ONOFRE')
5 into emp values ('ALEXANDRA ONOFRE')
6 into emp values ('ALEX DOE')
7 into emp values ('JOHN ONORE')
8 into emp values ('JOHN DOE')
9 into emp values ('MARIA EP ANTONOVA')
10 select * from dual
11 /
8 rows created.
SCOTT@orcl_12.1.0.2.0> create index emp_tname_idx on emp (tname)
2 indextype is ctxsys.context
3 parameters ('sync(on commit)')
4 /
Index created.
SCOTT@orcl_12.1.0.2.0> variable string varchar2(60)
SCOTT@orcl_12.1.0.2.0> exec :string := 'MARIA AP ANTONOVA'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> select tname
2 from emp
3 where contains
4 (tname,
5 '(fuzzy(' || replace (:string, ' ', ',1,5000,W) ACCUM fuzzy(') || ',1,5000,W)) OR
6 (!(' || replace (:string, ' ', ') ACCUM !(') || '))', 1) > 0
7 order by score(1) desc, utl_match.jaro_winkler_similarity (tname, :string) desc
8 /
TNAME
------------------------------------------------------------
MARIA EP ANTONOVA
1 row selected.
SCOTT@orcl_12.1.0.2.0> exec :string := 'ALEXENDER ONORE'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> /
TNAME
------------------------------------------------------------
ALEXANDER ONORE
ALEXANDRA ONORE
ALEXANDER ONOFRE
ALEXANDRA ONOFRE
JOHN ONORE
ALEX DOE
6 rows selected.
SCOTT@orcl_12.1.0.2.0> exec :string := 'ONORE ALEXANDER'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> /
TNAME
------------------------------------------------------------
ALEXANDER ONORE
ALEXANDRA ONORE
ALEXANDER ONOFRE
ALEXANDRA ONOFRE
JOHN ONORE
ALEX DOE
6 rows selected.
SCOTT@orcl_12.1.0.2.0> exec :string := 'ONOR ALAXANDEZ'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> /
TNAME
------------------------------------------------------------
ALEXANDRA ONORE
ALEXANDER ONORE
ALEXANDRA ONOFRE
ALEXANDER ONOFRE
JOHN ONORE
ALEX DOE
6 rows selected.
[Updated on: Mon, 22 January 2018 04:48] Report message to a moderator
|
|
|
|
|
|
Re: Asking for search algorithm [message #667965 is a reply to message #667907] |
Sun, 28 January 2018 20:07 ![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) |
![](/forum/images/custom_avatars/193058.png) |
NDKA739125
Messages: 33 Registered: January 2018
|
Member |
|
|
@Barbara Boehmer
@Littlefoot
New update is coming .
My teacher said, in the reality, sometimes, people has mistaskes about space. So, the new request is:
"CUTTING all the spaces between first and last name before SEARCHING".
So, I'll put a replace on the input like Replace(:INPUT,' ','').
Ex:
The correct name _ ALEXANDRA ONORE
Input:
ALEXANDRAONRA
ALAXANDREONOR
ONOELAXENDRA
ONOREALAXENDA
ONORE
ONR
==> Result found
Solution:
I saw that "[..] contains (tname, [..]", tname is a column, so I can not remove the space from a column, "[..] contains (replace(tname,' '','') [..]". I persuaded my teacher that is it possible to create a new column wich contains non-space-names, so we can use CONTAINS and FUZZY on that, and the answer is no, it is a giant databse with a million records. So I can not do anything. Is "FUZZY" could allow me to do that without creating a new non-space-name column ?
[Updated on: Sun, 28 January 2018 22:13] Report message to a moderator
|
|
|
Re: Asking for search algorithm [message #667986 is a reply to message #667965] |
Mon, 29 January 2018 22:07 ![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: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
You can use a multi_column_datastore to index virtual columns without creating any new columns in the table. In the following demonstration, I have indexed the names with spaces, the names without spaces, and the names in reverse order without spaces. I have then demonstrated searches with or without spaces in the input string, using the same query as before with fuzzy and soundex and jaro_winkler (for ordering).
SCOTT@orcl_12.1.0.2.0> create table emp (tname varchar2(60))
2 /
Table created.
SCOTT@orcl_12.1.0.2.0> insert all
2 into emp values ('ALEXANDER ONORE')
3 into emp values ('ALEXANDRA ONORE')
4 into emp values ('ALEXANDER ONOFRE')
5 into emp values ('ALEXANDRA ONOFRE')
6 into emp values ('ALEX DOE')
7 into emp values ('JOHN ONORE')
8 into emp values ('JOHN DOE')
9 into emp values ('MARIA EP ANTONOVA')
10 select * from dual
11 /
8 rows created.
SCOTT@orcl_12.1.0.2.0> -- multi_column_datastore that indexes virtual columns without spaces:
SCOTT@orcl_12.1.0.2.0> begin
2 ctx_ddl.create_preference ('name_ds', 'multi_column_datastore');
3 ctx_ddl.set_attribute
4 ('name_ds',
5 'columns',
6 'tname,
7 replace(tname,'' '', '''') nospaces,
8 substr(tname,instr(tname,'' '',-1)+1)||substr(tname,1,instr(tname,'' '')-1) backwards');
9 ctx_ddl.set_attribute ('name_ds', 'delimiter', 'newline');
10 end;
11 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> -- index that uses multi_column_datastore in parameters:
SCOTT@orcl_12.1.0.2.0> create index emp_tname_idx on emp (tname) indextype is ctxsys.context
2 parameters
3 ('datastore name_ds
4 sync (on commit)')
5 /
Index created.
SCOTT@orcl_12.1.0.2.0> -- tokens resulting from indexing:
SCOTT@orcl_12.1.0.2.0> select token_text from dr$emp_tname_idx$i
2 /
TOKEN_TEXT
----------------------------------------------------------------
ALEX
ALEXANDER
ALEXANDERONOFRE
ALEXANDERONORE
ALEXANDRA
ALEXANDRAONOFRE
ALEXANDRAONORE
ALEXDOE
ANTONOVA
ANTONOVAMARIA
DOE
DOEALEX
DOEJOHN
EP
JOHN
JOHNDOE
JOHNONORE
MARIA
MARIAEPANTONOVA
ONOFRE
ONOFREALEXANDER
ONOFREALEXANDRA
ONORE
ONOREALEXANDER
ONOREALEXANDRA
ONOREJOHN
26 rows selected.
SCOTT@orcl_12.1.0.2.0> -- example searches without spaces and with spaces using same query:
SCOTT@orcl_12.1.0.2.0> variable string varchar2(60)
SCOTT@orcl_12.1.0.2.0> exec :string := 'ALEXANDRAONRA'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> select tname
2 from emp
3 where contains
4 (tname,
5 '(fuzzy(' || replace (:string, ' ', ',1,5000,W) ACCUM fuzzy(') || ',1,5000,W)) or
6 (!(' || replace (:string, ' ', ') ACCUM !(') || '))', 1) > 0
7 order by score(1) desc, utl_match.jaro_winkler_similarity (tname, :string) desc
8 /
TNAME
------------------------------------------------------------
ALEXANDRA ONORE
ALEXANDRA ONOFRE
ALEXANDER ONORE
ALEXANDER ONOFRE
4 rows selected.
SCOTT@orcl_12.1.0.2.0> exec :string := 'ALAXANDREONOR'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> /
TNAME
------------------------------------------------------------
ALEXANDRA ONORE
ALEXANDRA ONOFRE
ALEXANDER ONORE
ALEXANDER ONOFRE
4 rows selected.
SCOTT@orcl_12.1.0.2.0> exec :string := 'ONOELAXENDRA'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> /
TNAME
------------------------------------------------------------
ALEXANDRA ONORE
ALEXANDER ONORE
ALEXANDRA ONOFRE
ALEXANDER ONOFRE
4 rows selected.
SCOTT@orcl_12.1.0.2.0> exec :string := 'ONOREALAXENDA'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> /
TNAME
------------------------------------------------------------
ALEXANDRA ONORE
ALEXANDRA ONOFRE
ALEXANDER ONORE
ALEXANDER ONOFRE
4 rows selected.
SCOTT@orcl_12.1.0.2.0> exec :string := 'ONR'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> /
TNAME
------------------------------------------------------------
JOHN ONORE
ALEXANDER ONORE
ALEXANDRA ONORE
ALEXANDRA ONOFRE
ALEXANDER ONOFRE
5 rows selected.
SCOTT@orcl_12.1.0.2.0> exec :string := 'ALEXANDRA ONRA'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> /
TNAME
------------------------------------------------------------
ALEXANDRA ONORE
ALEXANDRA ONOFRE
ALEXANDER ONORE
ALEXANDER ONOFRE
ALEX DOE
JOHN ONORE
6 rows selected.
SCOTT@orcl_12.1.0.2.0> exec :string := 'ONORE ALAXENDA'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> /
TNAME
------------------------------------------------------------
ALEXANDRA ONORE
ALEXANDER ONORE
ALEXANDRA ONOFRE
ALEXANDER ONOFRE
JOHN ONORE
ALEX DOE
6 rows selected.
|
|
|
|
|
Re: Asking for search algorithm [message #668025 is a reply to message #668017] |
Wed, 31 January 2018 09:15 ![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/136107.jpg) |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
NDKA739125 wrote on Tue, 30 January 2018 23:38@Barbara Boehmer
Its incredible, thanks for your help, bro.
When I try to compile the code, there was an error: "PLS-00201: indentifier 'TEN_KD' must be declared. I'm using Oracle SQL Developper.
Is "multi_column_datastore" a procedure ? Should I put it into the Porcedures tab ? When I create a new Procedure in this tab, the init code be like:
create or replace procedure multi_column_datastore as
begin
null;
end multi_column_datastore;
Is that normal ? Please guide me .
Do you have a problem reading the code Barbara posted?
SCOTT@orcl_12.1.0.2.0> -- multi_column_datastore that indexes virtual columns without spaces:
SCOTT@orcl_12.1.0.2.0> begin
2 ctx_ddl.create_preference ('name_ds', 'multi_column_datastore');
3 ctx_ddl.set_attribute
4 ('name_ds',
5 'columns',
6 'tname,
7 replace(tname,'' '', '''') nospaces,
8 substr(tname,instr(tname,'' '',-1)+1)||substr(tname,1,instr(tname,'' '')-1) backwards');
9 ctx_ddl.set_attribute ('name_ds', 'delimiter', 'newline');
10 end;
11 /
PL/SQL procedure successfully completed.
|
|
|
Re: Asking for search algorithm [message #668027 is a reply to message #668017] |
Wed, 31 January 2018 10:36 ![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: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
NDKA739125 wrote on Tue, 30 January 2018 23:38@Barbara Boehmer
Its incredible, thanks for your help, bro.
When I try to compile the code, there was an error: "PLS-00201: indentifier 'TEN_KD' must be declared. I'm using Oracle SQL Developper.
Is "multi_column_datastore" a procedure ? Should I put it into the Porcedures tab ? When I create a new Procedure in this tab, the init code be like:
create or replace procedure multi_column_datastore as
begin
null;
end multi_column_datastore;
Is that normal ? Please guide me :d.
I am a woman, so I am not your "bro", but I appreciate the sentiment.
I don't know where the 'TEN_KD' came from. You need to post a copy and paste of a run from your system, complete with line numbers and error messages.
You should be running it from SQL*Plus, not SQL Developer, to avoid any problems due to using SQL Developer.
A multi_column_datastore is an Oracle Text feature. Since you were apparently able to use my previous code, including an Oracle Text context index, I presume you have Oracle Text properly installed. You may need to ensure that you have appropriate privileges to create a multi_column_datastore by using the ctx_ddl procedure. You may need to have the ctxsys user grant the ctxapp role to whatever user you are running this from or grant execute on the ctx_ddl procedure directly. The following are examples of doing this for the user scott.
CTXSYS@orcl_12.1.0.2.0> grant ctxapp to scott;
Grant succeeded.
CTXSYS@orcl_12.1.0.2.0> grant execute on ctx_ddl to scott;
Grant succeeded.
You should not attempt to create a procedure named multi_column_datastore, just use the code that I provided. The following is what you need to run to create the multi_column_datastore and use it in your index. I have provided a copy wihtout line numbers, so that you can copy and paste it directly.
-- create a multi_column_datastore that indexes virtual columns without spaces:
begin
ctx_ddl.create_preference ('name_ds', 'multi_column_datastore');
ctx_ddl.set_attribute
('name_ds',
'columns',
'tname,
replace(tname,'' '', '''') nospaces,
substr(tname,instr(tname,'' '',-1)+1)||substr(tname,1,instr(tname,'' '')-1) backwards');
ctx_ddl.set_attribute ('name_ds', 'delimiter', 'newline');
end;
/
-- create an index that uses the multi_column_datastore above:
create index emp_tname_idx on emp (tname) indextype is ctxsys.context
parameters
('datastore name_ds
sync (on commit)')
/
|
|
|
|
|
|
|
Re: Asking for search algorithm [message #668404 is a reply to message #668393] |
Thu, 22 February 2018 21:55 ![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: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
The following shows that fuzzy searching on strings containing numbers using the same query does work, so you must have done something different. If you cannot figure out what you did different, then you need to post a copy and paste of a complete test run as I have done below.
SCOTT@orcl_12.1.0.2.0> create table emp (tname varchar2(60))
2 /
Table created.
SCOTT@orcl_12.1.0.2.0> insert all
2 into emp values ('45598365')
3 into emp values ('455598365')
4 into emp values ('456098365')
5 into emp values ('45598366')
6 into emp values ('46098370')
7 select * from dual
8 /
5 rows created.
SCOTT@orcl_12.1.0.2.0> create index emp_tname_idx on emp (tname) indextype is ctxsys.context
2 parameters ('sync (on commit)')
3 /
Index created.
SCOTT@orcl_12.1.0.2.0> variable string varchar2(60)
SCOTT@orcl_12.1.0.2.0> exec :string := '45598365'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> select tname
2 from emp
3 where contains
4 (tname,
5 '(fuzzy(' || replace (:string, ' ', ',1,5000,W) ACCUM fuzzy(') || ',1,5000,W)) or
6 (!(' || replace (:string, ' ', ') ACCUM !(') || '))', 1) > 0
7 order by score(1) desc, utl_match.jaro_winkler_similarity (tname, :string) desc
8 /
TNAME
------------------------------------------------------------
45598365
455598365
45598366
456098365
4 rows selected.
SCOTT@orcl_12.1.0.2.0> exec :string := '455593865'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> /
TNAME
------------------------------------------------------------
455598365
45598365
45598366
456098365
4 rows selected.
SCOTT@orcl_12.1.0.2.0> exec :string := '456098365'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> /
TNAME
------------------------------------------------------------
456098365
45598365
455598365
45598366
4 rows selected.
SCOTT@orcl_12.1.0.2.0> exec :string := '45598366'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> /
TNAME
------------------------------------------------------------
45598366
45598365
455598365
456098365
4 rows selected.
SCOTT@orcl_12.1.0.2.0> exec :string := '47098370'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> /
no rows selected
|
|
|
|
|
|
Re: Asking for search algorithm [message #668477 is a reply to message #668441] |
Tue, 27 February 2018 04:56 ![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: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
I do not get the same results, as shown below. I cannot tell from your pictures which query you are showing the result of, the text query or the other one. I don't know what tool you are running it from, but it would be better if you could test it from SQL*Plus and do a copy and paste from there.
It is possible that when you run your query, the index has not finished indexing yet. You might try waiting a bit and re-running the query. If running from a script, you can use dbms_lock.sleep between index creation and query.
SCOTT@orcl_12.1.0.2.0> create table hslt_temp (sohc varchar2(60))
2 /
Table created.
SCOTT@orcl_12.1.0.2.0> insert all
2 into hslt_temp values ('92369033')
3 into hslt_temp values ('923699033')
4 into hslt_temp values ('9876543210')
5 select * from dual
6 /
3 rows created.
SCOTT@orcl_12.1.0.2.0> create index hslttemp_sohc_idx on hslt_temp (sohc) indextype is ctxsys.context
2 parameters ('sync (on commit)')
3 /
Index created.
SCOTT@orcl_12.1.0.2.0> variable string varchar2(60)
SCOTT@orcl_12.1.0.2.0> exec :string := '92369033'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> select sohc
2 from hslt_temp
3 where contains
4 (sohc,
5 '(fuzzy(' || replace (:string, ' ', ',1,5000,W) ACCUM fuzzy(') || ',1,5000,W)) or
6 (!(' || replace (:string, ' ', ') ACCUM !(') || '))', 1) > 0
7 order by score(1) desc, utl_match.jaro_winkler_similarity (sohc, :string) desc
8 /
SOHC
------------------------------------------------------------
92369033
923699033
2 rows selected.
SCOTT@orcl_12.1.0.2.0> exec :string := '923699033'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> /
SOHC
------------------------------------------------------------
923699033
92369033
2 rows selected.
|
|
|
Re: Asking for search algorithm [message #668686 is a reply to message #668477] |
Fri, 09 March 2018 04:52 ![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) |
![](/forum/images/custom_avatars/193058.png) |
NDKA739125
Messages: 33 Registered: January 2018
|
Member |
|
|
Dear Ms,
I have tried for several days, and the result was the same,FUZZY is powerful on text but I cannot use it on number. So here is my idea. I will create a "clone" column which contains the same data as column sohc, but in the beginning of each row, I will put an "A", so the data will be like.
SOHC
-------
9236990
TK03568
will change to
SOHC
-------
A9236990
ATK03568
And for the input too, I will put an A before the input data. So when I type 9235998, the form will change my input into A9235998 and the correct result will be shown (A9236990).
I have tested my prog that way, and its work. The problem now is the creation of that clone column . Is it possible ? Please guide me.
|
|
|
Re: Asking for search algorithm [message #668695 is a reply to message #668686] |
Fri, 09 March 2018 18:05 ![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: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
You have not provided create table statements that show your data type. Oracle Text works on text columns, like varchar2 and clob, including those containing numeric values. Oracle Text will not index a column of number data type. As I have previously stated, you need to provide a complete test run, as I have done, from SQL*Plus, from start to finish, including either a create table statement or a describe of the table, so that column data types can be seen, and including error messages. Otherwise, all I can tell is that you are not doing what I am doing and not doing what you seem to be saying and can only guess at what you are actually doing.
Your column should be a text data type like varchar2 or clob not a numeric data type like number. If you cannot change that, although Oracle Text will not directly allow indexing of a numeric data type or indexing using a function like to_char or indexing of a virtual column, it will allow creation of a multi_column_datastore that applies to_char to the numeric data. You can find examples of that on this forum and in the online documentation and elsewhere on the internet. I am not going to post an example, since you have not demonstrated exactly what the situation and problem is.
|
|
|
Re: Asking for search algorithm [message #668707 is a reply to message #668695] |
Mon, 12 March 2018 01:47 ![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) |
![](/forum/images/custom_avatars/193058.png) |
NDKA739125
Messages: 33 Registered: January 2018
|
Member |
|
|
Dears Ms,
Here is my code . My column is a test data type varchar2. But Fuzzy woked the same way as "LIKE" operator on number, I don't know why .
SQL> desc test02
Name Null? Type
----------------------------------------- -------- ----------------------------
TESTNUM VARCHAR2(60)
SQL> drop index test02_testnum_idx
2 /
Index dropped.
SQL> create index test02_testnum_idx on test02 (testnum) indextype is ctxsys.context
2 parameters ('sync (on commit)')
3 /
Index created.
SQL> select * from test02
2 /
TESTNUM
------------------------------------------------------------
86120470
A7391250
SQL> exec :string := 'A7391111'
PL/SQL procedure successfully completed.
SQL> select testnum
2 from test02
3 where contains
4 (testnum,
5 '(fuzzy(' || replace (:string, ' ', ',1,5000,w) ACCUM fuzzy(') || ',1,5000,W)) or
6 (!(' || replace(:string, ' ', ') ACCUM !(') || '))', 1) > 0
7 order by score(1), utl_match.jaro_winkler_similarity(testnum, :string) desc
8 /
TESTNUM
------------------------------------------------------------
A7391250
SQL> exec :string := '86120470'
PL/SQL procedure successfully completed.
SQL> /
TESTNUM
------------------------------------------------------------
86120470
SQL> exec :string := '86120578'
PL/SQL procedure successfully completed.
SQL> /
no rows selected
|
|
|
Re: Asking for search algorithm [message #668727 is a reply to message #668707] |
Mon, 12 March 2018 17:25 ![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: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
I have copied and pasted what you posted, removed the line numbers, and run it, but do not get the same results. I get one row from the last query. I am baffled as to why there is such a difference. I have created a script below with some modifications that shows what tokens are created, what is being compared, and what the scores are. I have posted just the script first, then the results that I get after that. Please copy and paste the whole script onto your system, run it as I did, then post the complete results as I did below. Hopefully, we will see what is different and it will help narrow down the problem. Please copy and paste the whole script exactly, as I have made various changes, including using >= 0 instead of 0, so that it will show all results, regardless of score. If you get a 0 where I do not, then we will not that the problem is in the scoring. If you still do not even get a row, then it is something else.
-- test script:
drop table test02
/
create table test02 (testnum varchar2(15))
/
insert into test02 values ('86120470')
/
insert into test02 values ('A7391250')
/
create index test02_testnum_idx on test02 (testnum) indextype is ctxsys.context
/
select * from test02
/
select token_text from dr$test02_testnum_idx$i
/
variable string varchar2(15)
exec :string := 'A7391111'
column jws format 999
column string format a15
select score(1), utl_match.jaro_winkler_similarity(testnum, :string) jws, :string string, testnum
from test02
where contains
(testnum,
'(fuzzy(' || replace (:string, ' ', ',1,5000,w) ACCUM fuzzy(') || ',1,5000,W)) or
(!(' || replace(:string, ' ', ') ACCUM !(') || '))', 1) >= 0
order by score(1) desc, utl_match.jaro_winkler_similarity(testnum, :string) desc
/
exec :string := '86120470'
/
exec :string := '86120578'
/
-- run of above script:
SCOTT@orcl_12.1.0.2.0> drop table test02
2 /
Table dropped.
SCOTT@orcl_12.1.0.2.0> create table test02 (testnum varchar2(15))
2 /
Table created.
SCOTT@orcl_12.1.0.2.0> insert into test02 values ('86120470')
2 /
1 row created.
SCOTT@orcl_12.1.0.2.0> insert into test02 values ('A7391250')
2 /
1 row created.
SCOTT@orcl_12.1.0.2.0> create index test02_testnum_idx on test02 (testnum) indextype is ctxsys.context
2 /
Index created.
SCOTT@orcl_12.1.0.2.0> select * from test02
2 /
TESTNUM
---------------
86120470
A7391250
2 rows selected.
SCOTT@orcl_12.1.0.2.0> select token_text from dr$test02_testnum_idx$i
2 /
TOKEN_TEXT
----------------------------------------------------------------
86120470
A7391250
2 rows selected.
SCOTT@orcl_12.1.0.2.0> variable string varchar2(15)
SCOTT@orcl_12.1.0.2.0> exec :string := 'A7391111'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> column jws format 999
SCOTT@orcl_12.1.0.2.0> column string format a15
SCOTT@orcl_12.1.0.2.0> select score(1), utl_match.jaro_winkler_similarity(testnum, :string) jws, :string string, testnum
2 from test02
3 where contains
4 (testnum,
5 '(fuzzy(' || replace (:string, ' ', ',1,5000,w) ACCUM fuzzy(') || ',1,5000,W)) or
6 (!(' || replace(:string, ' ', ') ACCUM !(') || '))', 1) >= 0
7 order by score(1) desc, utl_match.jaro_winkler_similarity(testnum, :string) desc
8 /
SCORE(1) JWS STRING TESTNUM
---------- ---- --------------- ---------------
4 85 A7391111 A7391250
0 41 A7391111 86120470
2 rows selected.
SCOTT@orcl_12.1.0.2.0> exec :string := '86120470'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> /
SCORE(1) JWS STRING TESTNUM
---------- ---- --------------- ---------------
39 100 86120470 86120470
0 58 86120470 A7391250
2 rows selected.
SCOTT@orcl_12.1.0.2.0> exec :string := '86120578'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> /
SCORE(1) JWS STRING TESTNUM
---------- ---- --------------- ---------------
14 89 86120578 86120470
0 58 86120578 A7391250
2 rows selected.
|
|
|
Re: Asking for search algorithm [message #668730 is a reply to message #668727] |
Mon, 12 March 2018 20:16 ![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) |
![](/forum/images/custom_avatars/193058.png) |
NDKA739125
Messages: 33 Registered: January 2018
|
Member |
|
|
There are differents between your scores and mines. Here is my result, so the problem is regarding to score .
SQL> select token_text from dr$test02_testnum_idx$i
2 /
TOKEN_TEXT
----------------------------------------------------------------
86120470
A7391250
SQL> variable string varchar2(15)
SQL> exec :string := 'A7391111'
PL/SQL procedure successfully completed.
SQL> column jws format 999
SQL> column string format a15
SQL> select score(1), utl_match.jaro_winkler_similarity (testnum, :string) jws, :string string,testnum
2 from test02
3 where contains
4 (testnum,
5 '(fuzzy(' || replace (:string, ' ', ',1,5000,W) accum fuzzy(') || ',1,5000,W)) o
6 (!(' || replace (:string, ' ', ') accum !(') || '))', 1) >= 0
7 order by score(1) desc, utl_match.jaro_winkler_similarity (testnum, :string) des
8 /
SCORE(1) JWS STRING TESTNUM
---------- ---- --------------- ------------------------------------------------------------
23 85 A7391111 A7391250
0 41 A7391111 86120470
SQL> exec :string := '86120470'
PL/SQL procedure successfully completed.
SQL> /
SCORE(1) JWS STRING TESTNUM
---------- ---- --------------- ------------------------------------------------------------
39 100 86120470 86120470
0 58 86120470 A7391250
SQL> exec :string := '86120578'
PL/SQL procedure successfully completed.
SQL> /
SCORE(1) JWS STRING TESTNUM
---------- ---- --------------- ------------------------------------------------------------
0 89 86120578 86120470
0 58 86120578 A7391250
SQL>
[Updated on: Mon, 12 March 2018 20:17] Report message to a moderator
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 07 20:28:50 CDT 2024
|