finding the best match-CodePudding

I am trying to find best match value from table T2 with respect to the value in table T3. For example, here I am expecting to get 441 as result.

Please advise.

Thanks.

create table t3 (rc varchar2(20));
create table t2 (rcs varchar2(20));

insert into T3 values ('441449729804');
insert into T2 values ('44');
insert into T2 values ('441');
commit;

CodePudding user response：

If you only ever have a single row selected from t3 then:

SELECT t3.rc, t2.rcs
FROM   t2
       INNER JOIN t3
       ON t3.rc LIKE t2.rcs || '%'
ORDER BY LENGTH(t2.rcs) DESC
FETCH FIRST ROW ONLY

If you can have multiple rows from t3 and want the best match for each then:

SELECT t3.rc,
       t2.rcs
FROM   t3
       CROSS JOIN LATERAL (
         SELECT rcs
         FROM   t2
         WHERE  t3.rc LIKE t2.rcs || '%'
         ORDER BY LENGTH(t2.rcs) DESC
         FETCH FIRST ROW ONLY
       ) t2

or:

SELECT rc,
       rcs
FROM   (
  SELECT t3.rc,
         t2.rcs,
         ROW_NUMBER() OVER (PARTITION BY t3.rc ORDER BY LENGTH(t2.rcs) DESC) AS rn
  FROM   t2
         INNER JOIN t3
         ON t3.rc LIKE t2.rcs || '%'
)
WHERE  rn = 1;

Which, for your sample data, all output:

RC	RCS
441449729804	441

fiddle