progressive relaxation techniques [message #591251] |
Thu, 25 July 2013 23:29 |
|
userora
Messages: 63 Registered: December 2012 Location: Chennai
|
Member |
|
|
Hi,
I have a records like this in a table
CREATE TABLE Name_FUZZY
(NAME VARCHAR2(3500));
INSERT INTO Name_FUZZY VALUES ('Atlas Copco Australia Pty Ltd v Max Smith Enterprises Pty Ltd');
INSERT INTO Name_FUZZY VALUES ('Australia Estates Pty Ltd v Robert Smith Australian Real Estate Co Pty Ltd');
INSERT INTO Name_FUZZY VALUES ('Australia v Smith');
My Rule part is := For these names with the reference abbreviation "versus" or one of its variants, a partial match will occur where at least one term matches within the party name to the right and left of the reference.
My Abbreviations may be these
-v-
V
v.
--v--
Versus
vs
Vs.
If I select
'Australia -v- Smith'
or
'Australia V Smith'
or
'Australia v. Smith'
or
'Australia --v-- Smith'
or
'Australia Versus Smith'
or
'Australia vs Smith'
or
'Australia Vs.Smith'
or
'Australia v Smith'
MY result should be like this ..
"Atlas Copco Australia Pty Ltd v Max Smith Enterprises Pty Ltd"
"Australia Estates Pty Ltd v Robert Smith Australian Real Estate Co Pty Ltd"
"Australia v Smith"
Actually i tried using progressive relaxation techniques in oracle 10g but i could not achieve it as dynamic result set. Please suggest me some solution .
Thanks all in advance.
|
|
|
|
|
|
Re: progressive relaxation techniques [message #591260 is a reply to message #591258] |
Fri, 26 July 2013 00:45 |
|
Michel Cadot
Messages: 68732 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote:My case never comes with "two verses" as you mentioned
There is not "2 verses" V is the initial of Smith.
I should post "Australia V Smith Ltd" with no "verse".
Still waiting for rows that are not accepted for the example you gave.
Regards
Michel
[Updated on: Fri, 26 July 2013 00:46] Report message to a moderator
|
|
|
|
Re: progressive relaxation techniques [message #591274 is a reply to message #591267] |
Fri, 26 July 2013 02:22 |
|
Michel Cadot
Messages: 68732 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
And what are "Atlas" and "Copco", 2 words too? my "V" is also a word.
So, from what you say, having "verse" or not is irrelevant, it is just noise in your question, the answer could just be:
where instr(name,'Australia') > 0 or instr(name, 'Smith') > 0
or
where name like '%Australia%' or name like '%Smith%')
in standard SQL but you should install (Con)Text option and use CONTAINS extension.
Regards
Michel
|
|
|
|
|
Re: progressive relaxation techniques [message #594038 is a reply to message #591332] |
Thu, 22 August 2013 15:57 |
|
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
The following example uses VERSUS|VS|V to select versus or vs or v. By default, everything is converted to uppercase and hyphens and periods are eliminated. I have also used ? for fuzzy searching to find similarly spelled words. It uses progressive relaxation to list the results in the order specified by the sequences, regardless of score. In order not to destroy this order, you should not order the results by score or anything else. The example below lists results in the following order:
Australia followed by versus or vs or v followed by Smith
The same as the line above but with fuzzy matching on Australia and Smith such as Australian and Smythe
Australia and versus or vs or v and Smith, in any order with any number of words inbetween
The same as the line above but with fuzzy matching on Australia and Smith such as Australian and Smythe
Australia and Smith, in any order with any number of words inbetween, without versus or vs or v
The same as the line above but with fuzzy matching on Australia and Smith such as Australian and Smythe
Australia or Smith, not both with any number of other words
The same as the line above but with fuzzy matching on Australia and Smith such as Australian and Smythe
The following is just an example that should enable you to adjust it to what you need. If you have any trouble doing so, then please provide sample data and ordered results as I have done, indicating what rows you want in what order and any results that you do not want included in the results set.
SCOTT@orcl12c> -- table:
SCOTT@orcl12c> CREATE TABLE Name_FUZZY
2 (NAME VARCHAR2(3500))
3 /
Table created.
SCOTT@orcl12c> -- data for demonstration:
SCOTT@orcl12c> INSERT ALL
2 INTO Name_FUZZY VALUES
3 ('Atlas Copco Australia Pty Ltd v Max Smith Enterprises Pty Ltd')
4 INTO Name_FUZZY VALUES
5 ('Australia Estates Pty Ltd v Robert Smith Australian Real Estate Co Pty Ltd')
6 INTO Name_FUZZY
7 VALUES ('Australia v Smith')
8 INTO Name_FUZZY VALUES
9 ('Atlas Copco Australia Pty Ltd Max Smith Enterprises Pty Ltd')
10 INTO Name_FUZZY VALUES
11 ('Australia Estates Pty Ltd Robert Smith Australian Real Estate Co Pty Ltd')
12 INTO Name_FUZZY
13 VALUES ('Australia Smith')
14 INTO Name_FUZZY
15 VALUES ('Australia')
16 INTO Name_FUZZY
17 VALUES ('Smith')
18 INTO Name_FUZZY
19 VALUES ('Australian')
20 INTO Name_FUZZY
21 VALUES ('Australian versus Smith')
22 INTO Name_FUZZY
23 VALUES ('Australian vs. Smith')
24 INTO Name_FUZZY
25 VALUES ('Australian --v-- Smith')
26 INTO Name_FUZZY
27 VALUES ('Smythe')
28 SELECT * FROM DUAL
29 /
13 rows created.
SCOTT@orcl12c> -- context index:
SCOTT@orcl12c> CREATE INDEX name_idx ON name_fuzzy (name) INDEXTYPE IS CTXSYS.CONTEXT
2 /
Index created.
SCOTT@orcl12c> -- query that uses progressive relaxation and fuzzy:
SCOTT@orcl12c> COLUMN name FORMAT A60 WORD_WRAPPED
SCOTT@orcl12c> SELECT score(1), name FROM Name_FUZZY
2 WHERE CONTAINS
3 (name,
4 '<query>
5 <textquery>
6 <progression>
7 <seq>Australia VERSUS|VS|V Smith</seq>
8 <seq>?Australia VERSUS|VS|V ?Smith</seq>
9 <seq>Australia AND (VERSUS|VS|V) AND Smith</seq>
10 <seq>?Australia AND (VERSUS|VS|V) AND ?Smith</seq>
11 <seq>Australia AND Smith</seq>
12 <seq>?Australia AND ?Smith</seq>
13 <seq>Australia OR Smith</seq>
14 <seq>?Australia OR ?Smith</seq>
15 </progression>
16 </textquery>
17 </query>',
18 1) > 0
19 /
SCORE(1) NAME
---------- ------------------------------------------------------------
88 Australia v Smith
88 Australian vs. Smith
88 Australian --v-- Smith
76 Australian versus Smith
63 Atlas Copco Australia Pty Ltd v Max Smith Enterprises Pty
Ltd
63 Australia Estates Pty Ltd v Robert Smith Australian Real
Estate Co Pty Ltd
38 Atlas Copco Australia Pty Ltd Max Smith Enterprises Pty Ltd
38 Australia Estates Pty Ltd Robert Smith Australian Real
Estate Co Pty Ltd
38 Australia Smith
13 Australia
13 Smith
1 Australian
1 Smythe
13 rows selected.
|
|
|