interMedia - How to make a ranking?

From: Markus Schwabe <mschwabe_at_gscout.de>
Date: Wed, 23 Feb 2000 16:52:39 +0100
Message-ID: <8913k0$7g$1@news4.muc.eurocyber.net>

Hello,

I have the following table (ctx_test) with primary key on ID:

 Name                            Null?    Type
 ------------------------------- -------- ----
 NAME                                     VARCHAR2(32)
 ID                              NOT NULL NUMBER

I have an index on this table:

CREATE INDEX ctx_test_idx ON ctx_test(name) INDEXTYPE IS CTXSYS.CONTEXT; Now I search for a name with FUZZY:

SELECT ID, Name
FROM ctx_test
WHERE CONTAINS (Name, '?HAKEMESER') > 0;

What I want now is a scoring or ranking like this:

       ID NAME                       RANKING
--------- --------------------  -----------
      390 HACKEMESSER        80
    26971 HACKEMESSER       80
    64485 HAGEMOSER           72
      172 HAGEMOSER             72
    38521 HAUMESSER           60
   23395 HAMERS                    12
    51808 HASSEMER               11
    55895 HAEMKER               9
    62345 HARMES                 8

...to find out which name is as close as possible to my fuzzy-input. This is NOT possible with the interMedia-function SCORE() !!

Does anyone has a function or procedure to calculate this ranking? (I have something heard about the LEVENSHTEIN-DISTANCE).

I work with 8.1.6 (Oracle 8i Second Edition) on Solaris.

Thank you in advance
Markus Received on Wed Feb 23 2000 - 09:52:39 CST