Write query for Suond like search

Question

Write query for Suond like search

amitsingh308

Ten Centuries

Points: 1287
More actions
April 9, 2013 at 1:33 am

#291933

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.

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

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

Abu Dina SSChampion Points: 14155 More actions · Answer 2

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

amitsingh308 Ten Centuries Points: 1287 More actions · Answer 3

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

Abu Dina SSChampion Points: 14155 More actions · Answer 4

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

vinu512 SSCoach Points: 15729 More actions · Answer 5

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] 😉