Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Address Matching Using REGEXP_LIKE

571669Dec 29 2009 — edited Dec 30 2009
Hey all,
I am a newbie to REGEXP and have an issue where I have 2 spatial tables, for each object in table1 and am doing an SDO_NN to find the nearest 2 objects in table2. I also have an address column for each spatial table. I am trying to compare address1 (from table1) to both addresses (address2 from table2) to find the most likely match candidate.
After reading about REGEXP it seemed to me the REGEXP_LIKE was most likely best way of achieving my matches.
What I have come up with works alot of times but not always and I don't quite understand it.

Here is an example where I don't understand what is going on where I used REGEXP_LIKE as this
REGEXP_LIKE(address1,replace(address2,' ','|'))
SELECT 
  id,  
  address1,
  address2,
  CASE
    WHEN address1=address2 THEN 'EXACT MATCH'
    WHEN REGEXP_LIKE(address1,replace(address2,' ','|')) THEN 'PROBABLE MATCH'
    ELSE 'NON MATCH'
  END AS TEST
FROM
  (
  SELECT
    1 ID, '930 OLD STEESE HWY' address1, '930 OLD STEESE HWY' address2
  FROM
    DUAL  
  UNION SELECT
    3 ID, '930 OLD STEESE HWY' address1, '920 OLD STEESE HWY' address2
  FROM
    DUAL    
  UNION SELECT
    3 ID, '930 OLD STEESE HWY' address1, '99 COLLEGE RD' address2
  FROM
    DUAL    
  UNION SELECT
    4 ID, '930 OLD STEESE HWY' address1, '80 5TH ST' address2
  FROM
    DUAL      
  );

RESULTS
ID ADDRESS1            ADDRESS2            TEST
-- ------------------  ------------------  --------------
1  930 OLD STEESE HWY  930 OLD STEESE HWY  EXACT MATCH
2  930 OLD STEESE HWY  920 OLD STEESE HWY  PROBABLE MATCH
3  930 OLD STEESE HWY  99 COLLEGE RD       NON MATCH
4  930 OLD STEESE HWY  80 5TH ST           PROBABLE MATCH
I am really confused as to the 4th line in the results?
Any thoughts and or suggestions?
Appreciate it!
Cheers,
Eric
This post has been answered by 730428 on Dec 29 2009
Jump to Answer

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 27 2010
Added on Dec 29 2009
7 comments
1,988 views