6 Replies Latest reply on Feb 13, 2013 9:05 AM by chris227

    Find the closest match based on the data

    user527060
      Hi,
      I am using oracle 11g.

      I wanted to find out if there is a way to get a closest match for a numeric value against the database.

      Here is an example

      incoming string is 001163677011212

      and out of the two values below which is the closet match.
      001163677001212
      001163677001228


      Thanks,
        • 1. Re: Find the closest match based on the data
          Marcus Rangel
          What would be the criteria for "closest match" ?
          • 3. Re: Find the closest match based on the data
            Solomon Yakobson
            Assuming strings are numeric:
            with t as (
                       select '001163677001212' val from dual union all
                       select '001163677001228' from dual
                      )
            select  val
              from  (
                     select  val,
                             row_number() over(order by abs(val - 001163677011212)) rn
                       from  t
                    )
              where rn = 1
            /
            
            VAL
            ---------------
            001163677001228
            
            SQL> 
            SY.
            • 4. Re: Find the closest match based on the data
              Frank Kulash
              Hi,
              user527060 wrote:
              Hi,
              I am using oracle 11g.

              I wanted to find out if there is a way to get a closest match for a numeric value against the database.

              Here is an example

              incoming string is 001163677011212

              and out of the two values below which is the closet match.
              001163677001212
              001163677001228


              Thanks,
              The functions in the UTL_MATCH package might help you.

              For example:
              WITH     got_r_num     AS
              (
                   SELECT  str
                   ,     RANK () OVER ( ORDER BY  utl_match.edit_distance ( str
                                                                         , '001163677011212'
                                                       )
                                  )  AS r_num
                   FROM    table_x
              )
              SELECT     str
              FROM     got_r_num
              WHERE     r_num     = 1
              ;
              Of course, it depends on what you mean by "closest match" and "digit by digit".
              Please post CREATE TABLE and INSERT statements for a few sets of sample data, and the results you want from each set, given the same input parameter (such as '001163677011212').
              See the forum FAQ {message:id=9360002}
              • 5. Re: Find the closest match based on the data
                Peter vd Zwan
                Hi,

                Below query is comparing the strings digit for digit and selects the one with the most matching digits:
                with A as 
                (
                select '001163677001212' val from dual union all
                select '001163677001228' from dual
                )
                ,COMP AS
                (
                SELECT
                  '001163677011212' VAL
                FROM
                  DUAL
                )
                ,CNTR AS
                (
                SELECT
                  LEVEL L
                FROM
                  COMP
                CONNECT BY
                  LEVEL <= LENGTH(VAL)
                )
                , B AS
                (
                SELECT
                  A.VAL
                  ,SUM(CASE WHEN SUBSTR(A.VAL,CNTR.L,1) = SUBSTR(COMP.VAL,CNTR.L,1) THEN 1 ELSE 0 END) COMMON_DIGITS
                  
                FROM
                  A
                  ,CNTR
                  ,COMP
                
                GROUP BY
                  A.VAL
                  ,COMP.VAL
                
                ORDER BY
                  COMMON_DIGITS DESC
                )
                
                SELECT
                  *
                FROM
                  B
                WHERE
                  ROWNUM = 1
                ;
                
                VAL             COMMON_DIGITS
                --------------- -------------
                001163677001212            14 
                Regards,

                Peter
                • 6. Re: Find the closest match based on the data
                  chris227
                  If it doesnt matter if a difference at this first place has a different weight than one at the end
                  with data as (
                      select '001163677001212' s from dual union all
                      select '001163677001228' from dual
                  )
                  , matches as (
                  select
                   s
                  ,rank() over (order by 
                      UTL_MATCH.EDIT_DISTANCE (
                         s 
                         ,'001163677011212'
                      )
                  ) d
                  from data
                  )
                  
                  select
                   s, d
                  from matches
                  where
                  d = 1
                  
                  S     D
                  001163677001212     1