Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Posts
    116

    Unanswered: Best approach to search and compare data in two tables

    Hi Guys,

    I have a situation here, i have 2 tables, one is the base table
    and another one is the lookup table. The base table has around
    16 mil records, and the lookup table has around 30-50k records.
    Its extracted from 2 different systems and stored in two tables.

    Actually what i plan to achiive is to do a lookup to the base
    table and extract relevant info from the base table. Meaning to
    say to find a closest match for a particular record by comparing
    a particular field against the base and lookup

    The issue is that the value which im comparing is not stored
    in the same format. For instannce, theres a field called name field
    in the base table is stored as "christiano ronaldo" but in the lookup
    its stored as "ronaldo, christiano"..And its not always first name
    followed by last name, it might defer sometimes. Meaning to say
    theres no proper format how the name value is defined

    Actually i have planned to split the name value(by spaces) in lookup table
    and then take the first string and do a lookup against the base table
    to extract the relevant info using the like operator.

    I was just wondering if theres a better approach to achive this.

    thanks a lot for any suggestions
    Last edited by a1jit; 06-09-08 at 06:16.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    Anyway you do it, you may need to proceed in several steps an may have to code a pl/sql procedure.

    My personal summary suggestion is:

    1) Create a table where you split the name value by spaces from the base table.
    2) Load the split name values from the lookup table into a PL/SQL table in your procedure, .
    3) From a cursor on the temporary names table loop and lookup each split name for a match in the pl/sql table.
    4) Report matches.

    HTH

    PS: For more sophistication, code a function which would: a) strip vowels from the name, b) change like-sounding consonants with one same value ((k=k,c)?, (h=h,g)?,(m=m,n)?,(w=v,w)...) and whatever else you think of like removing SR, DR, VON, DE, DEL, and the like from the full names.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    An even easier way is to use the SOUNDEX clause in oracle. Use google "oracle soundex" to look it up. and if you made a function based index on the soundex code, your search would be very fast.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    Quote Originally Posted by beilstwh
    An even easier way is to use the SOUNDEX clause in oracle. Use google "oracle soundex" to look it up. and if you made a function based index on the soundex code, your search would be very fast.
    SOUNDEX May be easier and a good approximation, but it's unreliable.
    Also, would only partially work on the tables with parsed names, not in the actual non-parsed data:
    Code:
    SQL> select soundex('christiano ronaldo'), soundex('ronaldo, christiano') from 
    dual;
    
    SOUN SOUN
    ---- ----
    C623 R543
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Feb 2005
    Posts
    116
    Hi Guys,
    thanks a lot for the replies, really appreciate it, i ll try out the approach recommended, thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •