Wsrtjtyk

I have a database (Oracle) Table A with some strings in one of columns, Now I want to get matching records from Table B against each column value of Table A for example,

Table A

Name

-----------

ABC

DEE

GHI

JKL

Table B

Name

-----------

ABC

DEF

GHI

JKL

MNO

PQR

Now i want that each string in Table A must be checked against Table B's column and if some string is found almost identical then it should appear against original Value as per below

Table OutPut

Name      Matched

--------|----------

ABC     | ABC  

DEE     | DEF

GHI     | GHI

JKL     | JKL 

I have tried following query

with data as(

SELECT Name FROM TABLE_A UNION ALL

SELECT Name FROM TABLE_B

)

SELECT Name

FROM

(

SELECT t.*,utl_match.edit_distance_similarity(upper(Name),upper('DEE')) eds

FROM data t

ORDER BY eds DESC

)

WHERE rownum = 1

but problem is that using this query i can check only one record at a time and that too against a hard coded string. Is there any way to check whole column from Table A one by one against Table B and produce result in output against each string.

Not too clever (hint: performance issue, but - see if it helps. Might be OK if there aren't too many rows involved.

You need lines 21 onwards.

I set similarity to be greater than 80 - adjust it, if needed (which is very probable, as data you posted is really sample data).

SQL> WITH ta (name)

  2       AS (SELECT 'ABC' FROM DUAL

  3           UNION ALL

  4           SELECT 'DEE' FROM DUAL

  5           UNION ALL

  6           SELECT 'GHI' FROM DUAL

  7           UNION ALL

  8           SELECT 'JKL' FROM DUAL),

  9       tb (name)

 10       AS (SELECT 'ABC' FROM DUAL

 11           UNION ALL

 12           SELECT 'DEF' FROM DUAL

 13           UNION ALL

 14           SELECT 'GHI' FROM DUAL

 15           UNION ALL

 16           SELECT 'JKL' FROM DUAL

 17           UNION ALL

 18           SELECT 'MNO' FROM DUAL

 19           UNION ALL

 20           SELECT 'PQR' FROM DUAL)

 21  SELECT ta.name,

 22         tb.name,

 23         UTL_MATCH.jaro_winkler_similarity (ta.name, tb.name) sim

 24    FROM ta, tb

 25   WHERE UTL_MATCH.jaro_winkler_similarity (ta.name, tb.name) > 80

 26  ;



NAM NAM        SIM

--- --- ----------

ABC ABC        100

DEE DEF         82

GHI GHI        100

JKL JKL        100



SQL>