OraFAQ Forum: Text & interMedia » Is there a simple "Fuzzy" LIKE query to match similar strings?

Home » Server Options » Text & interMedia » Is there a simple "Fuzzy" LIKE query to match similar strings? (TOAD PLSQL)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Is there a simple "Fuzzy" LIKE query to match similar strings? [message #589663]

Tue, 09 July 2013 09:47

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

Report message to a moderator

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

ThomasG
Messages: 3212
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.

Report message to a moderator

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

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)

Report message to a moderator

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

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

See Barbara's answer in this recent topic.

Report message to a moderator

Previous Topic:	Improve ctx_ddl.sync_index performance
Next Topic:	performance issue with indexes

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 27 00:58:06 CST 2024