Write query for Suond like search

  • hello everyone

    I am working on database where I would require to make search which base on sound like search:

    e.g.

    In my search values 'Poonam' and 'Punam', 'Veenita' and 'Vinita' both are same but 'Ramita' and 'Amita', 'Amita' and 'Ankita' ,Both are different.

    can any one suggest me how to write such query.

  • SSC has several related articles [/url]which may help. It's tricky and time consuming, and you may find it worthwhile purchasing a commercial application.

    โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Based on the sample names you've mentioned, SOUNDEX will work:

    select SOUNDEX('Poonam') -- P550

    select SOUNDEX('Punam') -- P550

    select SOUNDEX('Veenita') -- V530

    select SOUNDEX('Vinita') -- V530

    select SOUNDEX('Amita') -- A530

    select SOUNDEX('Ankita') -- A523

    select SOUNDEX('Ramita') -- R530

    select SOUNDEX('Amita') -- A530

    However, you could also try the Metaphone / DoubleMetaphone phonetic algorithms.

    You can store the phonetic codes for your names in a seperate column then generate a pehonetic code for the search string on the fly to look for simlar sounding words in your database. But this is just basic stuff, see above articles mentioned for more complicated examples.

    ---------------------------------------------------------

    It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
    David Edwards - Media lens[/url]

    Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
    Howard Zinn

  • this is working for Amita and Ankita but not working for Amita and Anita

  • Yea.. SOUNDEX is fairly basic and probably best suited for US English pronunciations.

    Have you had a chance to look at the links mentioned by Chris above?

    ---------------------------------------------------------

    It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
    David Edwards - Media lens[/url]

    Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
    Howard Zinn

  • This might help A better soundex

    There is a certain way Soundex works. If you have a basic logic between what values are alike and what are not then you can develop a custom Soundex function depending upon your logic.

    The above link and the links posted by Chris would help you in doing so.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] ๐Ÿ˜‰

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply