6 Replies Latest reply: Feb 13, 2013 3:05 AM by chris227 RSS

    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" ?
          • 2. Re: Find the closest match based on the data
            user527060
            digit by digit
            • 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