This content has been marked as final.
Show 6 replies

1. Re: Find the closest match based on the data
Marcus Rangel Feb 12, 2013 5:27 PM (in response to user527060)What would be the criteria for "closest match" ? 
2. Re: Find the closest match based on the data
user527060 Feb 12, 2013 5:32 PM (in response to Marcus Rangel)digit by digit 
3. Re: Find the closest match based on the data
Solomon Yakobson Feb 12, 2013 5:47 PM (in response to user527060)Assuming strings are numeric:
SY.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>

4. Re: Find the closest match based on the data
Frank Kulash Feb 12, 2013 9:00 PM (in response to user527060)Hi,user527060 wrote:
The functions in the UTL_MATCH package might help you.
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,
For example:
Of course, it depends on what you mean by "closest match" and "digit by digit".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 ;
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 Feb 12, 2013 10:29 PM (in response to user527060)Hi,
Below query is comparing the strings digit for digit and selects the one with the most matching digits:
Regards,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
Peter 
6. Re: Find the closest match based on the data
chris227 Feb 13, 2013 9:05 AM (in response to user527060)If it doesnt matter if a difference at this first place has a different weight than one at the endwith 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