Home » Server Options » Text & interMedia » progressive relaxation techniques (oracle 10g)
progressive relaxation techniques [message #591251] Thu, 25 July 2013 23:29 Go to next message
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 #591252 is a reply to message #591251] Thu, 25 July 2013 23:40 Go to previous messageGo to next message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
Sorry i didn't add code which i tried using this technique.So,now i added
SELECT score(1),name FROM Name_FUZZY
WHERE CONTAINS (name, '
<query>
   <textquery>
     <progression>
       <seq>Australia v Smith</seq>
       <seq>?Australia or Smith</seq>
     </progression>
   </textquery>
</query>', 1) > 0 ORDER BY score(1) DESC;
Re: progressive relaxation techniques [message #591254 is a reply to message #591251] Fri, 26 July 2013 00:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
MY result should be like this ..

select name from Name_FUZZY;


A test case must contain rows you want AN D rows you don't want.

What about "Australia V Smith Ltd -v- Michel"?

Regards
Michel
Re: progressive relaxation techniques [message #591258 is a reply to message #591254] Fri, 26 July 2013 00:27 Go to previous messageGo to next message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
My case never comes with "two verses" as you mentioned
Quote:
"Australia V Smith Ltd -v- Michel"

it appears with only one verses which may have different abbreviations as mentioned before.
As of now i have hardcoded the values in my query..but i may get different kinds of name like this "Australia verses Michel".
Re: progressive relaxation techniques [message #591260 is a reply to message #591258] Fri, 26 July 2013 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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 #591267 is a reply to message #591260] Fri, 26 July 2013 02:03 Go to previous messageGo to next message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
Hi Michel..

Actually Australia is one word and Smith is one word if i give Australia v Smith i should get all the records related to Australia and Smith with can be either side of my name field which means a partial match will occur where at least one term matches within the party name to the right and left of the reference.
Re: progressive relaxation techniques [message #591274 is a reply to message #591267] Fri, 26 July 2013 02:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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 #591311 is a reply to message #591274] Fri, 26 July 2013 05:07 Go to previous messageGo to next message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
Thanks for your reply..I hope you yet my rule part hasn't been understood Sad
Re: progressive relaxation techniques [message #591332 is a reply to message #591251] Fri, 26 July 2013 07:52 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
I have never heard of progressive relaxation technique when it comes to Oracle. It sounds like some zen meditation thingy.

Re: progressive relaxation techniques [message #594038 is a reply to message #591332] Thu, 22 August 2013 15:57 Go to previous message
Barbara Boehmer
Messages: 9077
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.











Previous Topic: Oracle 11.2 text search using contains operator for % wildcard
Next Topic: Oracle text and locking issue
Goto Forum:
  


Current Time: Thu Mar 28 08:23:36 CDT 2024