Hi,
I have to compare two strings using Oracle SQL to see if they are similar.
For example, the title of a song in one table might be 'White and Nerdy', in another 'WHITE AND NERDY' or 'White an Nerdy', so they would be a close match.
Is there an Oracle function to identify strings that are close matches?
Thanks!
Chris
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
CREATE TABLE test1 (c1 VARCHAR(100));
INSERT INTO test1 VALUES('White and Nerdy');
INSERT INTO test1 VALUES('WHITE AND NERDY');
INSERT INTO test1 VALUES('White an Nerdy');
SELECT * FROM test1
WHERE Soundex(c1) = Soundex('WHITE AND NERDY');
but don't expect too much from it. It won't find 'White an Nerdy', but would find something like
'White and Nerdee'
This article describes the general ideas of "similar" strings matching:
http://www.catalysoft.com/
Implementaion of such functionality would require weeks of work. However, using reqexp and some PL/SQL code, it might be possible to find strings similar to some original one.
I would build list of talkens separators (space, comma, dot etc.) and use regexp to compare talkens only.
Convert all characters to upper case.
and try any other options which make your finding closer to desired result. For example, make a function, which replaces all numbers with the text. 1 - one, 12 - twelve.
More functions - better result,
Oracle documentation for REGEXP_INSTR:
http://download-west.oracl
Oracle Text has better operators for this. It has 'fuzzy' and 'stemming' operators. The only drawback is Oracle Text requires a decent level of understanding before you go into production with them. For example: The Text indexes don't update themselves automatically (there are a few exceptions). They also tend to fragment themselves pretty easy and if not constantly kept up to date, will not perform at all.
Business Accounts
Answer for Membership
by: OMC2000Posted on 2006-12-08 at 07:44:35ID: 18102093
I see two options:
use soundex function or use regular expressions. Notice, that acceptable implementation of regular expressions appeared for Oracle 10g only.