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