perform partial search

hello,

I'm trying to make a search engine which should report partial results as well... for instance if "alpha" is present in the database then searching "alfa" should report "alpha" in the partial match result..

I do realise that a search of this type will take longer, i'm fixing that with two searches... exact and exact+partial. Also i believe that lots of language considerations will need to be made i.e. the phonetics for "ph", "f" are the same.

a bit off the topic, whats the way to search for a substring inside mysql db.. for instance if the entry is "the new end of the world news" how do i search for all occurances of "new".
The problems i faced were that the wildcard % does not include eof or beginning of file.. so for "new new new" n searching for "%new%" would result in only one occurance.. i.e. for the middle. any ways around this? other than adding more conditions like "%new" << for end and "new%" for beginning


thanks a lot

I guess your best bet is the SOUNDEX function...

I think you've misunderstood how wildcards work. '%new%' will match 'new thing', 'I am new here' and 'this is new'. % means zero or more occurences.

If you write a parser that allows wildcards between strings, then you can get the desired result:

"al*a" matches "alpha".

The problem then reduces to figuring out which partial matches to look for - because almost everything in the target file is likely to be a partial match to some non-empty substring or combination of substrings of the search string. I agree that the best way to do what you want is to just use the soundex function, and do exact matching (or slightly fuzzy matching) with it - but this only applies if you are searching for linguistic matches to poorly spelled sources. If it is a more general problem, then the solution is far from obvious.

hello,
i tried using "%therapy%" and it returns nothing... !! although the string i'm searching for in the databse is "therapy dogs in the house" result is returned though when i my search is "therapy %"!!! weird?? anyways.. thanks a lot..i'll look into the soundex function :smile:

Artificial Intelligence would be handy with this problem. Anyone have a script for that?

The % wildcard never returns a number, it returns true or false. So, you cannot achieve the behaviour you want in the database unless you use some other functions or procedures.

Most databases have some regular expression functions, ie RLIKE in mySQL. Look at them.

Soundex is a good solution for english, but take this as an advice. Keep in the database tables the soundex of the fields you are looking for, avoiding soundex encoding foreach query you do. Faster.