SQL query for fuzzy searches? 1

[dwbh]

Hi,

I'm a wayward Lotus Notes developer who finds himself having to come up with a search engine back-end agent for an Oracle 8i DB. I'm very much a newbie with SQL, but so far, I've got a dynamically-built query that will do straight searches like a champ. However, I'd like to be able to do fuzzy searches, and someone mentioned that I might be able to modify my basic query to accomodate this. If my basic query template is "SELECT Field FROM Table WHERE Value LIKE 'ABC*'", is there a variant of this basic query that will do the fuzzy searches I'm looking for?

Muchas gracias,
Sam

 

[Naith]

What kind of fuzzy searches are you looking for there, Sam? There's a fair few ways you can do non-specific searches; from replacing single characters: ...LIKE 'A?E' to return 'ACE' and 'ARE', to similar phonetic searches: ...LIKE soundex('Kim') to return 'Kim', 'Kym', 'Kymm' etc.

Can you give an example of data to illustrate what you're trying to do if you still have questions.

Thanks,

Naith

 

[dwbh]

Naith, thanks for the reply. My search engine is for employee job titles, so for example, if someone does a search for the keyword "Administrator", I want to be able to pick up a title of "Admin". If someone typos and puts in "Administrater", I'd like to have enough quasi-intelligence to return hits on "Administrator" and "Admin". (the Soundex stuff you mentioned might help me here). I knew about wildcards and such, but I want to be able to pick up some abbreviations and typos too.

Is this stretching the SQL syntax, or can this be done (without purchasing a third-party solution, that is)? Thanks again!

 

[Naith]

Soundex is your man. Because some phonetic interpretation is required, it's not exactly a 100% waterproof function - but for uncovering the odd typo, you should be a-okay.

For example, in the case of the following:

select Name
from SomeGuy
where soundex(Name) = soundex('Alixander')

(I haven't got a database here, so the check the syntax)

should bring back something like the bolds below, but leave the others:

Alejandra
Alexander
Alexandra
Alexa
Alex

Try it out and see what you think of it.

All the best,

Naith

 

[karluk]

This issue was discussed in thread186-331331. If you have Oracle Text installed, you can use it for fuzzy matching. Oracle Text uses the packages installed in the CTXSYS schema to do pattern matching that is more sophisticated than standard SQL is capable of doing.

Soundex is a possible alternative, as Naith suggests.

 

[dwbh]

Soundex did the trick. In fact, it was even better than advertised: A search for "Technician" brough back hits on "Tech," which was the other part of the solution I needed. As long as I can accomplish this, I can sell my app without having to dig into Oracle Text stuff.

Thanks for the quick, helpful advice! If you should ever run into Lotus Notes trouble, just let me know.

Sam