Home » Server Options » Text & interMedia » Is there a simple "Fuzzy" LIKE query to match similar strings? (TOAD PLSQL)
Is there a simple "Fuzzy" LIKE query to match similar strings? [message #589663] Tue, 09 July 2013 09:47 Go to next message
bws93222
Messages: 27
Registered: April 2009
Junior Member
Hello. I'm looking for a simple query that does a "fuzzy" LIKE between two street addresses.
So, for example, if I had two STREET_ADDRESS values of '123 N. Elm St." and
'123 North Elm Street', I would want them to be matched. It doesn't need to be
perfect. If it just parses out the integers and compares those, that would be
fine. See my simplified pseudo-code below for what I'm trying to do. Any suggestions?

select MYTABLE.STREET_ADDRESS, OTHERTABLE.STREET_ADDRESS from MYTABLE, OTHERTABLE 
where MYTABLE.STREET_ADDRESS LIKE OTHERTABLE.STREET_ADDRESS
Re: Is there a simple "Fuzzy" LIKE query to match similar strings? [message #589666 is a reply to message #589663] Tue, 09 July 2013 10:54 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
For one, there is the utl_match package which has some string comparison functions.

And you could write a wrapper function, where you apply replace common abbreviations (North => N., Street => St.) before comparing the strings with a utl_match function.
Re: Is there a simple "Fuzzy" LIKE query to match similar strings? [message #589668 is a reply to message #589666] Tue, 09 July 2013 11:58 Go to previous messageGo to next message
bws93222
Messages: 27
Registered: April 2009
Junior Member
Thx. I really just needed something very quick and simple so I went ahead used this
which is imperfect but good enough to get (most of) the job done:

select MYTABLE.STREET_ADDRESS, OTHERTABLE.STREET_ADDRESS from MYTABLE, OTHERTABLE 
where(instr(upper(MYTABLE.STREET_ADDRESS), upper(substr(OTHERTABLE.STREET_ADDRESS, 0, 3)))=1)
Re: Is there a simple "Fuzzy" LIKE query to match similar strings? [message #589669 is a reply to message #589668] Tue, 09 July 2013 15:48 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
See Barbara's answer in this recent topic.
Previous Topic: Improve ctx_ddl.sync_index performance
Next Topic: performance issue with indexes
Goto Forum:
  


Current Time: Thu Mar 28 07:54:18 CDT 2024